Insert failed on MySQL VARBINARY column when keyGenerateStrategy is configured

[sandynz]

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?

[strongduanmu]

Thank you for your feedback, I will investigate this issue.

[harvies]

The reason is that byte[] does not implement the Comparable interface, resulting in an exception. After changing byte[] to String, the execution is normal.

[sandynz]

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).