「容灾」「测试」关于部分分库下线后，SQL 语句能否执行的问题

[RaigorJiang]

状态：原创

背景

有用户提出疑问：

当应用接入 SS 进行分库分表，其中一个数据源下线后，路由到另一个数据源的 SQL 语句可以执行吗？

测试验证

环境

接入端：Proxy

版本：master #bd7f5ca8

server.yaml

rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PERMITTED

props:
  sql-show: true

config-sharding-databases-tables.yaml

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 10
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3309/demo_ds?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 10
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: order_inline
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}

配置了两个 resource，分别是 3306 和 3309 端口

测试过程

1. 启动 Proxy

2. DROP TABLE

mysql> DROP TABLE IF EXISTS t_order;
Query OK, 0 rows affected (0.06 sec)

日志：
ShardingSphere-SQL - Actual SQL: ds_1 ::: DROP TABLE IF EXISTS t_order_0
ShardingSphere-SQL - Actual SQL: ds_1 ::: DROP TABLE IF EXISTS t_order_1
ShardingSphere-SQL - Actual SQL: ds_0 ::: DROP TABLE IF EXISTS t_order_0
ShardingSphere-SQL - Actual SQL: ds_0 ::: DROP TABLE IF EXISTS t_order_1

3. CREATE TABLE

mysql> CREATE TABLE `t_order` (
    ->   `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `user_id` int(11) NOT NULL,
    ->   `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
    ->   PRIMARY KEY (`order_id`)
    -> );
Query OK, 0 rows affected (0.63 sec)

日志：
ShardingSphere-SQL - Actual SQL: ds_1 ::: CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)
ShardingSphere-SQL - Actual SQL: ds_1 ::: CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)
ShardingSphere-SQL - Actual SQL: ds_0 ::: CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)
ShardingSphere-SQL - Actual SQL: ds_0 ::: CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
)

4. INSERT ds_1

mysql> INSERT INTO t_order (order_id, user_id, status) VALUES 
    -> (1,1,'OK');
Query OK, 1 row affected (0.56 sec)

日志：
ShardingSphere-SQL - Actual SQL: ds_1 ::: INSERT INTO t_order_1 (order_id, user_id, status) VALUES 
(1, 1, 'OK')

5. INSERT ds_0

mysql> INSERT INTO t_order (order_id, user_id, status) VALUES  (2,2,'OK');
Query OK, 1 row affected (0.02 sec)

日志：
ShardingSphere-SQL - Actual SQL: ds_0 ::: INSERT INTO t_order_0 (order_id, user_id, status) VALUES  (2, 2, 'OK')

6. 关闭数据库 ds_1

7. INSERT ds_1

出现异常，符合预期

mysql> INSERT INTO t_order (order_id, user_id, status) VALUES  (3,3,'OK');
ERROR: 
HikariPool-2 - Connection is not available, request timed out after 30004ms.

日志：
ShardingSphere-SQL - Actual SQL: ds_1 ::: INSERT INTO t_order_1 (order_id, user_id, status) VALUES  (3, 3, 'OK')
com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Failed to validate connection com.mysql.jdbc.JDBC4Connection@4613311f (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
[HikariPool-2 connection closer] com.zaxxer.hikari.pool.PoolBase - HikariPool-2 - Closing connection com.mysql.jdbc.JDBC4Connection@4613311f: (connection is dead)
[DEBUG] 2022-08-06 01:00:39.526 [HikariPool-2 connection adder] com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - Cannot acquire connection from data source
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
        at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)

8. INSERT ds_0

mysql> INSERT INTO t_order (order_id, user_id, status) VALUES  (4,4,'OK');
Query OK, 1 row affected (0.02 sec)

日志：
ShardingSphere-SQL - Actual SQL: ds_0 ::: INSERT INTO t_order_0 (order_id, user_id, status) VALUES  (4, 4, 'OK')

9. SELECT ds_1

出现异常，符合预期

mysql> select * from t_order where user_id = 3;
ERROR: 
HikariPool-2 - Connection is not available, request timed out after 30002ms.

日志：
ShardingSphere-SQL - Actual SQL: ds_1 ::: select * from t_order_0 where user_id = 3 UNION ALL select * from t_order_1 where user_id = 3
[DEBUG] 2022-08-06 01:01:37.890 [HikariPool-2 connection adder] com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - Cannot acquire connection from data source

10. SELECT ds_0

mysql> select * from t_order where user_id = 4;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        4 |       4 | OK     |
+----------+---------+--------+
1 row in set (0.01 sec)

日志：
ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from t_order_0 where user_id = 4 UNION ALL select * from t_order_1 where user_id = 4

完整操作过程

11. 补充：全路由查询对比

测试结论

1. 当精确路由的结果不涉及到异常数据源时，SQL 可以执行成功；

2. 当路由结果包含异常数据源时，SQL 无法执行成功，客户端得到异常。

注意：

• 以上测试针对的是 Proxy 运行中分库下线的情况，若 Proxy 启动前已经有数据源下线，这种情况是无法启动成功的，需要先处理数据源问题；
• JDBC 接入的情况类似。