otter 带转义符的JSON字段同步失败

z18hc3ub  于 2022-10-25  发布在  其他
关注(0)|答案(1)|浏览(332)

mysql -> tidb的单向同步，源端进行json的更新/插入时使用 {"content": "错误事例\n"} 这个字面量插入时，源端插入成功，但是otter在目标端同步时pipeline会报如下错误： invalid character '\n' in string literal ，加断点调试后发现otter从canal读入的json字段把 \n ( java String "\\n" )这个字面量直接识别成了 换行符 ( java String "\n" )。期待值应该是两个char，分别是 \ 和 n ，实际值是一个char，也就是 \n 。
源表更新sql： update test_types set jsoner='{"content": "错误事例\\n"}';
更新后源表查询结果： {"content": "错误事例\n"}
目前看下来应该是canal侧的bug。
完整pipeline错误:

pid:1 nid:1 exception:setl:com.alibaba.otter.node.etl.load.exception.LoadException: java.util.concurrent.ExecutionException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: com.alibaba.otter.node.etl.load.exception.LoadException: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into `otter_test`.`test_types`(`nuller` , `inter` , `doubler` , `decimaler` , `booler` , `varcharer` , `dater` , `timer` , `datetimer` , `jsoner` , `id`) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) on duplicate key update `nuller`=values(`nuller`) , `inter`=values(`inter`) , `doubler`=values(`doubler`) , `decimaler`=values(`decimaler`) , `booler`=values(`booler`) , `varcharer`=values(`varcharer`) , `dater`=values(`dater`) , `timer`=values(`timer`) , `datetimer`=values(`datetimer`) , `jsoner`=values(`jsoner`) , `id`=values(`id`)]; Data truncation: Invalid JSON text: invalid character '\n' in string literal; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Invalid JSON text: invalid character '\n' in string literal
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:868)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker$2.doInTransaction(DbLoadAction.java:625)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.doCall(DbLoadAction.java:617)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$DbLoadWorker.call(DbLoadAction.java:545)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doTwoPhase(DbLoadAction.java:462)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.doLoad(DbLoadAction.java:275)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction.load(DbLoadAction.java:161)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$FastClassByCGLIB$$d932a4cb.invoke()
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
at com.alibaba.otter.node.etl.load.loader.db.DbLoadAction$$EnhancerByCGLIB$$80fd23c2.load()
at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:198)
at com.alibaba.otter.node.etl.load.loader.db.DataBatchLoader$2.call(DataBatchLoader.java:189)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Invalid JSON text: invalid character '\n' in string literal
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2058)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5158)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2043)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
... 21 more
:-----------------
- PairId: 1 , TableId: 1 , EventType : U , Time : 1578991470000
- Consistency : , Mode :
-----------------
---Pks
EventColumn[index=0,columnType=4,columnName=id,columnValue=1,isNull=false,isKey=true,isUpdate=true]
---oldPks

---Columns
EventColumn[index=1,columnType=12,columnName=nuller,columnValue=,isNull=true,isKey=false,isUpdate=true]
EventColumn[index=2,columnType=4,columnName=inter,columnValue=10,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=3,columnType=8,columnName=doubler,columnValue=2.3,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=4,columnType=3,columnName=decimaler,columnValue=3.20,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=5,columnType=-6,columnName=booler,columnValue=1,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=6,columnType=12,columnName=varcharer,columnValue=sad,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=7,columnType=91,columnName=dater,columnValue=2020-01-01,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=8,columnType=92,columnName=timer,columnValue=01:00:00,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=9,columnType=93,columnName=datetimer,columnValue=2020-01-01 01:00:00,isNull=false,isKey=false,isUpdate=true]
EventColumn[index=10,columnType=12,columnName=jsoner,columnValue={"content": "错误事例
"},isNull=false,isKey=false,isUpdate=true]
---Sql
insert into `otter_test`.`test_types`(`nuller` , `inter` , `doubler` , `decimaler` , `booler` , `varcharer` , `dater` , `timer` , `datetimer` , `jsoner` , `id`) values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?) on duplicate key update `nuller`=values(`nuller`) , `inter`=values(`inter`) , `doubler`=values(`doubler`) , `decimaler`=values(`decimaler`) , `booler`=values(`booler`) , `varcharer`=values(`varcharer`) , `dater`=values(`dater`) , `timer`=values(`timer`) , `datetimer`=values(`datetimer`) , `jsoner`=values(`jsoner`) , `id`=values(`id`)

@agapple

otter

1条答案

按热度按时间
taor4pac

taor4pac1#

应该是mysql binlog记录json策略的问题，在byte[]当中就已经是 \n 了。应该 这个 canal issue的根本原因就是这个。感觉可以在canal parse json的时候借助JSONObject解决这个问题。

赞(0）分享  回复(0）举报  2022-10-25
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com