RDB Adapter 关于源端mysql字段名大小写问题 #1433
Description
environment
- canal version 1.1.3
- mysql version 5.6
Issue Description
源端mysql如果字段名是大写的会出现的更新和删除的情况
Steps to reproduce
源端mysql建表的时候字段名用的大写:
CREATE TABLE `xdual` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
源端测试表插入一条数据：
INSERT INTO test.xdual(id,x) VALUES(NULL,NOW());
ID X
1 2019-01-15 06:13:59
Sync RDB配置为大写:
dataSourceKey: defaultDS
destination: example
outerAdapterKey: 234pg
concurrent: true
dbMapping:
database: test
table: xdual
targetTable: canal_test.xdual
targetPk:
id: ID
targetColumns:
id: ID
x: X
启动了canal服务端和Sync RDB 后，在源端mysql更新记录:
UPDATE test.xdual SET ID = 1001 WHERE id = 1;
获得异常:
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "WHERE"
位置：28
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477) ~[postgresql-42.1.4.jar:42.1.4]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190) ~[postgresql-42.1.4.jar:42.1.4]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300) ~[postgresql-42.1.4.jar:42.1.4]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428) ~[postgresql-42.1.4.jar:42.1.4]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354) ~[postgresql-42.1.4.jar:42.1.4]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169) ~[postgresql-42.1.4.jar:42.1.4]
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158) ~[postgresql-42.1.4.jar:42.1.4]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) ~[druid-1.1.9.jar:1.1.9]
at com.alibaba.otter.canal.client.adapter.rdb.support.BatchExecutor.execute(BatchExecutor.java:52) ~[na:na]
at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.update(RdbSyncService.java:320) ~[na:na]
at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:193) ~[na:na]
... 8 common frames omitted
Debug查看生成的sql: UPDATE canal_test.xdual SE WHERE id=?
原因应该是在比对字段名时只对其中一个进行了小写转换
RdbSyncService.update 方法
然后尝试将mysql字段名配置成小写
dataSourceKey: defaultDS
destination: example
outerAdapterKey: 234pg
concurrent: true
dbMapping:
database: test
table: xdual
targetTable: canal_test.xdual
targetPk:
id: id
targetColumns:
id: id
x: x
在源端mysql更新记录:
UPDATE test.xdual SET ID = 1 WHERE id = 1001;
这次没有异常，但是目标端依然更新失败
跟到RdbSyncService.appendCondition 方法发现是字段值没有正确匹配
Expected behaviour
更新和删除正常
Actual behaviour
更新和删除失败