You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/11/02 08:23:33 UTC
[GitHub] [shardingsphere] sandynz opened a new issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
sandynz opened a new issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407
## Feature Request
### Is your feature request related to a problem?
No
Environment:
ShardingSphere Proxy, 5.0.0.
Commit: bf143245090a3b18589402d81ccddc68eab2e5c7
`scaling` is not enabled in `server.yaml`.
Register center (ZooKeeper) path `/metadata/sharding_db/rules` value:
```
- !SHARDING
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: user_id
defaultTableStrategy:
none: ''
keyGenerators:
snowflake:
props:
worker-id: 123
type: SNOWFLAKE
shardingAlgorithms:
database_inline:
props:
algorithm-expression: ds_${user_id % 2}
type: INLINE
t_order_inline:
props:
algorithm-expression: t_order_${order_id % 2}
type: INLINE
t_order_item_inline:
props:
algorithm-expression: t_order_item_${order_id % 2}
type: INLINE
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
logicTable: t_order
tableStrategy:
standard:
shardingAlgorithmName: t_order_inline
shardingColumn: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
logicTable: t_order_item
tableStrategy:
standard:
shardingAlgorithmName: t_order_item_inline
shardingColumn: order_id
```
### Describe the feature you would like.
Summary: For more convenient usage.
If I want to modify sharding algorithm of `t_order` and `t_order_item`, since there's no `ALTER SHARDING ALGORITHM` statement, I tried `DROP SHARDING ALGORITHM` first, it respond error message:
```
mysql> DROP SHARDING ALGORITHM t_order_inline;
ERROR 1116 (C1116): Sharding algorithms `[t_order_inline]` in schema `sharding_db` are still in used.
```
I got a sugguestion from @RaigorJiang , and it works.
```
mysql> CREATE SHARDING ALGORITHM t_order_inline2 (
-> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id % 4}"))
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE SHARDING ALGORITHM t_order_item_inline2 (
-> TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 4}"))
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER SHARDING TABLE RULE t_order (
-> DATANODES("ds_${2..4}.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_inline2),
-> GENERATED_KEY(COLUMN=order_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
-> ), t_order_item (
-> DATANODES("ds_${2..4}.t_order_item_${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_item_inline2),
-> GENERATED_KEY(COLUMN=order_item_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> DROP SHARDING ALGORITHM t_order_inline;
Query OK, 0 rows affected (0.02 sec)
mysql> DROP SHARDING ALGORITHM t_order_item_inline;
Query OK, 0 rows affected (0.01 sec)
```
And @RaigorJiang memtioned `ALTER SHARDING ALGORITHM` too. If it could run following DistSQL with no error message, it could be much more convenient for users:
```
ALTER SHARDING ALGORITHM t_order_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id % 4}"))
);
ALTER SHARDING ALGORITHM t_order_item_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_item_${order_id % 4}"))
);
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lanchengx commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
lanchengx commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-961567191
Hi @RaigorJiang @sandynz, the function has been implemented
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz closed issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
sandynz closed issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lanchengx commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
lanchengx commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-957461546
@RaigorJiang plz assign to me
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lanchengx commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
lanchengx commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-957461546
@RaigorJiang plz assign to me
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lanchengx commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
lanchengx commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-961567191
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-961860036
@lanchengx Thanks! I'll verify it a little later.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lanchengx commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
lanchengx commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-957461546
@RaigorJiang plz assign to me
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-961860036
@lanchengx Thanks! I'll verify it a little later.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-961860036
@lanchengx Thanks! I'll verify it a little later.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz commented on issue #13407: [DistSQL] Support ALTER SHARDING ALGORITHM
Posted by GitBox <gi...@apache.org>.
sandynz commented on issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407#issuecomment-962771651
I verified on SHARDING tables, it works.
Test DistSQL:
```
ALTER SHARDING TABLE RULE t_order(
RESOURCES(ds_2,ds_3,ds_4),
SHARDING_COLUMN=order_id,
TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=8)),
GENERATED_KEY(COLUMN=order_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
), t_order_item(
RESOURCES(ds_2,ds_3,ds_4),
SHARDING_COLUMN=order_id,
TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=8)),
GENERATED_KEY(COLUMN=order_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
);
```
`t_order` and `t_order_item` in `tables` will be removed and added in `autoTables`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org