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 2020/04/03 06:45:17 UTC

[GitHub] [incubator-shardingsphere] cowcomic opened a new issue #5058: SNOWFLAKE can not insert into correct shard

cowcomic opened a new issue #5058: SNOWFLAKE can not insert into correct shard
URL: https://github.com/apache/incubator-shardingsphere/issues/5058
 
 
   ## Bug Report
   i use shardingsphere-proxy for 5 mysql shard
   use proxy for making id by showflake
   but insert data is not correct
   data insert to wrong shard
   
   this is my table
   ```
   CREATE TABLE `person` (
     `id` varchar(32) NOT NULL,
     `name` varchar(16) DEFAULT NULL,
     `age` int(2) DEFAULT NULL,
     `gender` varchar(2) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   ```
   
   this is my insert sql
   ```
   insert `person` (`name`, `age`) VALUES ('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1),('a',1)
   ```
   
   and this is the log
   ```
   [INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_4 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461833), ('a', 1, 452837411152461828)
   [INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461832), ('a', 1, 452837411152461827)
   [INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_1 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461831), ('a', 1, 452837411152461826)
   [INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_2 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461830), ('a', 1, 452837411152461825)
   [INFO ] 14:14:12.246 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_3 ::: insert `person` (`name`, `age`, id) VALUES ('a', 1, 452837411152461829), ('a', 1, 452837411152461824)
   
   ```
   only mod=1 is correct
   so, I cannot select result from this table, such as
   ```
   select * from person where `id` = '452837411152461832'
   ```
   
   this is my config
   ```
   schemaName: sharding_db
   
   
   dataSources:
     ds_0:
       url: jdbc:mysql://127.0.0.1:13386/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: 123456
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 65
     ds_1:
       url: jdbc:mysql://127.0.0.1:13387/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: 123456
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 65
     ds_2:
       url: jdbc:mysql://127.0.0.1:13388/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: 123456
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 65
     ds_3:
       url: jdbc:mysql://127.0.0.1:13389/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: 123456
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 65
     ds_4:
       url: jdbc:mysql://127.0.0.1:13390/shardingsphere?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: 123456
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 65
   
   shardingRule:
     tables:
       person:
         actualDataNodes: ds_${0..4}.person
         databaseStrategy:
           inline:
             shardingColumn: id
             algorithmExpression: ds_${id.toLong() % 5}
   #          algorithmExpression: ds${id % 2}
         keyGenerator:
           type: SNOWFLAKE
           column: id
   ```
   
   
   
   ### Which version of ShardingSphere did you use?
   4.0.0
   
   ### Which project did you use? Sharding-JDBC or Sharding-Proxy?
   Sharding-Proxy
   
   ### Expected behavior
   insert to right sharding
   
   ### Actual behavior
   insert to incorrect sharding
   
   ### Reason analyze (If you can)
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   
   ### Example codes for reproduce this issue (such as a github link).
   

----------------------------------------------------------------
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.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] kimmking commented on issue #5058: SNOWFLAKE can not insert into correct shard

Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #5058: SNOWFLAKE can not insert into correct shard
URL: https://github.com/apache/incubator-shardingsphere/issues/5058#issuecomment-608318683
 
 
   duplicated with https://github.com/apache/incubator-shardingsphere/issues/4573

----------------------------------------------------------------
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.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

[GitHub] [incubator-shardingsphere] terrymanu closed issue #5058: SNOWFLAKE can not insert into correct shard

Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #5058: SNOWFLAKE can not insert into correct shard
URL: https://github.com/apache/incubator-shardingsphere/issues/5058
 
 
   

----------------------------------------------------------------
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.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services