Issue 1:
Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.

Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.

If no response more than 7 days 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.6.4-Sharding-Proxy 4.0.0

Download From https://www.apache.org/dyn/closer.cgi?path=incubator/shardingsphere/4.0.0/apache-shardingsphere-incubating-4.0.0-sharding-jdbc-bin.tar.gz

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

Sharding-Proxy

Expected behavior

I use SQLAlchemy to do a SELECT action like Model.query.limit(10).all()

Actual behavior

Here is the stdout.log

[INFO ] 16:12:02.712 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x2df725aa, L:/0.0.0.0:3307] READ: [id: 0xd7c30a2a, L:/127.0.0.1:3307 - R:/127.0.0.1:50186]
[INFO ] 16:12:02.724 [nioEventLoopGroup-2-1] i.n.handler.logging.LoggingHandler - [id: 0x2df725aa, L:/0.0.0.0:3307] READ COMPLETE
[WARN ] 16:12:02.937 [ShardingSphere-Command-0] o.a.s.s.b.c.j.c.BackendConnection - Current transaction have not terminated, retry count:[1].
[WARN ] 16:12:03.141 [ShardingSphere-Command-0] o.a.s.s.b.c.j.c.BackendConnection - Current transaction have not terminated, retry count:[2].
[WARN ] 16:12:03.346 [ShardingSphere-Command-0] o.a.s.s.b.c.j.c.BackendConnection - Current transaction have not terminated, retry count:[3].
[WARN ] 16:12:03.550 [ShardingSphere-Command-0] o.a.s.s.b.c.j.c.BackendConnection - Current transaction have not terminated, retry count:[4].
[WARN ] 16:12:03.751 [ShardingSphere-Command-0] o.a.s.s.b.c.j.c.BackendConnection - Current transaction have not terminated, retry count:[5].
[ERROR] 16:12:03.751 [ShardingSphere-Command-0] o.a.s.s.b.c.j.c.BackendConnection - Cannot do switch, exceed maximum retry count:[5].
[ERROR] 16:12:03.751 [ShardingSphere-Command-0] o.a.s.s.f.c.CommandExecutorTask - Exception occur:
org.apache.shardingsphere.core.exception.ShardingException: Failed to switch schema, please terminate current transaction.
at org.apache.shardingsphere.shardingproxy.backend.communication.jdbc.connection.BackendConnection.setCurrentSchema(BackendConnection.java:116)
at org.apache.shardingsphere.shardingproxy.backend.text.admin.BroadcastBackendHandler.execute(BroadcastBackendHandler.java:53)
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:92)
at org.apache.shardingsphere.shardingproxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)
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)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

I didn't see any SQL print. The logic SQL select * from t_order execute successfully in Proxy shell.

The config-sharding.yaml

schemaName: sharding_db

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:33065/test_db_0
username: root
password: ****
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 30
ds_1:
url: jdbc:mysql://127.0.0.1:33065/test_db_1
username: root
password: ****
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 30
ds_2:
url: jdbc:mysql://127.0.0.1:33065/test_db_2
username: root
password: ****
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 30

shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..2}.t_order_${0..2}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 3}

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

The table:

CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`status` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

The SQLAlchemy Model:

class Order(db.Model):
__tablename__ = 't_order'
order_id = db.Column(db.BigInteger, primary_key=True)
user_id = db.Column(db.Integer)
status = db.Column(db.VARCHAR(50), server_default="")

def __repr__(self):
return '<Order {id} {status}>'.format(id=self.user_id, status=self.status)

The URI:

SQLALCHEMY_DATABASE_URI = 'mysql://root:root@127.0.0.1:3307/sharding_db'

The Python package:

SQLAlchemy==1.3.12
mysqlclient==1.4.6
Flask_SQLAlchemy==2.4.0
Flask==1.1.1

Issue 2:
Which version of ShardingSphere did you use?

master batch

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

ShardingSphere-Proxy

Expected behavior

/metadata/schemaName/tables broadcast table metadata exist

Actual behavior

/metadata/schemaName/tables broadcast table metadata lost

Reason analyze (If you can)

It may be because Debug finds

TableMetadataLoadRengine's LOAD method leads to the loss of the broadcast

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1、start ShardingSphere-proxy

2、perform distsql

3、Data loss found in ZK