use config SNOWFLAKE Sharding value must implements Comparable

[ljz0721cx]

Bug Report

insert data but not accept below

Sharding value must implements Comparable

logic DROP TABLE IF EXISTS t_order0; CREATE TABLE t_order0 ( order_id bigint(20) NULL DEFAULT NULL COMMENT '订单id', order_no bigint(20) NOT NULL COMMENT '订单号', user_id int(20) NULL DEFAULT NULL COMMENT '用户id', status varchar(10) CHARACTER SET big5 COLLATE big5_chinese_ci NULL DEFAULT NULL COMMENT '状态码', create_date datetime(0) NULL DEFAULT NULL, PRIMARY KEY (order_no) USING BTREE ) ENGINE = InnoDB CHARACTER SET = big5 COLLATE = big5_chinese_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS t_order1; CREATE TABLE t_order0 ( order_id bigint(20) NULL DEFAULT NULL COMMENT '订单id', order_no bigint(20) NOT NULL COMMENT '订单号', user_id int(20) NULL DEFAULT NULL COMMENT '用户id', status varchar(10) CHARACTER SET big5 COLLATE big5_chinese_ci NULL DEFAULT NULL COMMENT '状态码', create_date datetime(0) NULL DEFAULT NULL, PRIMARY KEY (order_no) USING BTREE ) ENGINE = InnoDB CHARACTER SET = big5 COLLATE = big5_chinese_ci ROW_FORMAT = Compact;

excute error message

Error updating database. Cause: java.lang.IllegalArgumentException: Sharding value must implements Comparable.

The error may exist in file [D:\myworkers\myworkspace\spring-nacos\spring-shanding-db\target\classes\mapper\TransBillExtendsMapper.xml]

The error may involve com.bule.spring.dao.OrderShardingMapper.insert-Inline

The error occurred while setting parameters

SQL: INSERT INTO t_order ( order_id,order_no, user_id, status,create_date ) VALUES ( ?, ?, ?, ?, ? )

Cause: java.lang.IllegalArgumentException: Sharding value must implements Comparable.

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
... 37 more

Caused by: java.lang.IllegalArgumentException: Sharding value must implements Comparable. at com.google.common.base.Preconditions.checkArgument(Preconditions.java:122) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.getRouteValue(InsertClauseShardingConditionEngine.java:112) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.createShardingCondition(InsertClauseShardingConditionEngine.java:94) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.createShardingConditions(InsertClauseShardingConditionEngine.java:65) at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.getShardingConditions(ShardingRouteDecorator.java:80) at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:62) at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53) at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91) at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76) at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54) at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96) at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:143) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 43 more

Which version of ShardingSphere did you use?

org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.1.0

    <!-- for spring namespace -->
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.1.0</version>
    </dependency>

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

Expected behavior

SNOWFLAKE config can effect

Actual behavior

Cause: java.lang.IllegalArgumentException: Sharding value must implements Comparable.

Reason analyze (If you can)

when insert can not bind genrate sharding value

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

sharding:
      tables:
        t_order:
          key-generator:
            column: order_no
            type: SNOWFLAKE
          actual-data-nodes: ds-$->{2019..2020}.t_order$->{0..1}
          database-strategy:
            standard:
              sharding-column: create_date
              precise-algorithm-class-name: com.bule.spring.dao.sharding.TimeDatabaseShardingAlgorithm
          table-strategy:
            inline:
              sharding-column: order_no
              algorithm-expression: t_order$->{order_no % 2}

    <resultMap id="BaseResultMap" type="com.bule.spring.domain.Order">
        <id column="order_no" property="orderNo"/>
        <result column="order_id"  property="orderId"/>
        <result column="user_id" property="userId"/>
        <result column="status" property="status"/>
        <result column="create_date" property="createDate"/>
    </resultMap>

    <insert id="insert" parameterType="com.bule.spring.domain.Order" >
        INSERT INTO t_order (
           order_id,order_no, user_id, status,create_date
        )
        VALUES (
            #{orderId,jdbcType=INTEGER},
            #{orderNo,jdbcType=BIGINT},
            #{userId,jdbcType=INTEGER},
            #{status,jdbcType=INTEGER},
            #{createDate,jdbcType=TIMESTAMP}
        )
    </insert>

 @Test
    public void testInsert() {
        for (int i = 0; i < 10; i++) {
            Order order = new Order();
            order.setOrderId(Long.valueOf(i));
            order.setUserId(20001);
            order.setStatus("insert");
            order.setCreateDate(DateUtils.addYears(new Date(),-1));
           // order.setOrderNo(IdWorker.getId());
            orderShardingMapper.insert(order);
        }
        for (int i = 0; i < 10; i++) {
            Order order = new Order();
            order.setOrderId(Long.valueOf(i));
            order.setUserId(20001);
            order.setStatus("insert");
            order.setCreateDate(new Date());
            //order.setOrderNo(IdWorker.getId());
            orderShardingMapper.insert(order);
        }
    }

