apache / apache/shardingsphere

Insert failed on MySQL VARBINARY column when keyGenerateStrategy is configured

Open
#24,135 3 comments 0 reactions 1 assignee Claimed by @strongduanmu View on GitHub
feature: sharding type: bug
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 35m
Merged PRs (30d)
326

Description

## 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 NOT 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?

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.