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 方法

提问266.png

然后尝试将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 方法发现是字段值没有正确匹配

提问267.png

提问268.png

期待结果

更新和删除正常

实际执行情况

更新和删除失败

原提问者GitHub用户dsf66zcq

展开

收起

古拉古拉 2023-05-08 15:05:43 73 0

2 条回答

写回答

取消提交回答

函数计算小白

也在追踪这个问题，大约是在

canal/client-adapter/rdb/src/main/java/com/alibaba/otter/canal/client/adapter/rdb/service/RdbSyncService.java

Lines 312 to 320 in 7e4912c

int len = updateSql.length(); updateSql.delete(len - 2, len).append(" WHERE ");

// 拼接主键 appendCondition(dbMapping, updateSql, ctype, values, data, old); batchExecutor.execute(updateSql.toString(), values); if (logger.isTraceEnabled()) { logger.trace("Update target table, sql: {}", updateSql); }

313行计算SQL长度时 len - 2 存在问题，另外 L318-320 行建议放在 317行之前，原因是在 317行 thrown 后，无机会打印出 trace log。

按此例子，update 操作复现仅需调整为：

targetColumns: id: ID

然后源表执行

UPDATE test.xdual SET X = now() WHERE ID = 1001

就会引发 syntax error at or near "WHERE" 异常。

原回答者GitHub用户TheNorthMemory

2023-05-09 18:06:56

赞同展开评论打赏
vohelon

这种情况很可能是由于在默认情况下，MySQL 数据库是不区分字段名的大小写的。因此，在进行数据同步时，如果 Canal 与 MySQL 的字符集设置不一致，则在字段名大小写不同时可能会发生异常情况。

为了解决这个问题，可以尝试以下两种方式：

修改 MySQL 数据库的字符集在 MySQL 中，可以通过修改数据库或表的字符集来解决该问题。具体步骤如下：查看当前数据库或表的字符集：SHOW CREATE DATABASE database_name/table_name; 修改数据库或表的字符集：ALTER DATABASE/ TABLE database_name/table_name CHARACTER SET charset_name; 其中，charset_name 表示需要修改为的字符集名称，需要根据实际情况进行选择。

在 Canal 的配置文件中设置字符集参数在 Canal 的配置文件中，可以设置字符集相关的参数，以确保与 MySQL 数据库的字符集一致。具体参数包括： canal.instance.dbCharset: 指定 Canal 与 MySQL 数据库交互时使用的字符集； canal.instance.connectionCharset: 指定 Canal 与 MySQL 数据库连接时使用的字符集。

2023-05-08 16:01:06

赞同展开评论打赏