DM Synchronization Issues

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

Original topic: dm同步问题

| username: mono

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.8
[Encountered Problem: Phenomenon and Impact]
Upstream MySQL data is synchronized to the TiDB cluster. The partitioned tables in the MySQL database are merged and synchronized to the TiDB cluster. For example, MySQL tables t1_01, t1_02 are merged into table t1 in TiDB.
Previously, synchronization was normal, but due to changes in the data types of some fields in the partitioned tables in the upstream MySQL, the synchronization task was interrupted.
“syncerBinlog”: Not moving
“unsynced”: Indicates unsynchronized partitioned table information
“synced”: false

The table structure in TiDB has already been manually updated. How should this be handled?

| username: Kamner | Original post link

Take a look at

tiup dmctl query-status XXX

Try restarting the synchronization task.

| username: mono | Original post link

It is by checking the status that we know the information in the feedback.

| username: mono | Original post link

Tried it. Doesn’t work.

| username: 有猫万事足 | Original post link

I remember this situation. The task error message should prompt you to use

tiup dmctl operate-schema

or

tiup dmctl binlog-schema

You need to manually update the table structure recorded in DM. You can refer to the document above for details.

| username: wangkk2024 | Original post link

One-time synchronization?

| username: xiaoqiao | Original post link

Are there any logs, and is the process normal?

| username: DBAER | Original post link

This brother is right, manually update the table structure recorded by DM.

| username: mono | Original post link

The previous errors have already been skipped. The status is running, but it is stuck at the sync stage.
subTaskStatus": [
{
“name”: “dmtask_001”,
“stage”: “Running”,
“unit”: “Sync”,
“result”: null,
“unresolvedDDLLockID”: “”,
“sync”: {
“totalEvents”: “9753”,
“totalTps”: “0”,
“recentTps”: “0”,
“masterBinlog”: “(dd-mysql-1-bin.000489, 86754522)”,
“masterBinlogGtid”: “”,
“syncerBinlog”: “(dd-mysql-1-bin.000488, 190527979)”,
“syncerBinlogGtid”: “00000000-0000-0000-0000-000000000000:0”,
“blockingDDLs”: [
],
“unresolvedGroups”: [
{
“target”: “mydb.t1”,
“DDLs”: [
"ALTER TABLE mydb.t1 CHANGE COLUMN after_star_amount after_star_amount INT(11) DEFAULT 0 NOT NULL "
],
“firstLocation”: “position: (dd-mysql-1-bin.000488, 190527979), gtid-set: 00000000-0000-0000-0000-000000000000:0”,
“synced”: [
weplay.t1_202403
],
“unsynced”: [
mydb.t1_202404”,
mydb.t1_202405”,
mydb.t1_202406”,
]
}
],
“synced”: false,
“binlogType”: “remote”,
“secondsBehindMaster”: “0”,
“blockDDLOwner”: “”,
“conflictMsg”: “”,
“totalRows”: “9753”,
“totalRps”: “0”,
“recentRps”: “0”
},
“validation”: null
}

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

binlog skip taskname
Skip three more times. Since you are in a scenario of merging databases and tables, the DDL needs to be executed four times. You need to skip four times, but you have only skipped once.

| username: mono | Original post link

This cannot be skipped. The status is already running.

| username: 友利奈绪 | Original post link

Is it a bug issue? Please report it.

| username: dba远航 | Original post link

Update the table structure in TiDB and resynchronize?

| username: mono | Original post link

The table structure in TiDB has already been manually updated. Now the main task is running normally, but syncbinlog is stuck and not moving.

| username: 舞动梦灵 | Original post link

I encountered a similar issue where the table structure was inconsistent, causing synchronization to fail. Manually modifying the table structure and stopping/starting the task didn’t work. You must use binlog-schema to update the table structure. Refer to: 下游存在更多列的迁移场景 | PingCAP 文档中心

Steps I followed:

  1. Create the same table structure on the DM server as the upstream:
vim 1.sql
[tidb@dm1 log]$ cat /home/tidb/1.sql
CREATE TABLE TCCORE_PROD.tmp_20240328_userid (
AM_WALLET_ID char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
USER_ID char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (AM_WALLET_ID) USING BTREE,
INDEX USER_ID(USER_ID(20)) USING BTREE)
  1. Manually update the table structure using binlog-schema:
tiup dmctl --master-addr=192.168.1.xxx:8261 binlog-schema update -s db31 mysql-tidb -d tccore_prod -t tmp_20240328_userid 1.log
  1. Resume the task:
tiup dmctl --master-addr=192.168.3.233:8261 resume-task mysql-tidb

Check the status:

tiup dmctl --master-addr=192.168.3.233:8261 query-status
| username: 有猫万事足 | Original post link

Take a look at this. The reason for this error is:

“unsynced”: [
mydb.t1_202404”,
mydb.t1_202405”,
mydb.t1_202406”,
]

The table structures of these upstream tables might not have been changed, and the field types of these pre-created tables might still be old. Because it involves shard merging, it is locked by DM’s DDL.

If you are sure that no modifications are needed and no data will be inserted, you can directly consider unlocking this DDL lock as described in the documentation.

| username: mono | Original post link

The tables in MySQL have been modified. Initially, I used binlog skip, but the task couldn’t be restored. Then I used binlog-schema to delete the information of these tables in DM. The task still couldn’t run. I checked the DM synchronization task, and there were no locks. It seems that I have to specify the binlog position for synchronization. However, in this case, the data won’t match.

| username: 有猫万事足 | Original post link

What is the result of tiup dmctl shard-ddl-lock dmtask_001?

| username: 有猫万事足 | Original post link

It is definitely possible to resynchronize by specifying the binlog position.

Additionally, you can put frequently changing tables in a dedicated task.
This way, changes to a few frequently modified tables won’t affect the synchronization of other tables.
If the upstream is used arbitrarily, the downstream will suffer.
The only thing you can do is to group the frequently problematic tables together. Don’t let them affect the tables that are not problematic.

| username: mono | Original post link

{
“result”: true,
“msg”: “no DDL lock exists”,
“locks”:
}