[kimmking]

It's a strange case. Snowflake id generator return a long type value, it must be a comparable.

Could you post your logic sql and actual sql in you log(sql.show=true)?

[ljz0721cx]

logic DROP TABLE IF EXISTS t_order0; CREATE TABLE t_order0 ( order_id bigint(20) NULL DEFAULT NULL COMMENT '订单id', order_no bigint(20) NOT NULL COMMENT '订单号', user_id int(20) NULL DEFAULT NULL COMMENT '用户id', status varchar(10) CHARACTER SET big5 COLLATE big5_chinese_ci NULL DEFAULT NULL COMMENT '状态码', create_date datetime(0) NULL DEFAULT NULL, PRIMARY KEY (order_no) USING BTREE ) ENGINE = InnoDB CHARACTER SET = big5 COLLATE = big5_chinese_ci ROW_FORMAT = Compact;

DROP TABLE IF EXISTS t_order1; CREATE TABLE t_order0 ( order_id bigint(20) NULL DEFAULT NULL COMMENT '订单id', order_no bigint(20) NOT NULL COMMENT '订单号', user_id int(20) NULL DEFAULT NULL COMMENT '用户id', status varchar(10) CHARACTER SET big5 COLLATE big5_chinese_ci NULL DEFAULT NULL COMMENT '状态码', create_date datetime(0) NULL DEFAULT NULL, PRIMARY KEY (order_no) USING BTREE ) ENGINE = InnoDB CHARACTER SET = big5 COLLATE = big5_chinese_ci ROW_FORMAT = Compact;

excute error message

Error updating database. Cause: java.lang.IllegalArgumentException: Sharding value must implements Comparable.

The error may exist in file [D:\myworkers\myworkspace\spring-nacos\spring-shanding-db\target\classes\mapper\TransBillExtendsMapper.xml]

The error may involve com.bule.spring.dao.OrderShardingMapper.insert-Inline

The error occurred while setting parameters

SQL: INSERT INTO t_order ( order_id,order_no, user_id, status,create_date ) VALUES ( ?, ?, ?, ?, ? )

Cause: java.lang.IllegalArgumentException: Sharding value must implements Comparable.

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
... 37 more

Caused by: java.lang.IllegalArgumentException: Sharding value must implements Comparable. at com.google.common.base.Preconditions.checkArgument(Preconditions.java:122) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.getRouteValue(InsertClauseShardingConditionEngine.java:112) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.createShardingCondition(InsertClauseShardingConditionEngine.java:94) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.createShardingConditions(InsertClauseShardingConditionEngine.java:65) at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.getShardingConditions(ShardingRouteDecorator.java:80) at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:62) at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53) at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91) at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76) at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54) at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96) at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:143) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ... 43 more

[terrymanu]

The problem maybe cause by create_date, what is the type of it? Can this value be null?

[kimmking]

What is the type create_date in your java data class? @ljz0721cx

[kimmking]

https://github.com/apache/shardingsphere/issues/6016#issue-637638624

[kimmking]

@ljz0721cx ok，i know your data class now. but i can't reproduce it...ah... can you make a small demo for reproducing it for me?

[ljz0721cx]

@kimmking

Ok you can pull demo code with url https://github.com/ruiqikeji/spring-nacos.git , branch is sharding-db of this project this is my wechat Cx521314888，I can give you more info ;

run test class TestOrderShardingMapper

[kimmking]

@kimmking

Ok you can pull demo code with url https://github.com/ruiqikeji/spring-nacos.git , branch is sharding-db of this project this is my wechat Cx521314888，I can give you more info ;

run test class TestOrderShardingMapper

your repo is private.

[kimmking]

your project has no sharding-sphere config or dependency.

[huangjingcai]

I have the same Error。(The version of the shardingphere-jdbc is 4.1.1) And I debug the code,the code run here: InsertClauseShardingConditionEngine.java line:65

result.add(createShardingCondition(tableName, columnNames.iterator(), each, parameters));

The first of the parameters is id and the value is null then cause the error.