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/03/02 18:11:59 UTC
[GitHub] [incubator-shardingsphere] kimmking opened a new issue #4573:
Multi-values insertion bug with key-generator in sharding databases/tables
kimmking opened a new issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
URL: https://github.com/apache/incubator-shardingsphere/issues/4573
## Multi-values insertion bug with key-generator in sharding databases/tables
We can reproduce the bug in a rule:
> shard db by `id%2` : ds_0,ds_1
> shard table by `id%3` : user0,user1,user2
### Case 1, literal multi-values insertion
Execute：
`insert into user(`name`,`age`) values ('name-11',11),('name-12',12),('name-13',13),('name-14',14)`
Actual SQL：
```
..ds_1 ::: insert into user1(`name`,`age`, id) values ('name-11', 11, 441401574401507332)
..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-12', 12, 441401574401507331)
..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-13', 13, 441401574401507330)
..ds_0 ::: insert into user1(`name`,`age`, id) values ('name-14', 14, 441401574401507329)
```
This are two issues：
1. ids in descending order
2. wrong sharding tables, lead to that those data can't be selected
The first row, id=441401574401507332，id%3=>1，so shard to table `user1`，it's right。
The second row, id=441401574401507331，id%3=>0，so should shard to table `user0`，but shard to table `user2`, it's wrong。
The second row, id%3=>2，so should shard to table `user2`，but shard to table `user0`, it's wrong, too。
The last row, id%3=>1，so shard to table `user1`，it's right。
We can find if make ids reverse, remainders are 1,2,0,1，just match the sharding tables number.
We learn more into shardingsphere sourcecodes，it generator 4 keys and binding them to sharding tables at first, like the ascending order. And then binding keys and literal columns fields(name, age)，it reverse keys to descending order by `List.descendingIterator`. Finally, each key orders can't match its own row.
The related codes here：
```
org.apache.shardingsphere.sharding.rewrite.token.generator.impl.keygen.GeneratedKeyInsertValuesTokenGenerator：generateSQLToken()
Iterator<Comparable<?>> generatedValues = generatedKey.getGeneratedValues().descendingIterator();
```
### Case 2, multi-values insertion with placeholders
SQL：
`insert into user(`name`,`age`) values (?,?),(?,?),(?,?),(?,?)`
Java codes：
```
try(PreparedStatement preparedStatement = con.prepareStatement(sql)){
for (int i = 0; i < 4; i++){
preparedStatement.setString(i * 2 + 1,"name-" + i);
preparedStatement.setInt(i * 2 + 2,i);
}
preparedStatement.execute();
}
```
Actual SQL：
```
..ds_0 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-0, 0, 441306093067436035]
..ds_1 ::: insert into user2(`name`,`age`, id) values (?, ?, ?) ::: [name-1, 1, 441306093067436034]
..ds_0 ::: insert into user0(`name`,`age`, id) values (?, ?, ?) ::: [name-2, 2, 441306093067436033]
..ds_1 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-3, 3, 441306093067436032]
```
The same problem，but codes is in different class：
```
org.apache.shardingsphere.sharding.rewrite.parameter.impl.ShardingGeneratedKeyInsertValueParameterRewriter：rewrite()
Iterator<Comparable<?>> generatedValues = shardingRouteContext.getGeneratedKey().get().getGeneratedValues().descendingIterator();
```
### fix
We will fix it by replace `descendingIterator` to `iterator`.
----------------------------------------------------------------
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 commented on issue #4573:
Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
URL: https://github.com/apache/incubator-shardingsphere/issues/4573#issuecomment-608322883
Duplicate with #4306
----------------------------------------------------------------
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] [shardingsphere] terrymanu commented on issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4573:
URL: https://github.com/apache/shardingsphere/issues/4573#issuecomment-696533779
Fixed by #7551
----------------------------------------------------------------
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
[GitHub] [shardingsphere] terrymanu closed issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4573:
URL: https://github.com/apache/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
[GitHub] [incubator-shardingsphere] kimmking edited a comment on issue
#4573: Multi-values insertion bug with key-generator in sharding
databases/tables
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
URL: https://github.com/apache/incubator-shardingsphere/issues/4573#issuecomment-593542444
The code to reproduce bug：
1. put Issue4306Main.java to examples/sharding-jdbc-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/Issue4306Main.java
2. put issue4306.yaml to examples/sharding-jdbc-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/issue4306.yaml
3. run Issue4306Main.java and check the output log
----------------------------------------------------------------
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] [shardingsphere] terrymanu closed issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4573:
URL: https://github.com/apache/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
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4573:
Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
URL: https://github.com/apache/incubator-shardingsphere/issues/4573#issuecomment-593752574
And check case3, preparedStatement&addBatch, no related of this issue.
It's processed one by one as a single insertion.
----------------------------------------------------------------
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 #4573:
Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
URL: https://github.com/apache/incubator-shardingsphere/issues/4573#issuecomment-593542444
Reproduce code：
1. put Issue4306Main.java to examples/sharding-jdbc-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/Issue4306Main.java
2. put issue4306.yaml to examples/sharding-jdbc-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/issue4306.yaml
3. run Issue4306Main.java and check the output log
----------------------------------------------------------------
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 #4573:
Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
URL: 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] kimmking edited a comment on issue
#4573: Multi-values insertion bug with key-generator in sharding
databases/tables
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
URL: https://github.com/apache/incubator-shardingsphere/issues/4573#issuecomment-593542444
The code to reproduce bug：
1. put Issue4306Main.java to examples/sharding-jdbc-example/sharding-example/sharding-raw-jdbc-example/src/main/java/org/apache/shardingsphere/example/sharding/raw/jdbc/Issue4306Main.java
2. put issue4306.yaml to examples/sharding-jdbc-example/sharding-example/sharding-raw-jdbc-example/src/main/resources/META-INF/issue4306.yaml
3. run Issue4306Main.java and check the output log
[Issue4306Main.java.txt](https://github.com/apache/incubator-shardingsphere/files/4277195/Issue4306Main.java.txt)
[issue4306.yaml.txt](https://github.com/apache/incubator-shardingsphere/files/4277194/issue4306.yaml.txt)
----------------------------------------------------------------
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] [shardingsphere] terrymanu commented on issue #4573: Multi-values insertion bug with key-generator in sharding databases/tables
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4573:
URL: https://github.com/apache/shardingsphere/issues/4573#issuecomment-696533779
Fixed by #7551
----------------------------------------------------------------
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