You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "sandynz (via GitHub)" <gi...@apache.org> on 2023/02/13 06:01:23 UTC
[GitHub] [shardingsphere] sandynz opened a new issue, #24135: Insert failed on MySQL VARBINARY column when keyGenerateStrategy is configured
sandynz opened a new issue, #24135:
URL: https://github.com/apache/shardingsphere/issues/24135
## Feature Request
### Is your feature request related to a problem?
Yes
### Describe the feature you would like.
MySQL is used, table primary key is VARBINARY type. Primary key column is sharding column, and keyGenerateStrategy is configured.
When insert into table, primary key value is set in SQL, not generated by ShardingSphere. There's exception thrown.
```
Caused by: java.lang.ClassCastException: class [B cannot be cast to class java.lang.Comparable ([B and java.lang.Comparable are in module java.base of loader 'bootstrap')
at org.apache.shardingsphere.infra.binder.segment.insert.keygen.engine.GeneratedKeyContextEngine.findGeneratedKey(GeneratedKeyContextEngine.java:102)
at org.apache.shardingsphere.infra.binder.segment.insert.keygen.engine.GeneratedKeyContextEngine.lambda$createGenerateKeyContext$0(GeneratedKeyContextEngine.java:58)
at java.base/java.util.Optional.map(Optional.java:265)
at org.apache.shardingsphere.infra.binder.segment.insert.keygen.engine.GeneratedKeyContextEngine.createGenerateKeyContext(GeneratedKeyContextEngine.java:57)
at org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementContext.setUpParameters(InsertStatementContext.java:271)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createQueryContext(ShardingSpherePreparedStatement.java:562)
```
table structure example:
```
CREATE TABLE `t_order` (
`order_id` varbinary(64) NOT NULL,
`user_id` int(11) NOT NULL,
`status` varchar(255) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```
sharding rule `driver/test_varbinary_pk_batch_insert.yaml`:
```
databaseName: logic_db
dataSources:
ds_2:
password: root
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://127.0.0.1:3306/pipeline_it_2?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&useLocalSessionState=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
username: root
ds_3:
password: root
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://127.0.0.1:3306/pipeline_it_3?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&useLocalSessionState=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true
username: root
ds_4:
password: root
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://127.0.0.1:3306/pipeline_it_4?useServerPrepStmts=true&serverTimezone=UTC&useSSL=false&useLocalSessionState=true&characterEncoding=utf-8&allowPublicKeyRetr
ieval=true&rewriteBatchedStatements=true
username: root
rules:
- !SHARDING
autoTables:
t_order:
actualDataSources: ds_2,ds_3,ds_4
keyGenerateStrategy:
column: order_id
keyGeneratorName: t_order_snowflake
logicTable: t_order
shardingStrategy:
standard:
shardingAlgorithmName: t_order_hash_mod
shardingColumn: user_id
keyGenerators:
t_order_snowflake:
type: snowflake
shardingAlgorithms:
t_order_hash_mod:
props:
sharding-count: '6'
type: hash_mod
```
Test java code:
```
public class TestVarbinaryPkBatchInsert {
public static void main(String[] args) throws Exception {
new ShardingSphereDriver();
try (Connection connection = DriverManager.getConnection("jdbc:shardingsphere:classpath:driver/test_varbinary_pk_batch_insert.yaml");
PreparedStatement statement = connection.prepareStatement("INSERT INTO t_order (order_id,user_id,status) VALUES (?,?,?)")) {
connection.setAutoCommit(false);
statement.setObject(1, "a1".getBytes());
statement.setObject(2, 101);
statement.setObject(3, "ok");
// statement.addBatch();
// statement.executeBatch();
statement.executeUpdate();
connection.commit();
}
}
}
```
#### Possible solution
Current work around:
Remove `keyGenerateStrategy` configuration, then it works.
If `keyGenerateStrategy` is set, could we ignore `createGenerateKeyContext` when related column's value is set in SQL?
--
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] harvies commented on issue #24135: Insert failed on MySQL VARBINARY column when keyGenerateStrategy is configured
Posted by "harvies (via GitHub)" <gi...@apache.org>.
harvies commented on issue #24135:
URL: https://github.com/apache/shardingsphere/issues/24135#issuecomment-1428011094
The reason is that byte[] does not implement the Comparable interface, resulting in an exception. After changing byte[] to String, the execution is normal.
--
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 #24135: Insert failed on MySQL VARBINARY column when keyGenerateStrategy is configured
Posted by "sandynz (via GitHub)" <gi...@apache.org>.
sandynz commented on issue #24135:
URL: https://github.com/apache/shardingsphere/issues/24135#issuecomment-1428062094
Hi @harvies , thanks for your investigation.
In real world, `order_id` might be byte array with special characters (e.g. 0x05), then we could not just call `setObject(int,String)`.
--
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 #24135: Insert failed on MySQL VARBINARY column when keyGenerateStrategy is configured
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24135:
URL: https://github.com/apache/shardingsphere/issues/24135#issuecomment-1427429921
Thank you for your feedback, I will investigate this issue.
--
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] GuoHaoZai commented on issue #24135: Insert failed on MySQL VARBINARY column when keyGenerateStrategy is configured
Posted by "GuoHaoZai (via GitHub)" <gi...@apache.org>.
GuoHaoZai commented on issue #24135:
URL: https://github.com/apache/shardingsphere/issues/24135#issuecomment-1428258003
> Hi @harvies , thanks for your investigation. In real world, `order_id` might be byte array with special characters (e.g. 0x05), then we could not just call `setObject(int,String)`.
I also don't think this is a bug, but it should be more user-friendly
--
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