apache / apache/shardingsphere
Wrong route result when execute create table statement with single rule
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 11h 35m
- Merged PRs (30d)
- 326
Description
## Bug Report
### Which version of ShardingSphere did you use?
[6025cdd](https://github.com/apache/shardingsphere/commit/6025cdd0b0f679f2582fbe45d6ba0cf9d88e48fd)
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Route according to single rule when I execute create table statement.
### Actual behavior
```
mysql> PREVIEW CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_2 | CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> PREVIEW CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0 | CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> PREVIEW CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_1 | CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> PREVIEW CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_2 | CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Yaml config:
```yaml
databaseName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_2?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: t_order_inline
shardingAlgorithms:
t_order_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2}
- !BROADCAST
tables:
- t_config
- !SINGLE
tables:
- ds_2.t_single
```
Then execute `PREVIEW CREATE TABLE `t_single` ( `single_id` bigint NOT NULL, `status` varchar(50) NOT NULL, `creation_date` date NOT NULL, PRIMARY KEY (`single_id`) ) ENGINE=InnoDB;`
### Example codes for reproduce this issue (such as a github link).
Contributor guide
Research direction
Start by reproducing the PREVIEW CREATE TABLE statement with the supplied ShardingSphere-Proxy YAML configuration, then trace how SINGLE-rule tables are routed. Confirm the fix by checking that t_single consistently routes to ds_2 and add or run a regression test for this case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, mysql
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100