同步的表必须要有主键，否则更新不了

环境信息

canal version:v1.1.4 mysql version 5.6

问题描述

Mysql 开启镜像同步，创建的表没有主键，插入没有问题，更新的时候会报异常， debug了下貌似是必须通过主键更新，没有主键拼装的sql会出现问题？ 生成的update sql语句如下： UPDATE output SET age=123 WH

rdb/mytest_user.yml 配置如下： Mirror schema synchronize config dataSourceKey: defaultDS destination: example groupId: g1 outerAdapterKey: mysql1 concurrent: true dbMapping: mirrorDb: true database: test

异常如下

2019-09-26 15:48:16.515 [pool-8-thread-1] ERROR c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: com.my sql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WH' at line 1 java.lang.RuntimeException: java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in you r SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WH' at line 1 at com.alibaba.otter.canal.client.adapter.rdb.RdbAdapter.sync(RdbAdapter.java:171) at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.batchSync(AbstractCanalAdapterWorker.java:201) at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$1(AbstractCanalAdapterWorker.java:62) at java.util.ArrayList.forEach(ArrayList.java:1257) at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$2(AbstractCanalAdapterWorker.java:58) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; che ck the manual that corresponds to your MySQL server version for the right syntax to use near 'WH' at line 1 at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$2(RdbSyncService.java:132) at java.util.ArrayList.forEach(ArrayList.java:1257) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:128) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbMirrorDbSyncService.sync(RdbMirrorDbSyncService.java:83) at com.alibaba.otter.canal.client.adapter.rdb.RdbAdapter.sync(RdbAdapter.java:169) ... 8 common frames omitted Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that correspon ds to your MySQL server version for the right syntax to use near 'WH' at line 1 at java.util.concurrent.FutureTask.report(FutureTask.java:122) at java.util.concurrent.FutureTask.get(FutureTask.java:192) "../logs/adapter/adapter.log" 887L, 131589C at com.alibaba.otter.canal.client.adapter.rdb.RdbAdapter.sync(RdbAdapter.java:171) at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.batchSync(AbstractCanalAdapterWorker.java:201) at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$1(AbstractCanalAdapterWorker.java:62) at java.util.ArrayList.forEach(ArrayList.java:1257) at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$2(AbstractCanalAdapterWorker.java:58) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; che ck the manual that corresponds to your MySQL server version for the right syntax to use near 'WH' at line 1 at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$2(RdbSyncService.java:132) at java.util.ArrayList.forEach(ArrayList.java:1257) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:128) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbMirrorDbSyncService.sync(RdbMirrorDbSyncService.java:83) at com.alibaba.otter.canal.client.adapter.rdb.RdbAdapter.sync(RdbAdapter.java:169) ... 8 common frames omitted Caused by: java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that correspon ds to your MySQL server version for the right syntax to use near 'WH' at line 1 at java.util.concurrent.FutureTask.report(FutureTask.java:122) at java.util.concurrent.FutureTask.get(FutureTask.java:192) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$2(RdbSyncService.java:130) ... 12 common frames omitted Caused by: java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the r ight syntax to use near 'WH' at line 1 at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$1(RdbSyncService.java:123) ... 4 common frames omitted Caused by: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WH' at line 1 at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:224) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$null$0(RdbSyncService.java:115) at java.util.ArrayList.forEach(ArrayList.java:1257) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.lambda$sync$1(RdbSyncService.java:115) ... 4 common frames omitted Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WH' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1192) at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) at com.alibaba.otter.canal.client.adapter.rdb.support.BatchExecutor.execute(BatchExecutor.java:63) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.update(RdbSyncService.java:348) at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:214)

原提问者GitHub用户wade-bigdata