批量插入数据,插入成功但却查不到数据

方便更快捷的说明问题,可以按需填写(可删除)

使用环境:

ShardingSphere-Proxy-5.1.0 + MyBatis-Plus-3.4.1+Driud + Mysql8

场景、问题:

1、在代码中执行批量插入数据的操作,sql 如下:

insert into aiot_t_set_group(tenantId, setId, groupId,createTime,updateTime)
values
('1003400', 134, 37, now(), now()), ('1003400', 136, 37, now(), now()), ('1003400', 137, 37, now(), now()), ('1003400', 138, 37, now(), now());

2、代码中返回了影响的行数,打开mysql的general log ,发现mysql已经接收到了 sql ,但是 sql
并未提交,general log 如下:

2022-02-21T17:31:53.211744Z     489231 Query    SELECT @@session.transaction_read_only
2022-02-21T17:31:53.257390Z     489231 Query    insert into aiot_t_set_group(tenantId, setId, groupId,createTime,updateTime)
     values
('1003400', 134, 37, now(), now()), ('1003400', 136, 37, now(), now()), ('1003400', 137, 37, now(), now()), ('1003400', 138, 37, now(), now());

3、停止代码运行,断开对sharding的连接后,general log 如下:

2022-02-21T17:32:28.648088Z     489231 Query    rollback
2022-02-21T17:32:28.653755Z     489231 Query    SET autocommit=1

4、这里不明白为什么没有提交事务,按道理这条 sql 就是一条事务,并且代码中并未配置事务

已进行操作:

1、将批量操作在代码中改成循环插入则成功

2、general log 如下:

 2022-02-22T03:50:05.291206Z     491198 Query    SET autocommit=0
2022-02-22T03:50:05.293255Z     491198 Query    select *
2022-02-22T03:50:05.340099Z     491198 Query    SELECT *
2022-02-22T03:50:35.478205Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.513322Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.548700Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.583996Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.618729Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.653627Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.688784Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.723339Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.758179Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.793192Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.828241Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.863524Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.898350Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.933470Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:35.968479Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.003318Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.038421Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.073324Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.108160Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.142737Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.177484Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.212461Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.251486Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.286732Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.321686Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.356481Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.390895Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.425661Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.460917Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.495828Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.530360Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.565289Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.600212Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.635157Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.670115Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.704971Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.739427Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.774439Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.808705Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.843210Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.878283Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.913145Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.948367Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:36.984233Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.019275Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.054235Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.089259Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.123983Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.158538Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.193111Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.227367Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.263336Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.298094Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.333384Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.368615Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.403723Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.438483Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.474080Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.509010Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.543725Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.579794Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.614594Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.649117Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.684388Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.719667Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.755239Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.790662Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.826071Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.861381Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.896493Z     491198 Query    INSERT INTO aiot_t_set_group  ( tenantId,
2022-02-22T03:50:37.934057Z     491198 Query    commit
2022-02-22T03:50:37.939432Z     491198 Query    SET autocommit=1

现状:

暂未解决

proxy 的 server.yaml 是怎么配置的?

打开配置 sql-show=true, proxy的日志是什么样的?

京ICP备2021015875号