wsm12138 opened a new issue, #18156: URL: https://github.com/apache/shardingsphere/issues/18156
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? ``` we find java version: java8, full_version=1.8.0_131 ShardingSphere-5.1.2-SNAPSHOT Commit ID: dirty-1b77c33983198a420e974a302d2a891ca05ae664 Commit Message: Support openGauss cursor, close, move and fetch statements route and rewrite in sharding scenario (#18148) Branch: 1b77c33983198a420e974a302d2a891ca05ae664 Build time: 2022-06-02T15:58:32+0800 ``` ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior no error ### Actual behavior ``` hanks for using Atomikos! This installation is not registered yet. REGISTER FOR FREE at http://www.atomikos.com/Main/RegisterYourDownload and receive: - tips & advice - working demos - access to the full documentation - special exclusive bonus offers not available to others - everything you need to get the most out of using Atomikos! [INFO ] 2022-06-02 18:18:12.915 [main] o.a.s.p.v.ShardingSphereProxyVersion - Database name is `MySQL`, version is `5.7.26`, database name is `proxy_tpcc_four` [INFO ] 2022-06-02 18:18:12.978 [main] o.a.s.p.v.ShardingSphereProxyVersion - Database name is `MySQL`, version is `5.7.26`, database name is `proxy_sysbench_four` [INFO ] 2022-06-02 18:18:13.126 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy Memory mode started successfully [ERROR] 2022-06-02 18:18:30.817 [ShardingSphere-Command-8] o.a.s.p.f.c.CommandExecutorTask - Exception occur: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '201-8-3161' for key 'PRIMARY' 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:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1288) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:788) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:41) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:66) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:86) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:66) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.parallelExecute(ExecutorEngine.java:131) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:116) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:71) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:171) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:132) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:126) at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:140) at org.apache.shardingsphere.proxy.frontend.mysql.command.query.binary.execute.MySQLComStmtExecuteExecutor.execute(MySQLComStmtExecuteExecutor.java:118) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:106) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:76) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:748) [ERROR] 2022-06-02 18:18:30.819 [ShardingSphere-Command-106] o.a.s.p.f.c.CommandExecutorTask - Exception occur: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '758-10-3154' for key 'PRIMARY' 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:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1288) at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:788) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ``` ### Reason analyze (If you can) when i use 5.1.1 proxy it didn't occurred error. but when i use 5.1.2 snapshot error occurred. Two proxy have same driver,ds,config. ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. 1. use proxy build tpcc warehouse ##### config-sharding.yaml ``` schemaName: proxy_tpcc_four dataSources: ds_0: url: jdbc:mysql://10.16.9.12:3306/proxy_tpcc_four?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=8000 username: root password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 600 minPoolSize: 1 ds_1: url: jdbc:mysql://10.16.9.62:3306/proxy_tpcc_four?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=8000 username: root password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 600 minPoolSize: 1 ds_2: url: jdbc:mysql://10.16.9.192:3306/proxy_tpcc_four?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=8000 username: root password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 600 minPoolSize: 1 ds_3: url: jdbc:mysql://10.16.9.120:3306/proxy_tpcc_four?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=8000 username: root password: connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 600 minPoolSize: 1 rules: - !SHARDING bindingTables: - bmsql_warehouse, bmsql_customer - bmsql_stock, bmsql_district, bmsql_order_line defaultDatabaseStrategy: none: defaultTableStrategy: none: keyGenerators: snowflake: type: SNOWFLAKE tables: bmsql_config: actualDataNodes: ds_0.bmsql_config bmsql_warehouse: actualDataNodes: ds_${0..3}.bmsql_warehouse databaseStrategy: standard: shardingColumn: w_id shardingAlgorithmName: mod_4 bmsql_district: actualDataNodes: ds_${0..3}.bmsql_district databaseStrategy: standard: shardingColumn: d_w_id shardingAlgorithmName: mod_4 bmsql_customer: actualDataNodes: ds_${0..3}.bmsql_customer databaseStrategy: standard: shardingColumn: c_w_id shardingAlgorithmName: mod_4 bmsql_item: actualDataNodes: ds_${0..3}.bmsql_item databaseStrategy: standard: shardingColumn: i_id shardingAlgorithmName: mod_4 bmsql_history: actualDataNodes: ds_${0..3}.bmsql_history databaseStrategy: standard: shardingColumn: h_w_id shardingAlgorithmName: mod_4 bmsql_oorder: actualDataNodes: ds_${0..3}.bmsql_oorder databaseStrategy: standard: shardingColumn: o_w_id shardingAlgorithmName: mod_4 bmsql_stock: actualDataNodes: ds_${0..3}.bmsql_stock databaseStrategy: standard: shardingColumn: s_w_id shardingAlgorithmName: mod_4 bmsql_new_order: actualDataNodes: ds_${0..3}.bmsql_new_order databaseStrategy: standard: shardingColumn: no_w_id shardingAlgorithmName: mod_4 bmsql_order_line: actualDataNodes: ds_${0..3}.bmsql_order_line databaseStrategy: standard: shardingColumn: ol_w_id shardingAlgorithmName: mod_4 shardingAlgorithms: mod_4: type: MOD props: sharding-count: 4 ``` ##### server.yaml ``` rules: - !AUTHORITY users: - root@%:root provider: type: ALL_PRIVILEGES_PERMITTED props: ``` ##### props.proxy ``` db=postgres driver=com.mysql.jdbc.Driver conn=jdbc:mysql://10.16.9.224:3333/proxy_tpcc_four?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=1024 user=root password= warehouses=1000 loadWorkers=201 terminals=200 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=30 //Number of total transactions per minute limitTxnsPerMin=0 //Set to true to run in 4.x compatible mode. Set to false to use the //entire configured database evenly. terminalWarehouseFixed=false //The following five values must add up to 100 //The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4 // Directory name to create for collecting detailed result data. // Comment this out to suppress. resultDirectory=/root/test_benchmark/run/script/result/proxy/proxy_result_%tY-%tm-%td_%tH%tM%tS_10 osCollectorScript=./misc/os_collector_linux.py osCollectorInterval=1 [email protected] osCollectorDevices=net_eth0 blk_sda ``` ### Example codes for reproduce this issue (such as a github link). -- 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. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
