apache / apache/shardingsphere

Is it possible to detect unmatched DATANODES and DATABASE_STRATEGY / TABLE_STRATEGY for tables rule

Open
#18,424 1 comment 0 reactions 0 assignees View on GitHub
feature: sharding type: discussion
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

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.