Issues in Handling a Database with Thousands of Tables When Migrating from V7.6 to V7.5.1

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

Original topic: 从V7.6迁移到V7.5.1时对一个具有几千张表的数据库处理问题

| username: 郑旭东石家庄

Bug Report
Clearly and accurately describe the issue you found. Providing any steps to reproduce the problem can help the development team address the issue promptly.
【TiDB Version】 Backup in V7.6, restore in V7.5.1
【Impact of the Bug】Performed a full backup in V7.6, backed up to Minio. During the full restore in V7.5.1, after waiting for four hours, no progress bar appeared, and it repeatedly prompted “switch to import mode”. Abandoned the full restore and tried restoring individual databases. The first few databases restored quickly, but when restoring a database with thousands of tables, the same issue as the full restore occurred. This database has over 200GB of data. After restoring for 3 hours, no progress bar appeared. Decided to abandon and switch to DM to sync from the source MySQL database, using the “all” sync mode. The initial export was normal, but it got stuck during the Load operation, remaining at 0% even after running overnight. Then decided to manually delete this database and re-import, but found that this database could not be deleted. Other databases can be operated normally. All tables in this database are visible but cannot be operated.
【Possible Steps to Reproduce the Issue】

【Observed Unexpected Behavior】

【Expected Behavior】

【Related Components and Specific Versions】

【Other Background Information or Screenshots】
CentOS 7.9
Memory: 125GB, 110GB available
Disk space is not an issue
Contact Number: 18132066585

| username: dba-kit | Original post link

The slow DM import is likely due to the fact that table creation could only be done serially before version 7.6. This was optimized in version 7.6: TiDB DDL V2 加速建表 | PingCAP 文档中心

| username: dba-kit | Original post link

When you used BR to restore, did you use BR version 7.5 or BR version 7.6?

| username: 郑旭东石家庄 | Original post link

This is not the reason. Other databases have no issues. Even if it is slower, not a single piece of data has been imported overnight. Now, this entire database is unresponsive and cannot be deleted.

| username: dba-kit | Original post link

What is the error message? Can you share it?

| username: 郑旭东石家庄 | Original post link

There is no error, it just keeps executing. It has been half an hour and nothing has happened while deleting an empty database.

| username: dba-kit | Original post link

Could you also share the error log when importing BR? Is there an issue with Minio?

| username: 郑旭东石家庄 | Original post link

Repeatedly

[2024/03/01 20:16:47.421 +08:00] [INFO] [client.go:1616] [“switch to import mode”]
[2024/03/01 20:16:47.518 +08:00] [INFO] [pd.go:513] [“pause scheduler(configs)”] [name=“[balance-region-scheduler,balance-leader-scheduler,balance-hot-region-scheduler]”] [cfg=“{"enable-location-replacement":"false","leader-schedule-limit":20,"max-pending-peer-count":2147483647,"max-snapshot-count":40,"merge-schedule-limit":0,"region-schedule-limit":40}”]
[2024/03/01 20:18:27.600 +08:00] [INFO] [pd.go:513] [“pause scheduler(configs)”] [name=“[balance-region-scheduler,balance-leader-scheduler,balance-hot-region-scheduler]”] [cfg=“{"enable-location-replacement":"false","leader-schedule-limit":20,"max-pending-peer-count":2147483647,"max-snapshot-count":40,"merge-schedule-limit":0,"region-schedule-limit":40}”]
[2024/03/01 20:20:07.522 +08:00] [INFO] [pd.go:513] [“pause scheduler(configs)”] [name=“[balance-region-scheduler,balance-leader-scheduler,balance-hot-region-scheduler]”] [cfg=“{"enable-location-replacement":"false","leader-schedule-limit":20,"max-pending-peer-count":2147483647,"max-snapshot-count":40,"merge-schedule-limit":0,"region-schedule-limit":40}”]
[2024/03/01 20:21:20.865 +08:00] [INFO] [domain.go:2899] [“refreshServerIDTTL succeed”] [serverID=905] [“lease id”=56188df885036464]

| username: lemonade010 | Original post link

Is there a log for insertion if there is no log for deletion?

| username: dba-kit | Original post link

Could you confirm which version of BR you are using, 7.5 or 7.6? Also, check the tidb-server logs to see if there are any errors. The issue of not being able to delete the database is a separate problem.

| username: 郑旭东石家庄 | Original post link

When backing up, BR version 7.6 was used, and version 7.5 was used for restoration.

| username: 郑旭东石家庄 | Original post link

Where can I see if there are logs for the insert?

| username: zhanggame1 | Original post link

If the data volume is not very large, use Dumpling and Lightning to run it.

| username: 郑旭东石家庄 | Original post link

The current issue is that the database name cannot be changed, and the current database name cannot be deleted. Executing a drop command just gets stuck, and it’s possible that all operations related to this database cannot be performed.

| username: Kongdom | Original post link

I looked at the version requirements for backup and restore, and it seems that 7.6 is a watershed.

| username: Kongdom | Original post link

First of all, the database name cannot be modified. If you cannot delete the database, could it be that there is a DDL operation stuck?

| username: IanWong | Original post link

The process you described needs to be analyzed in several parts. If the issue is not resolved, it is recommended to supplement the logs for further analysis.

  1. Using version 7.6.0 br for backup and version 7.5.1 br for full restoration to version 7.5.1: I tested 1000 tables and did not reproduce the error you encountered. It is speculated that there was an issue with pd scheduling at that time. It is recommended to provide complete logs: a. Backup and restore logs; b. pd and tikv logs, located in deploy_dir/${component:port}/log/
  2. Using version 7.6.0 br for backup and version 7.5.1 br for single database restoration to version 7.5.1: I tested 1000 tables and did not reproduce the error you encountered. It is recommended to provide logs as mentioned above.
  3. For DM synchronization errors, logs are also needed.
  4. For the situation where the database cannot be deleted, check if the previous operation process has not stopped, and logs are needed for troubleshooting.

Recommendations:

  1. It is not recommended to use br for cross-version backup and restoration. If cross-version is used, it is recommended to restore only the objects under the specified database.
  2. For cross-version or mysql->tidb with a small amount of data, you can use Dumping/Lightning.
| username: TiDBer_aaO4sU46 | Original post link

dumpling and lighting

| username: h5n1 | Original post link

Check the admin show ddl jobs and mysql.tidb_mdl_view table for DM import. High version backup and low version restore are generally used for self-testing only.

| username: 郑旭东石家庄 | Original post link

This was also a helpless choice. At that time, we were in a hurry and urgently needed to use it, so we installed version 7.6 without fully understanding it. Later, we found out that it was not a stable version and had bugs that prevented us from continuing to use it, so we switched to 7.5.1.