项目场景:
提示:这里简述项目相关背景:
canal-adpter在执行update同步数据时报错
问题描述
提示:这里描述项目中遇到的问题:
例如:从这里可以看出update时sql补全,出现了WH无法识别的问题,看起来是在拼接WHERE关键字时被截取了。
2024-04-22 11:20:41.572 [Thread-11] ERROR c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - Outer adapter sync failed! Error sync and rollb
ack, execute times: 1880803
2024-04-22 11:20:42.076 [pool-29-thread-1] ERROR com.alibaba.druid.filter.stat.StatFilter - merge sql error, dbType mysql, druid-1.2.17, sql : UPDATE `mytest`.`t` SET `id`=? WH
com.alibaba.druid.sql.parser.ParserException: not supported.pos 33, line 1, column 31, token IDENTIFIER WH
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:615)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:101)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:163)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:134)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:148)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:672)
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:325)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:118)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:531)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:326)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:362)
at com.alibaba.otter.canal.client.adapter.rdb.support.BatchExecutor.execute(BatchExecutor.java:55)
at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.update(RdbSyncService.java:365)
at com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService.sync(RdbSyncService.java:228)
at com.alibaba.otter.canal.client.adapter.rd
原因分析:
直接说结论和解决方法:
原因是因为同步的表没有设置主键,而canal-adapter在同步的时候,是根据主键去拼接where条件的,如果没有设置会导致截取出错。
下面是源代码
com.alibaba.otter.canal.client.adapter.rdb.service.RdbSyncService
private void appendCondition(MappingConfig.DbMapping dbMapping, StringBuilder sql, Map<String, Integer> ctype,
List<Map<String, ?>> values, Map<String, Object> d, Map<String, Object> o) {
String backtick = SyncUtil.getBacktickByDbType(dataSource.getDbType());
// 拼接主键
for (Map.Entry<String, String> entry : dbMapping.getTargetPk().entrySet()) {
String targetColumnName = entry.getKey();
String srcColumnName = entry.getValue();
if (srcColumnName == null) {
srcColumnName = Util.cleanColumn(targetColumnName);
}
sql.append(backtick).append(targetColumnName).append(backtick).append("=? AND ");
Integer type = ctype.get(Util.cleanColumn(targetColumnName).toLowerCase());
if (type == null) {
throw new RuntimeException("Target column: " + targetColumnName + " not matched");
}
// 如果有修改主键的情况
if (o != null && o.containsKey(srcColumnName)) {
BatchExecutor.setValue(values, type, o.get(srcColumnName));
} else {
BatchExecutor.setValue(values, type, d.get(srcColumnName));
}
}
/**
* 关键是在这里
* 如果没有主键会直接运行这段代码
* 拼接好的WHERE会被截取掉后4个字符变成 WH
**/
int len = sql.length();
sql.delete(len - 4, len);
}
解决方案:
给相关的表添加主键即可解决