You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/08/04 03:54:58 UTC
[GitHub] [shardingsphere] dmbj opened a new issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
dmbj opened a new issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604
### sharding scaling&proxy version
sharding scaling&proxy 4.1.1, MYSQL 5.7.26
### Expected behavior
sync realtimedata
### Actual behavior
scaling got an exception,and proxy trace log find some log:
### Reason analyze (If you can)
realtimeTask sync binlog data explain SQL field text error,got an special char with sql statement
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1、use scaling&proxy sync data，history task sync work well；
2、sync realtime data，when sync row fieldtype is text，seam explain sql statement wrong.
some log:
**scaling exception:**
`
[INFO ] 10:19:51.635 [pool-1-thread-2] o.a.s.s.c.e.e.SyncTaskExecuteCallback - HistoryDataSyncTask history-appbiz_data_test-t_qrcode_consume_details#0 execute finish
[INFO ] 10:19:51.636 [pool-1-thread-2] o.a.s.s.c.c.task.SyncTaskController - history data migrate task historyGroup-appbiz_data_test finished, execute result: FINISHED
[INFO ] 10:19:51.812 [pool-1-thread-3] com.zaxxer.hikari.HikariDataSource - HikariPool-6 - Starting...
[INFO ] 10:19:51.862 [pool-1-thread-3] com.zaxxer.hikari.HikariDataSource - HikariPool-6 - Start completed.
[INFO ] 10:19:53.675 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0xa2f9b16a, L:/0.0.0.0:8888] READ: [id: 0x9c894650, L:/127.0.0.1:8888 - R:/127.0.0.1:45070]
[INFO ] 10:19:53.675 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0xa2f9b16a, L:/0.0.0.0:8888] READ COMPLETE
[ERROR] 10:19:54.833 [pool-1-thread-8] o.a.s.s.c.e.e.SyncTaskExecuteCallback - RealtimeDataSyncTask realtime-appbiz_data_test execute exception exit
org.apache.shardingsphere.shardingscaling.core.exception.SyncTaskExecuteException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01', '01', '2019-07-25 17:39:44', '2019-07-25 17:39:44', null, '00', 'ch_17itblm' at line 1
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.write(AbstractJDBCWriter.java:90)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.run(AbstractJDBCWriter.java:72)
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.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '01', '01', '2019-07-25 17:39:44', '2019-07-25 17:39:44', null, '00', 'ch_17itblm' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.executeInsert(AbstractJDBCWriter.java:128)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.flush(AbstractJDBCWriter.java:103)
at org.apache.shardingsphere.shardingscaling.core.execute.executor.writer.AbstractJDBCWriter.write(AbstractJDBCWriter.java:81)
... 6 common frames omitted
`
**proxy log:**
`
[INFO ] 10:19:51.633 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: INSERT INTO `t_qrcode_consume_details_11`(`order_no`,`user_id`,`code_type`,`channel`,`update_time`,`amount`,`scene`,`extra`,`paid_charge_id`,`init_status`,`status`,`gmt_create`,`gmt_modify`,`rev`,`pay_channel`,`pay_order_no`) VALUES('ybb-88888888215405201588634', '201015511', '00', '01', '2018-10-26 10:15:58.0', 1, '64', null, null, '01', '01', '2018-10-26 11:33:42.0', '2018-10-26 11:33:42.0', null, null, null)
[INFO ] 10:19:54.676 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: select @@session.tx_read_only
[INFO ] 10:19:54.676 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@79b0e6cd, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@e6b0ee6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@e6b0ee6, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=28, distinctRow=false, projections=[ExpressionProjection(expression=@@session.tx_read_only, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@557ba7cf, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@ab82903, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@74403017, containsSubquery=false)
[INFO ] 10:19:54.676 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: select @@session.tx_read_only
[INFO ] 10:19:54.816 [ShardingSphere-Command-10] ShardingSphere-SQL - Logic SQL: INSERT INTO `t_qrcode_consume_details`(`order_no`,`user_id`,`code_type`,`channel`,`update_time`,`amount`,`scene`,`extra`,`paid_charge_id`,`init_status`,`status`,`gmt_create`,`gmt_modify`,`rev`,`pay_channel`,`pay_order_no`) VALUES('00028361564047575000010272479377','1','00','02','2019-07-25 17:39:35',200,'正常乘车扣费',x'',null,'01','01','2019-07-25 17:39:44','2019-07-25 17:39:44',null,'00','ch_17itblm9nkaehv9jc27277')
[INFO ] 10:19:54.816 [ShardingSphere-Command-10] ShardingSphere-SQL - SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@64c000ae, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4f6a3bc6), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4f6a3bc6, columnNames=[order_no, user_id, code_type, channel, update_time, amount, scene, extra, paid_charge_id, init_status, status, gmt_create, gmt_modify, rev, pay_channel, pay_order_no], insertValueContexts=[InsertValueContext(parametersCount=0, valueExpressions=[LiteralExpressionSegment(startIndex=230, stopIndex=263, literals=00028361564047575000010272479377), LiteralExpressionSegment(startIndex=265, stopIndex=267, literals=1), LiteralExpressionSegment(startIndex=269, stopIndex=272, literals=00), LiteralExpressionSegment(startIndex=274, stopIndex=277, literals=02), Li
teralExpressionSegment(startIndex=279, stopIndex=299, literals=2019-07-25 17:39:35), LiteralExpressionSegment(startIndex=301, stopIndex=303, literals=200), LiteralExpressionSegment(startIndex=305, stopIndex=312, literals=正常乘车扣费), LiteralExpressionSegment(startIndex=314, stopIndex=316, literals='), CommonExpressionSegment(startIndex=318, stopIndex=321, text=null), LiteralExpressionSegment(startIndex=323, stopIndex=326, literals=01), LiteralExpressionSegment(startIndex=328, stopIndex=331, literals=01), LiteralExpressionSegment(startIndex=333, stopIndex=353, literals=2019-07-25 17:39:44), LiteralExpressionSegment(startIndex=355, stopIndex=375, literals=2019-07-25 17:39:44), CommonExpressionSegment(startIndex=377, stopIndex=380, text=null), LiteralExpressionSegment(startIndex=382, stopIndex=385, literals=00), LiteralExpressionSegment(startIndex=387, stopIndex=413, literals=ch_17itblm9nkaehv9jc27277)], parameters=[])], generatedKeyContext=Optional.empty)
[INFO ] 10:19:54.817 [ShardingSphere-Command-10] ShardingSphere-SQL - Actual SQL: ds_0 ::: INSERT INTO `t_qrcode_consume_details_1`(`order_no`,`user_id`,`code_type`,`channel`,`update_time`,`amount`,`scene`,`extra`,`paid_charge_id`,`init_status`,`status`,`gmt_create`,`gmt_modify`,`rev`,`pay_channel`,`pay_order_no`) VALUES('00028361564047575000010272479377', '1', '00', '02', '2019-07-25 17:39:35', 200, '正常乘车扣费', ''', null, '01', '01', '2019-07-25 17:39:44', '2019-07-25 17:39:44', null, '00', 'ch_17itblm9nkaehv9jc27277')
[INFO ] 10:19:57.684 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0xd054fba8, L:/0.0.0.0:3307] READ: [id: 0x6101b320, L:/192.168.100.217:3307 - R:/192.168.100.217:37556]
[INFO ] 10:19:57.685 [epollEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0xd054fba8, L:/0.0.0.0:3307] READ COMPLETE
`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-668524797
got it, let me see.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-670370412
@dmbj got it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] dmbj commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
dmbj commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-668906188
> I think the sql is wrong whit `extra = ''' ` , it should be `extra = '\'' `?
>
> > INSERT INTO t_qrcode_consume_details_1(order_no,user_id,code_type,channel,update_time,amount,scene,extra,paid_charge_id,init_status,status,gmt_create,gmt_modify,rev,pay_channel,pay_order_no) VALUES('00028361564047575000010272479377', '1', '00', '02', '2019-07-25 17:39:35', 200, '正常乘车扣费', ''', null, '01', '01', '2019-07-25 17:39:44', '2019-07-25 17:39:44', null, '00', 'ch_17itblm9nkaehv9jc27277')
ths for answer the issue,As you can see,trigger scaling exception is that SQL.but why proxy execute that SQL statement,then you will find the Logic SQL log:`Logic SQL: INSERT INTOt_qrcode_consume_details(order_no,user_id,code_type,channel,update_time,amount,scene,extra,paid_charge_id,init_status,status,gmt_create,gmt_modify,rev,pay_channel,pay_order_no) VALUES('00028361564047575000010272479377','1','00','02','2019-07-25 17:39:35',200,'正常乘车扣费',x'',null,'01','01','2019-07-25 17:39:44','2019-07-25 17:39:44',null,'00','ch_17itblm9nkaehv9jc27277')`
appeal an 'x' in it.
So I guess binlog explain postion SQL wrong,this feildtype in mysql is text,i have already test this field value with NULL & notempty val,i found if the val is NULL, realtime sync work fine, if not got an exception. the reason i thind should be fieldtype text surpport not well in binlog explain SQL statement, you can test it
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-671776230
@Lucas-307 Got it. The test passed.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-671762329
@Lucas-307 I can't execute that SQL when directly connect to a MySQL server.
`insert into test_text(val) values(x'123')`
`Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'123')' at line 1 0.000 sec`
Can you have a check?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-671774504
@tuohai666 The value number should be even, like
`insert into test_text(val) values(x'1234')`
or
`insert into test_text(val) values(x'6162')`
every 2 number is a hexadecimal byte.
x'6162' --> hexadecimal('61' + '62') --> decimal( 97 + 98) --> 'a' + 'b' --> "ab"
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-668531208
I think the sql is wrong whit `extra = ''' ` , it should be `extra = '\'' `?
> INSERT INTO t_qrcode_consume_details_1(order_no,user_id,code_type,channel,update_time,amount,scene,extra,paid_charge_id,init_status,status,gmt_create,gmt_modify,rev,pay_channel,pay_order_no) VALUES('00028361564047575000010272479377', '1', '00', '02', '2019-07-25 17:39:35', 200, '正常乘车扣费', ''', null, '01', '01', '2019-07-25 17:39:44', '2019-07-25 17:39:44', null, '00', 'ch_17itblm9nkaehv9jc27277')
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Lucas-307 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
Lucas-307 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-671243349
@tuohai666 Hi, Yonglun. proxy transfer failed with `text` type sql. Could you help me to fix it.
```sql
CREATE TABLE `test_text` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` text COLLATE utf8_bin,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
```
Logic SQL: `insert into test_text(val) values(x'61626364')`
Actual SQL: ds_0 ::: `insert into test_text(val) values(''61626364')`
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 edited a comment on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
tuohai666 edited a comment on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-671762329
@Lucas-307 I can't execute that SQL when directly connect to a MySQL server. I think we can't insert a binary value into a string column.
`insert into test_text(val) values(x'123')`
`Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'123')' at line 1 0.000 sec`
Can you have a check?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuohai666 commented on issue #6604: sharding scaling&proxy sync realtimedata in mysql with fieldtype text occur exception
Posted by GitBox <gi...@apache.org>.
tuohai666 commented on issue #6604:
URL: https://github.com/apache/shardingsphere/issues/6604#issuecomment-671707471
It seems like you want to insert a hex value, I'll fix it.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org