用 canal 消费kafka 消息写入 tidb 时，遇到如下报错

🪐 TiDB 技术问题
1

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

["command dispatched failed"] [conn=7965071138818675769] [connInfo="id:7965071138818675769, addr:10.61.244.112:59410 status:0, collation:utf8_general_ci, user:xxx"] [command=Prepare] [status="inTxn:0, autocommit:0"] [sql="delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? ;delete from `db1`.`table1` where  `uid` = ? and `groupby` = ? "] [txn_mode=PESSIMISTIC] [timestamp=0] [err="[executor:8115]Can not prepare multiple statements"]`

有没有什么参数可以控制执行这个语句？

2

工具在执行 SQL 时包含了 多条 SQL 语句，而 TiDB 出于安全性考虑，不支持在一个 PREPARE 或执行语句中包含多条 SQL 语句。
可以调整 tidb_multi_statement_mode 来关闭检测，但是有 sql 注入等风险。推荐改 canal 配置，allow multi，工具上拆 sql。

2 个赞
3

java connector 相关配置。

4

image
image2112×784 101 KB

1 个赞
5

写入的时候，可以将SQL 拆分，重组即可

String url = "jdbc:mysql://localhost:3306/mydatabase";
String user = "username";
String password = "password";
Connection conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false); // 关闭自动提交
PreparedStatement pstmt = conn.prepareStatement("delete from `db1`.`table1` where  `uid` = ? and `groupby` = ?");
for (int i = 0; i < 100; i++) {
    pstmt.setString(1, "value1");
    pstmt.setString(2, "value2");
    pstmt.addBatch(); // 将当前语句添加到批处理中
}
pstmt.executeBatch(); // 执行批处理中的所有语句
conn.commit(); // 提交事务
conn.close(); // 关闭连接

通过batch 的方式写入 tidb，这样会比较省事，性能也还不错