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