BR Tool Incremental Backup Set Restore Failed [error="[ddl:1061]index already exists k_1"]

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: br工具增量备份集恢复失败 [error=“[ddl:1061]index already exist k_1”]

| username: TiDBer_BwNZ5U9X

Full recovery succeeded, incremental recovery failed with the error: Error: [ddl:1061] index already exists k_1.

Table creation statement:

CREATE TABLE `sctest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1035594 |

br.log

image
image1380×750 981 KB

2
| username: zhanggame1 | Original post link

Check if there is already an index named k_1 in the database.

3
| username: TiDBer_BwNZ5U9X | Original post link

k_1 is the index of this table, and it was created during the full backup.

4
| username: tidb菜鸟一只 | Original post link

What is the version of br?

5
| username: TiDBer_BwNZ5U9X | Original post link

It’s also 6.1.0, specifically chosen.

6
| username: xfworld | Original post link

What operation is the increment based on?

7
| username: tidb菜鸟一只 | Original post link

Your incremental recovery restored the table creation statement and index creation statement for sctest1, but the table creation statement already included the index, so the index creation statement couldn’t be executed?

8
| username: TiDBer_BwNZ5U9X | Original post link

Use the br tool, based on the TSO returned from the last full backup.

9
| username: TiDBer_BwNZ5U9X | Original post link

My steps:

  1. Create database test, create table sctest1 in the database, generate data, and perform a full backup.

  2. Create database test1, create table sctest1 in the database, generate data, and perform an incremental backup.

  3. Restore the full backup successfully, but the incremental restore failed.
    (The table creation and data generation were done using sysbench)

10
| username: xfworld | Original post link

After a full restore, the table information, index information, and data are all successfully established.

At this point, if the incremental restore operation includes complete table information and complete index information, it will definitely fail…

Because the table and index already exist…

image
image986×1000 102 KB

The feature you want will only be available after version 7.1…

image
image1064×817 59.6 KB

11
| username: 裤衩儿飞上天 | Original post link

Was the restoration done in the original cluster? It should be performed in an empty cluster or a cluster without data conflicts (same schema or table) to execute snapshot backup restoration.

image
image1047×108 28.8 KB

12
| username: TiDBer_BwNZ5U9X | Original post link

However, shouldn’t the restoration be done by first restoring the full backup and then the incremental backup? Otherwise, it would be impossible to restore the incremental changes. The ifexists option skips the restoration of existing tables, so if there are modifications to this table in the incremental backup, wouldn’t those changes be missed?

13
| username: zhanggame1 | Original post link

Are you restoring an empty database? If it’s an empty database, you shouldn’t have the issue you’re describing. Also, can you specify how to perform full and incremental backups? Is there a problem with the operation?

14
| username: xfworld | Original post link

So the documentation has a warning, right? It is not recommended for use in a production environment. :slightly_smiling_face:

There are also precautions. If you haven’t considered them, you’ll end up encountering issues…

15
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.