Dm 增量数据同步报错

🪐 TiDB 技术问题 备份&数据迁移
1

我们建了一张没有主键的表，有40个columns,我们使用dm dump出历史数据能导入进去，但是在使用dm同步增量数据的时候报:

"msg": "[code=36027:class=sync-unit:scope=internal:level=high]   
gen insert sqls failed, schema: lejiapay, table: tc_**_report: Column count doesn't match value count: 40 (columns) vs 41 (values)   

github.com/pingcap/dm/pkg/terror.(*Error).Generate
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:232
github.com/pingcap/dm/syncer.genInsertSQLs
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/dml.go:120
github.com/pingcap/dm/syncer.(*Syncer).handleRowsEvent
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1462
github.com/pingcap/dm/syncer.(*Syncer).Run
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1269
github.com/pingcap/dm/syncer.(*Syncer).Process
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:578
github.com/pingcap/dm/syncer.(*Syncer).Resume
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:2250
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1337"
2

TiDB 对于没有主键的表会生成一个隐士的 row_id ,不过这个并不会影响后续的数据同步，出现上述的报错问题，能否查一下下游表的列情况？

4

同步已经断开了吧，能拿下当前正在同步的语句看下吗

5

是的，由于报上面那个错，所以task停止，resume task后，还是会报错停止的

6

同步断了之后会显示当前正在同步的语句，拿下这个语句看下有什么异常。

7

只能看到当前在同步那个binlog文件然后pos是多少？怎么根据这个找到对应的sql呢？

8

query-error 看下返回的信息。

9 
» query-status
{
    "result": true,
    "msg": "",
    "workers": [
        {
            "result": true,
            "worker": "11.11.10.147:8262",
            "msg": "",
            "subTaskStatus": [
                {
                    "name": "online-sync",
                    "stage": "Paused",
                    "unit": "Sync",
                    "result": {
                        "isCanceled": false,
                        "errors": [
                            {
                                "Type": "UnknownError",
                                "msg": "[code=36027:class=sync-unit:scope=internal:level=high] gen insert sqls failed, schema: lejiapay, table: tc_financial_report: Column count doesn't match value count: 40 (columns) vs 41 (values)
github.com/pingcap/dm/pkg/terror.(*Error).Generate
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:232
github.com/pingcap/dm/syncer.genInsertSQLs
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/dml.go:120
github.com/pingcap/dm/syncer.(*Syncer).handleRowsEvent
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1462
github.com/pingcap/dm/syncer.(*Syncer).Run
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:1269
github.com/pingcap/dm/syncer.(*Syncer).Process
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:578
github.com/pingcap/dm/syncer.(*Syncer).Resume
	/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/syncer/syncer.go:2250
runtime.goexit
	/usr/local/go/src/runtime/asm_amd64.s:1337"
                            }
                        ],
                        "detail": null
                    },
                    "unresolvedDDLLockID": "",
                    "sync": {
                        "totalEvents": "5008866",
                        "totalTps": "448",
                        "recentTps": "123",
                        "masterBinlog": "(mysql-bin.001946, 2587576)",
                        "masterBinlogGtid": "5511bf34-9f8c-11e8-ade9-7cd30aeb7d98:1-263774444,711c81e0-9f8c-11e8-ade9-7cd30abeb464:1-76301308",
                        "syncerBinlog": "(mysql-bin|000001.001944, 179287012)",
                        "syncerBinlogGtid": "",
                        "blockingDDLs": [
                        ],
                        "unresolvedGroups": [
                        ],
                        "synced": false
                    }
                }
            ],
            "relayStatus": {
                "masterBinlog": "(mysql-bin.001946, 2587576)",
                "masterBinlogGtid": "5511bf34-9f8c-11e8-ade9-7cd30aeb7d98:1-263774444,711c81e0-9f8c-11e8-ade9-7cd30abeb464:1-76301308",
                "relaySubDir": "5511bf34-9f8c-11e8-ade9-7cd30aeb7d98.000001",
                "relayBinlog": "(mysql-bin.001946, 2587576)",
                "relayBinlogGtid": "",
                "relayCatchUpMaster": true,
                "stage": "Running",
                "result": null
            },
            "sourceID": "mysql-replica-01"
        }
    ]
}
» query-error
{
    "result": true,
    "msg": "",
    "workers": [
        {
            "result": true,
            "worker": "11.11.10.147:8262",
            "msg": "",
            "subTaskError": [
                {
                    "name": "online-sync",
                    "stage": "Paused",
                    "unit": "Sync",
                    "sync": {
                        "errors": [
                        ]
                    }
                }
            ],
            "RelayError": {
                "msg": ""
            }
        }
    ]
}
»
10

同步任务是如何配置的，是使用 all 模式还是 incremental 模式？（如果使用 incremental 模式有没有正确配置同步起始的 binlog position）

11

我们发现最终的问题还是因为mysql那边的表有id字段，但不是自增主键这个原因导致的，加了主键后就没有问题了

12

忘记说了我们使用的是阿里云的rds，阿里云的rds针对没有主键的表会自动加上一个隐性的主键

13

阿里 RDS 有个隐式主键的设置，针对没有主键的表增加了隐式主键同步到 TiDB 会报错，需要关掉这个设置，如果所有的表都有主键可以忽略

1 个赞
14

好的，3q

15

你的意思是关掉上游rds的还是下游DM的

16

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。