集成shardingjdbc 批量插入数据,如果sql里面插入now()函数,sea解析异常

问题描述

集成shardingjdbc 批量插入数据,如果sql里面插入now()函数

运行的sql :

insert into t_member1 (merchantId,member_name,create_time, id) values (?, ?, now(), ?), (?, ?, now(), ?), (?, ?, now(), ?) ::: [214, 张三的份上, 470255886078574594, 214, 李四, 470255886078574593, 214, 王五, 470255886078574592]

数据库sql:

insert into t_member (merchantId,member_name,create_time) values ( #{member.merchantId}, #{member.memberName}, now() ) `

2020-05-21 14:55:38.020 default [http-nio-8082-exec-2] ERROR i.seata.rm.datasource.exec.AbstractDMLBaseExecutor - execute executeAutoCommitTrue error:9 java.lang.ArrayIndexOutOfBoundsException: 9 at io.seata.rm.datasource.exec.InsertExecutor.getPkValuesByColumn(InsertExecutor.java:151) at io.seata.rm.datasource.exec.InsertExecutor.afterImage(InsertExecutor.java:78) at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.executeAutoCommitFalse(AbstractDMLBaseExecutor.java:88) at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.lambda$executeAutoCommitTrue$0

期待希望正常运行,,目前已经将now()函数 替换成了占位符,不会出现这个情况了

复制回顾

1、使用spring boot 项目集成mybatis mysql jar包,使用mysql 5.6版本 集成openfeigh 注册中心随便eureka nacos 都行

2、myabtis 新增代码

insert into t_member (merchantId,member_name,create_time) values ( #{member.merchantId}, #{member.memberName}, #{member.createTime} )

</insert>

3、业务逻辑 order类

@GlobalTransactional public ReturnMessage addList(List memberInfoList) { logger.error("xid"+ RootContext.getXID()); TransactionTypeHolder.set(TransactionType.BASE); int result =memberInfoMapper.addList(memberInfoList); List ids=new ArrayList<>(); for (MemberInfo info : memberInfoList) { ids.add(info.getId()); } //远程调用 ReturnMessage pointResult= memberPointI.addMemberPoint(memberInfoList); logger.info(JSON.toJSONString(pointResult)); //模拟异常 测试回滚 int z=1/0; return result>0?new ReturnMessage(ReturnCode.SUCCESS,"添加成功port:"+port,ids):new ReturnMessage(ReturnCode.SUCCESS,"添加失败:port"+port,null); }

4、sql 以merchantId 作为分片建

CREATE TABLE t_member ( id bigint(20) NOT NULL AUTO_INCREMENT, merchantId bigint(20) DEFAULT '0' COMMENT '商家Id', member_name varchar(255) DEFAULT NULL COMMENT '会员名称', create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1086 DEFAULT CHARSET=utf8;

CREATE TABLE t_member0 ( id bigint(20) NOT NULL AUTO_INCREMENT, merchantId bigint(20) DEFAULT '0' COMMENT '商家Id', member_name varchar(255) DEFAULT NULL COMMENT '会员名称', create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1086 DEFAULT CHARSET=utf8;

CREATE TABLE t_member1 ( id bigint(20) NOT NULL AUTO_INCREMENT, merchantId bigint(20) DEFAULT '0' COMMENT '商家Id', member_name varchar(255) DEFAULT NULL COMMENT '会员名称', create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1086 DEFAULT CHARSET=utf8;

5、添加数据http包

POST 请求 数据包 [ { "merchantId":214, "memberName":"张三的份上", "createTime":"2020-05-18 15:02:01" }, { "merchantId":214, "memberName":"李四", "createTime":"2020-06-18 15:02:01" }, { "merchantId":214, "memberName":"王五", "createTime":"2020-05-18 15:02:01" }

]

6、yml 配置文件

spring: application: name: shardingjdbc-inline shardingsphere: datasource: names: ds0 ds0: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.jdbc.Driver jdbc-url: jdbc:mysql://192.168.1.188:3306/pdy_member username: root password: mysql

props:
  sql:
    show: true
sharding:


  tables:
    t_member:
      key-generator:
        column: id
        type: SNOWFLAKE
      actual-data-nodes:     ds0.t_member$->{0..2}
      table-strategy:  #分表策略
        inline: #行表达式
          sharding-column: merchantId
          algorithm-expression: t_member$->{merchantId % 3}

其他信息

如果把上面的sharding jdbc 分为多个库,多个表,无法进行分布式事务回滚 ####问题4 提示回滚成功 , 实际上并没有成功 客户端与服务端的版本都是seata1.2 ,分库分表的版本的是sharding-jdbc-spring-boot-starter 4.0,sharding-transaction-base-seata-at4.0,................这个图是调用方 【提问37-39】

图中ds0 的事务一直回滚不了

环境信息

JDK version :1.8 OS :win10 Others: eureka, sharding-transaction-base-seata-at 4.0.0 sharding-jdbc-spring-boot-starter 4.0.0 spring-cloud-starter-alibaba-seata 2.1.0.RELEASE io.seata 1.2.0 myql 5.6 mybatis spring boot,openfeigh

原提问者GitHub用户pdyg