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 2022/10/11 04:11:05 UTC
[GitHub] [shardingsphere] ningzio opened a new issue, #21475: INLINE script cannot handle int64 id as expected
ningzio opened a new issue, #21475:
URL: https://github.com/apache/shardingsphere/issues/21475
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/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 anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
docker image: `apache/shardingsphere-proxy:5.2.0`
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
**ShardingSphere-Proxy**
### Expected behavior
I use a custom snowflake algorithm to generate unique ID, I expected to use bit manipulation to calculate table shard.
this is my INLINE script
```yaml
shardingAlgorithms:
t_test_inline:
type: INLINE
props:
algorithm-expression: t_test_$->{ (id & (1 << 4) - 1 << 18) >> 18 }_$->{ (id & (1 << 8) - 1 << 10) >> 10 }
```
when I insert a record with id `278935294492016641`, It will insert into actual table **t_test_1_0**
```mysql
MySQL root@localhost:sharding_db> insert into t_test(id, title) values (278935294492016641, '')
Query OK, 1 row affected
Time: 0.053s
```
this is the log from shardingsphere-proxy
```
[INFO ] 2022-10-11 03:45:45.689 [ShardingSphere-Command-0] ShardingSphere-SQL - Actual SQL: test ::: insert into t_test_1_0(id, title) values (278935294492016641, '')
```
### Actual behavior
but some id does not work as expected
```mysql
MySQL root@localhost:sharding_db> insert into t_test(id, title) values (278935034474528769, '')
(20050, 'Routed target `t_test_14_255` does not exist, available targets are `[t_test_0_0, t_test_0_1, t_test_0_2, t_test_0_3, t_test_0_4, t_test_1_0, t_test_1_1, t_test_1_2, t_test_1_3, t_test_1_4]`')
```
I was expected this record to be inserted into actual table **t_test_1_0**
and no log from shardingsphere-proxy
I don't see any problem from this ID
```python
In [25]: (278935034474528769 & (1 << 4) - 1 << 18) >> 18
Out[25]: 1
In [26]: (278935034474528769 & (1 << 8) - 1 << 10) >> 10
Out[26]: 0
In [27]: bin(278935034474528769)
Out[27]: '0b1111011110111110011110111110011101010001000000000000000001'
```
### Reason analyze (If you can)
I tried to change INLINE script like this
```yaml
shardingAlgorithms:
t_test_inline:
type: INLINE
props:
# output id directly
algorithm-expression: t_test_$->{ id }_$->{ (id & ((1 << 4) -1 << 18)) >> 18 }_$->{ (id & ((1 << 8) -1 << 10)) >> 10 }
```
```mysql
MySQL root@localhost:sharding_db> insert into t_test(id, title) values (278935034474528769, '')
(20050, 'Routed target `t_test_1656487935_14_255` does not exist, available targets are `[t_test_0_0, t_test_0_1, t_test_0_2, t_test_0_3, t_test_0_4, t_test_1_0, t_test_1_1, t_test_1_2, t_test_1_3, t_test_1_4]`')
```
this is strange, I don't know why this happened.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
create schema
```mysql
create database test_0 default character set 'utf8mb4';
create table t_test_(0..1)_(0..4)
(
id bigint primary key,
title varchar(8) not null default ''
);
```
server.yaml
```yaml
rules:
- !AUTHORITY
users:
- root@:root
provider:
type: ALL_PERMITTED
props:
sql-show: true
```
config-sharding.yaml
```yaml
databaseName: sharding_db
dataSources:
test:
url: jdbc:mysql://localhost:3306/test_0?serverTimezone=UTC&useSSL=false
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
t_test:
actualDataNodes: test.t_test_${0..1}_${0..4}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: t_test_inline
bindingTables:
- t_test
shardingAlgorithms:
t_test_inline:
type: INLINE
props:
algorithm-expression: t_test_$->{ (id & ((1 << 4) -1 << 18)) >> 18 }_$->{ (id & ((1 << 8) -1 << 10)) >> 10 }
```
ext-lib
```
ext-lib
└── mysql-connector-java-8.0.11.jar
```
run docker
```shell
docker run (mount conf and ext-lib) -e PORT=3308 -p 13308:3308 apache/shardingsphere-proxy:5.2.0
```
### 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] ningzio commented on issue #21475: INLINE script cannot handle int64 id as expected
Posted by GitBox <gi...@apache.org>.
ningzio commented on issue #21475:
URL: https://github.com/apache/shardingsphere/issues/21475#issuecomment-1275698714
is there any solution?
--
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] ningzio commented on issue #21475: INLINE script cannot handle int64 id as expected
Posted by GitBox <gi...@apache.org>.
ningzio commented on issue #21475:
URL: https://github.com/apache/shardingsphere/issues/21475#issuecomment-1313174842
> Maybe it has been fixed by #21564, You can try it with version 5.2.1.
5.2.1 works, thank you!
--
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] TeslaCN closed issue #21475: INLINE script cannot handle int64 id as expected
Posted by GitBox <gi...@apache.org>.
TeslaCN closed issue #21475: INLINE script cannot handle int64 id as expected
URL: https://github.com/apache/shardingsphere/issues/21475
--
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] ningzio commented on issue #21475: INLINE script cannot handle int64 id as expected
Posted by GitBox <gi...@apache.org>.
ningzio commented on issue #21475:
URL: https://github.com/apache/shardingsphere/issues/21475#issuecomment-1274087351
Hi @strongduanmu thanks for your reply.
this is main.groovy
```groovy
println((278935034474528769 & (1 << 4) - 1 << 18) >> 18);
println((278935034474528769 & 3932160) >> 18);
println((278935034474528769 & (1 << 8) - 1 << 10) >> 10);
println((278935034474528769 & 261120) >> 10);
```
output:
```
[Running] apache-groovy-binary-4.0.5/groovy-4.0.5/bin/groovy "/workspace/tempCodeRunnerFile.groovy"
1
1
0
0
```
this is expected result.
(Im not familiar with groovy, is this 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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] ningzio commented on issue #21475: INLINE script cannot handle int64 id as expected
Posted by GitBox <gi...@apache.org>.
ningzio commented on issue #21475:
URL: https://github.com/apache/shardingsphere/issues/21475#issuecomment-1313173899
>
5.2.1 works! thank you
--
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] FlyingZC commented on issue #21475: INLINE script cannot handle int64 id as expected
Posted by GitBox <gi...@apache.org>.
FlyingZC commented on issue #21475:
URL: https://github.com/apache/shardingsphere/issues/21475#issuecomment-1285241578
Maybe it has been fixed by #21564, You can try it with version 5.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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #21475: INLINE script cannot handle int64 id as expected
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21475:
URL: https://github.com/apache/shardingsphere/issues/21475#issuecomment-1274082517
Hi @ningzio, thank you for your feedback. Can you try test this expression with groovy shell? The INLINE sharding algrithom is based on groovy shell.
--
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