tuohai666 opened a new issue #9611: URL: https://github.com/apache/shardingsphere/issues/9611
## 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? 5.0.0-RC1-SNAPSHOT ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior The following series SQLs executed correctly: ``` Query(Statement): 'BEGIN' Prepare Statement(Statement ID = 1): 'COMMIT' Prepare Statement(Statement ID = 2): SELECT c FROM sbtest1 WHERE id=? Execute Statement: Statement ID = 2 Execute Statement: Statement ID = 1 ``` ### Actual behavior Proxy throws Exception at the last SQL(Execute Statement: Statement ID = 1): ``` [ERROR] 2021-03-08 21:00:32.228 [pool-7-thread-1] o.a.s.p.f.c.CommandExecutorTask - Exception occur: java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) 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:72) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:65) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:43) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:82) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:61) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:99) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:85) at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:80) at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:66) at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:61) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:163) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:123) at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:112) at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:112) at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:101) at org.apache.shardingsphere.proxy.frontend.mysql.command.query.binary.execute.MySQLComStmtExecuteExecutor.execute(MySQLComStmtExecuteExecutor.java:78) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:93) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:71) 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) ``` ### Reason analyze (If you can) The exception **The command cannot be executed when global transaction is in the ACTIVE state** can be found in https://dev.mysql.com/doc/refman/5.7/en/xa-states.html . ``` Within the context of a given client connection, XA transactions and local (non-XA) transactions are mutually exclusive. For example, if XA START has been issued to begin an XA transaction, a local transaction cannot be started until the XA transaction has been committed or rolled back. Conversely, if a local transaction has been started with START TRANSACTION, no XA statements can be used until the transaction has been committed or rolled back. ``` The XA transaction codes are only in TextProtocolBackendHandler. In this case, the 'COMMIT' comes from the binary protocol which Proxy will not deal with. The DatabaseCommunicationEngine execute the 'COMMIT' and send it directly to MySQL server (this behavior is local transaction) . ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. ### 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. For queries about this service, please contact Infrastructure at: [email protected]
