Issue 1:
Question

When using mybatis3.4.x for query. It will invoke PreparedStatementHandler.query, it's code is :

public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
PreparedStatement ps = (PreparedStatement) statement;
ps.execute();
return resultSetHandler.<E> handleResultSets(ps);
}

It will invoke the PreparedStatement.execute() not the PreparedStatement.executeQuery(), so it can't invoke the ShardingPrepareStatement.executeQuery() too.

So How Can i invoke的executeQuery() when use Mybatis?

Thanks a lot!

Issue 2:
Bug Report

I open two ShardingSphere-Proxy, they share the same zookeeper and MySQL. When i execute Scaling distsql, didn't get the expected results, seem both proxy tryLock failed.

Related class is PipelineJobExecutor,

Which version of ShardingSphere did you use?

master 5839a53

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

ShardingSphere-Proxy

Expected behavior

One of the Proxy will get the lock.

Actual behavior

None of them get the lock.

Reason analyze (If you can)

Maybe the problem is isAckOK at acquire, but i'm not sure, because debug may affects the lock

private boolean acquire(final String lockName, final long timeout) {
long consumeTime = 0;
String currentInstanceId = getCurrentInstanceId();
do {
boolean isLocked = lockService.tryLock(lockName, TimeoutMilliseconds.DEFAULT_REGISTRY);
consumeTime += TimeoutMilliseconds.DEFAULT_REGISTRY;
if (isLocked) {
lockedInstances.add(currentInstanceId);
if (isAckOK(timeout - consumeTime)) {
return true;
} else {
lockedInstances.remove(currentInstanceId);
return false;
}
}
} while (timeout > consumeTime);
log.debug("inner try lock acquire timeout, lockName={}", lockName);
return false;
}

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

Step 1

Prepare environment

zookeeper: 3.8

MySQL: 8.0

docker command

docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 -e TZ=Asia/Shanghai -d mysql:8 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

docker run --name test-zk -p 2181:2181 -d zookeeper:3.8

then init database

CREATE DATABASE ds_0;
CREATE DATABASE ds_1;
CREATE DATABASE ds_2;
CREATE DATABASE ds_3;
CREATE DATABASE ds_4;

ShardingSphere-Proxy config

server.yaml

mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: localhost:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: false

rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PERMITTED
- !SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
concurrencyLevel: 4
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
concurrencyLevel: 4

props:
max-connections-size-per-query: 1
kernel-executor-size: 16 # Infinite by default.
proxy-frontend-flush-threshold: 128 # The default value is 128.
proxy-hint-enabled: false
sql-show: false
check-table-metadata-enabled: false
show-process-list-enabled: false
# Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
# The default value is -1, which means set the minimum value for different JDBC drivers.
proxy-backend-query-fetch-size: -1
check-duplicate-table-enabled: false
proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
# Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
# and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
proxy-backend-executor-suitable: OLAP
proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
sql-federation-enabled: false
# Available proxy backend driver type: JDBC (default), ExperimentalVertx
proxy-backend-driver-type: JDBC
proxy-default-port: 3307 # Proxy default port.

config-sharding.yaml

databaseName: sharding_db

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/ds_0?serverTimezone=UTC&useSSL=false
username: root
password: root
maxPoolSize: 5
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/ds_1?serverTimezone=UTC&useSSL=false
username: root
password: root
maxPoolSize: 5
minPoolSize: 1
ds_2:
url: jdbc:mysql://127.0.0.1:3306/ds_2?serverTimezone=UTC&useSSL=false
username: root
password: root
maxPoolSize: 5
minPoolSize: 1
ds_3:
url: jdbc:mysql://127.0.0.1:3306/ds_3?serverTimezone=UTC&useSSL=false
username: root
password: root
maxPoolSize: 5
minPoolSize: 1
ds_4:
url: jdbc:mysql://127.0.0.1:3306/ds_4?serverTimezone=UTC&useSSL=false
username: root
password: root
maxPoolSize: 5
minPoolSize: 1

rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:

shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}

keyGenerators:
snowflake:
type: SNOWFLAKE

scalingName: default_scaling
scaling:
default_scaling:
input:
workerThread: 40
batchSize: 1000
output:
workerThread: 40
batchSize: 1000
streamChannel:
type: MEMORY
props:
block-queue-size: 10000
completionDetector:
type: IDLE
props:
incremental-task-idle-seconds-threshold: 1800
dataConsistencyChecker:
type: DATA_MATCH
props:
chunk-size: 1000

Step 2

Start both proxy, wait them start success.

Step 3

Connect to proxy, mysql -h 127.0.0.1 -u root -p -P3308

execute scaling dist sql

mysql> use sharding_db;
Database changed

mysql> CREATE TABLE t_order (order_id int NOT NULL,user_id int NOT NULL,status varchar(50) DEFAULT NULL,PRIMARY KEY (order_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.17 sec)

mysql> INSERT INTO t_order(order_id, user_id, status) VALUES (1, 1, 'SUCCESS'),(2, 2, 'FAIL'),(3, 3, 'FAIL'),(4, 4, 'FAIL');
Query OK, 4 rows affected (0.46 sec)

mysql> ALTER SHARDING TABLE RULE t_order(RESOURCES(ds_2, ds_3, ds_4),SHARDING_COLUMN=order_id,TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=6)),KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake)));
Query OK, 0 rows affected (0.90 sec)

mysql> show scaling list;
+----------------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id | tables | sharding_total_count | active | create_time | stop_time |
+----------------------------------------------+---------+----------------------+--------+---------------------+-----------+
| 0130317c30317c3054317c7368617264696e675f6462 | t_order | 2 | true | 2022-05-13 10:29:57 | NULL |
+----------------------------------------------+---------+----------------------+--------+---------------------+-----------+
1 row in set (0.25 sec)

mysql> show scaling status 0130317c30317c3054317c7368617264696e675f6462;
+------+-------------+--------+--------+-------------------------------+--------------------------+
| item | data_source | status | active | inventory_finished_percentage | incremental_idle_seconds |
+------+-------------+--------+--------+-------------------------------+--------------------------+
| 0 | | | | | |
| 1 | | | | | |
+------+-------------+--------+--------+-------------------------------+--------------------------+
2 rows in set (0.03 sec)