apache / apache/shardingsphere

Wrong route result when execute create table statement with single rule

Open
#26,693 3 comments 0 reactions 0 assignees View on GitHub
feature: single type: bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.