apache / apache/shardingsphere
Is it possible to detect unmatched DATANODES and DATABASE_STRATEGY / TABLE_STRATEGY for tables rule
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 9h 55m
- Merged PRs (30d)
- 311
Description
## Feature Request
### Is your feature request related to a problem?
Yes
### Describe the feature you would like.
I've found several times in GitHub issues and our forums, unmatched DATANODES and DATABASE_STRATEGY / TABLE_STRATEGY might cause issue.
Is it possible to detect unmatched DATANODES and DATABASE_STRATEGY / TABLE_STRATEGY for tables rule?
#### 2 examples that cause issue
Example 1:
```
mysql> CREATE SHARDING ALGORITHM database_inline (
-> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
-> );
Query OK, 0 rows affected (0.87 sec)
mysql> CREATE SHARDING ALGORITHM t_order_inline (
-> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> CREATE SHARDING TABLE RULE t_order (
-> DATANODES("ds_${0..1}.t_order_0"),
-> DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
-> TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_inline),
-> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
-> );
Query OK, 0 rows affected (0.55 sec)
```
`DATANODES("ds_${0..1}.t_order_0")` is wrong, it should be `DATANODES("ds_${0..1}.t_order_${0..1}")`, but it's not detected.
Example 2:
```
mysql> CREATE SHARDING ALGORITHM database_inline (
-> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 2}"))
-> );
Query OK, 0 rows affected (0.87 sec)
mysql> CREATE SHARDING ALGORITHM t_order_inline (
-> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id % 2}"))
-> );
Query OK, 0 rows affected (0.45 sec)
mysql> CREATE SHARDING TABLE RULE t_order (
-> DATANODES("ds_${0..1}.t_order_${0..1}"),
-> DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
-> TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_inline),
-> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=snowflake))
-> );
Query OK, 0 rows affected (0.55 sec)
mysql> CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.86 sec)
mysql> ALTER SHARDING ALGORITHM database_inline (
-> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="ds_${user_id % 3 + 2}"))
-> );
Query OK, 0 rows affected (0.55 sec)
mysql> insert into t_order (order_id, user_id, status) values (1,2,'ok');
ERROR 1997 (C1997): Runtime exception: [Route table ds_4 does not exist, available actual table: [ds_0, ds_1]]
```
#### Possible solution?
It might be difficult to detect it, since we could not iterate all values and evaluate `algorithm-expression` to get all data nodes.
Any ideas?
Contributor guide
Research direction
The issue names no source files, tests, or entry points. Begin by reproducing both SQL examples in ShardingSphere and tracing validation for sharding table rules; done means unmatched DATANODES and strategy ranges are detected, or the supported limitation is documented.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- backend, databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 30/100