use config SNOWFLAKE Sharding value must implements Comparable #5876

ljz0721cx · 2020-06-01T12:04:30Z

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);
        }
    }

The text was updated successfully, but these errors were encountered:

kimmking · 2020-06-02T07:35:41Z

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 · 2020-06-04T03:55:41Z

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 · 2020-06-10T04:42:20Z

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

kimmking · 2020-06-11T10:16:42Z

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

kimmking · 2020-06-12T10:09:00Z

#6016 (comment)

kimmking · 2020-06-12T10:15:12Z

@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 · 2020-06-13T01:00:53Z

@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 · 2020-06-15T14:41:26Z

@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 · 2020-06-17T06:21:16Z

your project has no sharding-sphere config or dependency.

huangjingcai · 2020-09-17T14:46:44Z

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.

kimmking added the status: need more info label Jun 2, 2020

ljz0721cx mentioned this issue Jun 12, 2020

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

Closed

ljz0721cx mentioned this issue Jun 13, 2020

@ljz0721cx ok，i know your data class now. #6022

Closed

kimmking closed this as completed Jun 17, 2020

terrymanu added status: invalid and removed status: need more info labels Jun 17, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

use config SNOWFLAKE Sharding value must implements Comparable #5876

use config SNOWFLAKE Sharding value must implements Comparable #5876

ljz0721cx commented Jun 1, 2020 •

edited

Loading

kimmking commented Jun 2, 2020

ljz0721cx commented Jun 4, 2020

terrymanu commented Jun 10, 2020 •

edited

Loading

kimmking commented Jun 11, 2020 •

edited

Loading

kimmking commented Jun 12, 2020

kimmking commented Jun 12, 2020

ljz0721cx commented Jun 13, 2020 •

edited

Loading

kimmking commented Jun 15, 2020

kimmking commented Jun 17, 2020

huangjingcai commented Sep 17, 2020

use config SNOWFLAKE Sharding value must implements Comparable #5876

use config SNOWFLAKE Sharding value must implements Comparable #5876

Comments

ljz0721cx commented Jun 1, 2020 • edited Loading

Bug Report

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.

Which version of ShardingSphere did you use?

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

Expected behavior

Actual behavior

Reason analyze (If you can)

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

kimmking commented Jun 2, 2020

ljz0721cx commented Jun 4, 2020

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.

terrymanu commented Jun 10, 2020 • edited Loading

kimmking commented Jun 11, 2020 • edited Loading

kimmking commented Jun 12, 2020

kimmking commented Jun 12, 2020

ljz0721cx commented Jun 13, 2020 • edited Loading

kimmking commented Jun 15, 2020

kimmking commented Jun 17, 2020

huangjingcai commented Sep 17, 2020

ljz0721cx commented Jun 1, 2020 •

edited

Loading

terrymanu commented Jun 10, 2020 •

edited

Loading

kimmking commented Jun 11, 2020 •

edited

Loading

ljz0721cx commented Jun 13, 2020 •

edited

Loading