RDB Adapter 关于源端mysql字段名大小写问题

环境信息

canal version 1.1.3 mysql version 5.6

问题描述

源端mysql如果字段名是大写的会出现的更新和删除的情况

步骤重现

源端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 方法发现是字段值没有正确匹配

期待结果

更新和删除正常

实际执行情况

更新和删除失败

原提问者GitHub用户dsf66zcq