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/02/14 08:26:26 UTC
[GitHub] [incubator-shardingsphere] saboluo opened a new issue #4306: Why do
distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
saboluo opened a new issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306
## Question
**For English only**, other languages will not accept.
Before asking a question, make sure you have:
- Googled your question.
- Searched open and closed [GitHub issues](https://github.com/apache/incubator-shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response **more than 7 days** and we cannot reproduce it on current information, we will **close it**.
source code
class ShardingGeneratedKeyInsertValueParameterRewriter
`public void rewrite(final ParameterBuilder parameterBuilder, final SQLStatementContext sqlStatementContext, final List<Object> parameters) {
Preconditions.checkState(sqlRouteResult.getGeneratedKey().isPresent());
((GroupedParameterBuilder) parameterBuilder).setDerivedColumnName(sqlRouteResult.getGeneratedKey().get().getColumnName());
**Iterator<Comparable<?>> generatedValues = sqlRouteResult.getGeneratedKey().get().getGeneratedValues().descendingIterator();**
int count = 0;
int parametersCount = 0;
for (List<Object> each : ((InsertSQLStatementContext) sqlStatementContext).getGroupedParameters()) {
parametersCount += ((InsertSQLStatementContext) sqlStatementContext).getInsertValueContexts().get(count).getParametersCount();
Comparable<?> generatedValue = generatedValues.next();
if (!each.isEmpty()) {
((GroupedParameterBuilder) parameterBuilder).getParameterBuilders().get(count).addAddedParameters(parametersCount, Lists.<Object>newArrayList(generatedValue));
}
count++;
}
}`
----------------------------------------------------------------
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 #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-587000071
Should we add an option to decide desc or asc here?
----------------------------------------------------------------
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] saboluo commented on issue #4306: Why do
distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
saboluo commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-586191359
When I use 'insert into XXX (xx,xx) values(?,?),(?,?)' statement inserts data and use SNOWFLAKE algorithm to generate the distribution key, the sharding results are not the same as expected.
----------------------------------------------------------------
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 #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4306:
URL: https://github.com/apache/shardingsphere/issues/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
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-593337142
Hi, @saboluo
I have reprodeced your case:
```
..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]
```
Two points：
1. descendingIterator id；
2. wrong mod lead to wrong sharding table.
Row 2, 441306093067436034 % 3 == 0, but inserted into user2 not user0.
----------------------------------------------------------------
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 #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-593517683
This is a bug, I will fix it ASAP.
----------------------------------------------------------------
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
#4306: Why do distributed primary keys need to be retrieved in reverse
order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-593351335
And I try:
```
Logic SQL: insert into user(`name`,`age`)
values ('name-11',11),('name-12',12),('name-13',13),('name-14',14);
..ds_1 ::: insert into user2(`name`,`age`, id) values ('name-11', 11, 441315240232091649)
..ds_0 ::: insert into user0(`name`,`age`, id) values ('name-12', 12, 441315240232091648)
..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-13', 13, 441315240227897346)
..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-14', 14, 441315240227897345)
```
It's the same.
----------------------------------------------------------------
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] saboluo edited a comment on issue #4306:
Why do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
saboluo edited a comment on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-587024629
version 4.0.0
Thank you for reply
When I inserted the data using the above statement, I could not query the result using the
`select id from user where id='435366707763085314'`
statement because 435366707763085314% 3 = 0
----------------------------------------------------------------
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 #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-586164431
Did you meet some problem?
----------------------------------------------------------------
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 #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-586992727
What is your version?
----------------------------------------------------------------
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
#4306: Why do distributed primary keys need to be retrieved in reverse
order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-586367763
What's your expected results, and the actual results in your case?
The details is better for understanding your problem.
-------------------------------------
You means you wanna insert(id) values(1),(2)
but get values(2),(1) ?
----------------------------------------------------------------
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 #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4306:
URL: https://github.com/apache/shardingsphere/issues/4306#issuecomment-696533561
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] [incubator-shardingsphere] kimmking commented on issue #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-593351335
And I try:
```
Logic SQL:
insert into user(`name`,`age`) values ('name-11',11),('name-12',12),('name-13',13),('name-14',14);
..ds_1 ::: insert into user2(`name`,`age`, id) values ('name-11', 11, 441315240232091649)
..ds_0 ::: insert into user0(`name`,`age`, id) values ('name-12', 12, 441315240232091648)
..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-13', 13, 441315240227897346)
..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-14', 14, 441315240227897345)
```
It's the same.
----------------------------------------------------------------
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] saboluo commented on issue #4306: Why do
distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
saboluo commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-587545007
right
----------------------------------------------------------------
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
#4306: Why do distributed primary keys need to be retrieved in reverse
order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-593351335
And I try:
```
Logic SQL: insert into user(`name`,`age`)
values ('name-11',11),('name-12',12),('name-13',13),('name-14',14);
..ds_1 ::: insert into user2(`name`,`age`, id) values ('name-11', 11, 441315240232091649)
..ds_0 ::: insert into user0(`name`,`age`, id) values ('name-12', 12, 441315240232091648)
..ds_0 ::: insert into user2(`name`,`age`, id) values ('name-13', 13, 441315240227897346)
..ds_1 ::: insert into user0(`name`,`age`, id) values ('name-14', 14, 441315240227897345)
```
It's the same.
So I guess descendingIterator is a mistake, I will try a go
----------------------------------------------------------------
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 #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4306:
URL: https://github.com/apache/shardingsphere/issues/4306#issuecomment-696533561
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] [incubator-shardingsphere] kimmking commented on issue #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-587541189
you means this data should be insert into user0, but insert into user2 actually.
----------------------------------------------------------------
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] saboluo commented on issue #4306: Why do
distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
saboluo commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-587024629
version 4.0.0
----------------------------------------------------------------
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] saboluo commented on issue #4306: Why do
distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
saboluo commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-586539549
**config file:**
```
dataSources:
lunar0: !!com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3307/lunar0
username:
password:
initialSize: 10
maxActive: 10
maxWait: 60000
keepAlive: true
lunar1: !!com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3308/lunar1
username:
password:
initialSize: 10
maxActive: 10
maxWait: 60000
keepAlive: true
shardingRule:
tables:
user:
actualDataNodes: lunar${0..1}.user${0..2}
databaseStrategy:
inline:
shardingColumn: id
algorithmExpression: lunar${id % 2}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: user${id % 3}
keyGenerator:
type: SNOWFLAKE
column: id
defaultDataSourceName: lunar0
defaultTableStrategy:
none:
props:
sql.show: true
```
**code:**
```
URL url = Main.class.getClassLoader().getResource("configFile.yaml");
DataSource dataSource = YamlShardingDataSourceFactory.createDataSource(new File(url.getFile()));
String sql = "insert into user(`name`,`age`) values (?,?),(?,?),(?,?),(?,?)";
try(Connection con = dataSource.getConnection()){
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();
}
}
```
**logic table user:**
```
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
**result:**
Actual SQL: lunar0 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-0, 0, 435366707763085315]
Actual SQL: lunar1 ::: insert into user2(`name`,`age`, id) values (?, ?, ?) ::: [name-1, 1, 435366707763085314]
Actual SQL: lunar0 ::: insert into user0(`name`,`age`, id) values (?, ?, ?) ::: [name-2, 2, 435366707763085313]
Actual SQL: lunar1 ::: insert into user1(`name`,`age`, id) values (?, ?, ?) ::: [name-3, 3, 435366707763085312]
----------------------------------------------------------------
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 #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4306:
URL: https://github.com/apache/shardingsphere/issues/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
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4306: Why
do distributed primary keys need to be retrieved in reverse order in
ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4306: Why do distributed primary keys need to be retrieved in reverse order in ShardingGeneratedKeyInsertValueParameterRewriter.rewrite
URL: https://github.com/apache/incubator-shardingsphere/issues/4306#issuecomment-586367763
What's your expected results, and the actual results in your case?
The details is better for understanding your problem.
----------------------------------------------------------------
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