Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 使用br从s3恢复数据到一个新的tidb集群报错Error: [ddl:1069]Too many keys specified; max 64 keys allowed
[Test Environment for TiDB] Testing
[TiDB Version] v6.5.3
[Reproduction Path] What operations were performed to cause the issue
Normal backup to S3
During restoration
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
Error during restoration:
Error code 1069 specifically means “Error 1069: Too many keys specified; max 64 keys allowed,” which implies that the number of keys specified when creating an index or constraint exceeds the maximum limit. In TiDB, the total number of keys for indexes and constraints in each table cannot exceed 64. 
You can check your table definition to see if there are any redundant or duplicate keys. Additionally, you might consider using composite keys instead of multiple individual keys to reduce the number of keys.
I think an optimization should be suggested here. During the backup process, a warning message should be provided. Otherwise, if the backup is successful but the environment is lost and recovery fails, it would be a disaster.
The number of indexes has exceeded the limit.
Yes, it’s really frustrating. The backup was successful, but it won’t let me restore. Today, I was doing a disaster recovery drill because TiDB’s BR had issues before, so I thought it had been a few months since I last tested backup and recovery. It turned out there was indeed a problem. But when I asked our developers, they said we need that many indexes. Is there a way to solve this problem from the database level, like increasing the number of indexes to allow a table to have 256 or 512 indexes? If I need to restore in a production environment now, I’m screwed.
Is there a way to solve the problem from the database level, such as increasing the number of indexes, allowing a table to have 256 or 512 indexes? If I need to recover in the production environment now, I’m done for.
I configured this, but it still reports an error.
Ah, did you find the table? Check the table structure…
Also, use this command to confirm if the modification was successful: show config where name like ‘%index%’;
Oh right, BR restoring the table structure probably doesn’t execute an SQL file but uses SST to load into TiKV. So changing the database configuration won’t help, this error is likely a validation done by BR itself, I’m not too sure.
You should report this in the feedback section.
Check if the version of BR you used for export is consistent with the version you used for import?
It is best to keep the imported BR version consistent, although the imported BR version can be higher than the exported BR version.
Thank you for your response. I have identified which table caused the error. I imported this table into my new cluster using an SQL script, and there were no errors. This indicates that at the TiDB layer, there was no obstruction in creating the table, and it also shows that my configuration has taken effect, confirming that I successfully modified the configuration to 256, as shown in the image below:
To further verify this issue, I created a new cluster and set the index-limit to 64. When I imported the table again, it indeed reported an error because the table has 66 indexes. This once again confirmed that my configuration had taken effect. However, it was blocked by the BR tool. After checking the source code, it seems that in this version of the BR tool, the right value of the if condition should be the default index-limit, which is 64. Therefore, the error reported is 64. This is confirmed to be a bug.
It is consistent; the cluster version and the tool version are both the same.
It is most likely a bug in BR, which did not correctly read the index-limit I configured.
It’s unlikely to be a bug. Let’s keep looking for the cause.
You can report bugs to the community.
6.5.4
Increase the default values of the global parameters TableColumnCountLimit and IndexLimit used by BR to the maximum value to fix the issue of failure during the restore process #45793
Sure, I’ll give it a try. I’ll take this opportunity to upgrade TiDB. Which versions of the operator and TiDB should I choose for production use?
I upgraded my database to v7.1.0, then did a full backup, and then fully restored it to a new cluster (dr, also v7.1.0), but the error still occurs. This indicates that the problem has not been resolved yet.
|
username: panqiao
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: