search

apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.
Apache License 2.0
19.9k stars 6.73k forks source link

Failed to switch schema, please terminate current transaction. #5531

Closed mty2015 closed 2 years ago

mty2015 commented 4 years ago

Bug Report

Which version of ShardingSphere did you use?

4.1.0

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-Proxy

Steps to reproduce the behavior

My project developed by Django wants to connect mysql proxy, errors are always reported when initializing the connection, but after the initialization is complete, it can continue to work fine.

I used wireshark to grab the data package that initialized the connection process(the login process is omitted):

SET AUTOCOMMIT = 0
SELECT @@SQL_AUTO_IS_NULL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

get error response: 2Unknown exception: [Failed to switch schema, please terminate current transaction.]

the content of config-sharding.yaml:

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: admin
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: admin
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_id
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        inline:
          shardingColumn: order_id
          algorithmExpression: t_order_item_${order_id % 2}
      keyGenerator:
        type: SNOWFLAKE
        column: order_item_id
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:

Example codes for reproduce this issue (such as a github link).

kimmking commented 4 years ago

what is the whole error logs in proxy console?

cherrylzhao commented 4 years ago

thanks for feedback, I can do some investigate for that.

mty2015 commented 4 years ago

what is the whole error logs in proxy console?

[ERROR] 17:02:22.479 [ShardingSphere-Command-3] o.a.s.s.b.c.j.c.BackendConnection - Cannot do switch, exceed maximum retry count:[5]. [ERROR] 17:02:22.479 [ShardingSphere-Command-3] o.a.s.s.f.c.CommandExecutorTask - Exception occur: org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Failed to switch schema, please terminate current transaction. at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.connection.BackendConnection.setCurrentSchema(BackendConnection.java:112) at org.apache.shardingsphere.shardingproxy.backend.text.admin.BroadcastBackendHandler.execute(BroadcastBackendHandler.java:50) at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:73) at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:93) at org.apache.shardingsphere.shardingproxy.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)

ssxlulu commented 4 years ago

I think sharding-proxy doesn't support the sql syntax SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED which ORM framework send.

mty2015 commented 4 years ago

According to my guess, i think that sharding-proxy did not handle the mysql protocol correctly.

The seq 11: SET AUTOCOMMIT = 0

sharding-proxy_pcapng

but the response of server status is incorrect(the seq 13). If the correct server status is returned here, the client will call SET AUTOCOMMIT = 1 next, and eventually all become normal.

sharding-proxy_pcapng

i verified my guess by directly connecting to the database instead of sharding-proxy, the network packages are as follows:

SET AUTOCOMMIT = 0
SELECT @@SQL_AUTO_IS_NULL
SET AUTOCOMMIT = 1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
cherrylzhao commented 4 years ago

what's the JDBC-driver are you using? from my understand it's very strange sending a SET AUTOCOMMIT =1 command after SET AUTOCOMMIT=0

cherrylzhao commented 4 years ago

similar with #3546, I have some ideas to enhance it recently.

mty2015 commented 4 years ago

what's the JDBC-driver are you using? from my understand it's very strange sending a SET AUTOCOMMIT =1 command after SET AUTOCOMMIT=0

the JDBC-driver is mysql-connector-java-5.1.47.jar, but i think this problem has nothing to do with it, it's related to django initinalization.

The root reason of this problem is that proxy cannot return the correct server satus for set autocommit = 1 | 0. If the client ignores this return, there is no problem. Once the client cares about this state, it will cause client problems

cherrylzhao commented 4 years ago

agree with it, I will do something to be compatible with it.

mty2015 commented 4 years ago

i updated my previous comment

The root reason of this problem is that proxy cannot return the correct server satus for set autocommit = 1 | 0. If the client ignores this return, there is no problem. Once the client cares about this state, it will cause client problems

cherrylzhao commented 4 years ago

yes, there are two problems exist in your issue.

  1. status flag of MySQLOKPacket is wrong, see OKPacket of MySQL
  2. transaction across schema could not be supported in BackendConnection
mty2015 commented 4 years ago

Will the issue be resolved in the 5 milestone or 4.1.x version? I hope to solve it as soon as possible because I need it urgently :)

cherrylzhao commented 4 years ago

it should be enhanced in 5.x, we have not 4.1.x release plan recently.

cherrylzhao commented 4 years ago

@hongfuli can you push your Django project to github, I want to verify the test result

mty2015 commented 4 years ago

@cherrylzhao https://github.com/hongfuli/sharding-proxy-django/

cherrylzhao commented 4 years ago

@hongfuli this example could not run on my pc, please try to test using my branch code: git clone -b issue#5531-test https://github.com/cherrylzhao/incubator-shardingsphere.git

mty2015 commented 4 years ago

git clone -b issue#5531-test https://github.com/cherrylzhao/incubator-shardingsphere.git

build failed :(

[INFO] Running org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest
[ERROR] Tests run: 3, Failures: 0, Errors: 3, Skipped: 0, Time elapsed: 0.005 s <<< FAILURE! - in org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest
[ERROR] assertIsErrorResponse(org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest)  Time elapsed: 0.001 s  <<< ERROR!
java.lang.NullPointerException
    at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest.<init>(MySQLComQueryPacketExecutorTest.java:50)

[ERROR] assertIsQuery(org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest)  Time elapsed: 0 s  <<< ERROR!
java.lang.NullPointerException
    at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest.<init>(MySQLComQueryPacketExecutorTest.java:50)

[ERROR] assertIsUpdateResponse(org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest)  Time elapsed: 0 s  <<< ERROR!
java.lang.NullPointerException
    at org.apache.shardingsphere.shardingproxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutorTest.<init>(MySQLComQueryPacketExecutorTest.java:50)

[INFO]
[INFO] Results:
[INFO]
[ERROR] Errors:
[ERROR]   MySQLCommandExecutorFactoryTest.assertNewInstance:59 » NullPointer
[ERROR]   MySQLComStmtExecuteExecutorTest.assertIsErrorResponse:59 » NullPointer
[ERROR]   MySQLComStmtExecuteExecutorTest.assertIsQuery:88 » NullPointer
[ERROR]   MySQLComStmtExecuteExecutorTest.assertIsUpdateResponse:74 » NullPointer
[ERROR]   MySQLComQueryPacketExecutorTest.<init>:50 » NullPointer
[ERROR]   MySQLComQueryPacketExecutorTest.<init>:50 » NullPointer
[ERROR]   MySQLComQueryPacketExecutorTest.<init>:50 » NullPointer
[INFO]
[ERROR] Tests run: 33, Failures: 0, Errors: 7, Skipped: 0
cherrylzhao commented 4 years ago

I have fixed the unit test error, you can pull it again or just -DskipTests

mty2015 commented 4 years ago

I still have the same problem.

Actually you don't need Django to verify, just need to check the status flag after sending the requestSET AUTOCOMMIT = 0.

cherrylzhao commented 4 years ago

yes I know, but it's very difficult to implement SERVER_SESSION_STATE_CHANGED protocol in current architecture, so I want to test a alternate way

mty2015 commented 4 years ago

@cherrylzhao Can I use a fixed version for this problem?

ChuckPerry commented 4 years ago

did this problem resoled? I catch same problem in qrtz frame.

lijintaotzx commented 4 years ago

Has the problem been solved ? I have the same problem in my django project.

xingsuo-zbz commented 4 years ago

The same error info in sqoop import with sqoop-1.4.6+sharding-proxy-5.0.0-RC1

nimeijian commented 3 years ago

When I use dataX to connect shardingproxy, I encountered the same error. I tried version 3.1.0/4.1.1/5.0.0-alpha, all have this problem.

yoloz commented 3 years ago

use dbcp2 and connection.setAutoCommit(false), get the same error, mysqlProxy version 5.0-alpha

huangtaogege commented 2 years ago

当我使用dataX连接分片代理时，我遇到了同样的错误。我试过3.1.0/4.1.1/5.0.0-alpha版本，都存在这个问题。

 有解决么

userkdg commented 2 years ago

+1 都到2021年尾了，bug还在？

anxinlong commented 2 years ago

+1 this issue has been open for more than one year😂

jingshanglu commented 2 years ago

It maybe fixed by #14983 .