我发现了一个涉及 mariadb 的奇怪问题。从休眠状态批量更新。此处添加了原始问题: https://stackoverflow.com/questions/78204056/hibernate-sqlintegrityconstraintviolation-duplicate-data-entry-issue-after-upg ?noredirect=1#comment138316709_78204056
表 t_instancetest
CREATE TABLE `t_instancetest` (
`RATEID` BIGINT(10) NOT NULL,
`BID` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8_bin',
`ASK` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8_bin',
`MID` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8_bin',
`UPDATEDDATE` DATETIME(6) NULL DEFAULT NULL,
`INSTANCEUPDATEDDATE` DATETIME(6) NULL DEFAULT NULL,
`UPDATEDBY` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`CRUD` VARCHAR(1) NULL DEFAULT NULL COLLATE 'utf8_bin',
`CREATEDBY` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`INSTANCEUPDATEDBY` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_bin',
`LOCATIONID` INT(3) NOT NULL,
PRIMARY KEY (`RATEID`, `LOCATIONID`) USING BTREE
)COLLATE='utf8_bin'
ENGINE=InnoDB
;
审计日志
CREATE TABLE `t_datalog` (
`Id` BIGINT(10) NOT NULL AUTO_INCREMENT,
`RateId` BIGINT(10) NOT NULL,
`CreatedDate` DATETIME(6) NOT NULL,
PRIMARY KEY (`Id`) USING BTREE
))
COLLATE='utf8_bin'
ENGINE=InnoDB
AUTO_INCREMENT=232
;
TRIGGER (BEFORE UPDATE ) - 但此字段后来被删除以使其更容易测试 - 并且此触发器被删除。
CREATE DEFINER=`USERADMIN`@`%` TRIGGER `TRG_BU_T_INSTANCE`.
BEFORE UPDATE ON `t_instance` FOR EACH ROW BEGIN
SET NEW.ATTRCHANGEID=UUID();
END
触发(更新后)
CREATE DEFINER=`USERADMIN`@`%` TRIGGER `TRG_AU_T_INSTANCETEST` AFTER UPDATE ON `t_instancetest` FOR EACH ROW BEGIN
INSERT INTO `t_datalog` ( `RateId`, `CreatedDate`) VALUES (OLD.RATEID, sysdate());
END
从应用程序批量更新 (batchsize = 7) - 如此处所示,它更新 7 个不同的速率
2024-05-10 12:31:59.814 INFO 3472 --- [nio-9012-exec-2] n.t.d.l.l.SLF4JQueryLoggingListener : {"name":"reliableDataSource", "connection":3, "time":13, "success":true, "type":"Prepared", "batch":true, "querySize":1, "batchSize":7, "query":["update t_instancetest set ask=?, bid=?, createdby=?, crud=?, instanceupdateddate=?, locationId=?, mid=?, updatedby=?, updateddate=? where locationid=? and rateId=?"], "params":[["116.13","12.23","SYSTEM","U","2024-05-10 12:31:59.665","101","7181","naga","2024-05-10 12:03:55.0","101","7181"],
["116.13","12.23","SYSTEM","U","2024-05-10 12:31:59.665","101","7239","naga","2024-05-10 12:03:55.0","101","7239"],
["116.13","12.23","SYSTEM","U","2024-05-10 12:31:59.665","101","7254","naga","2024-05-10 12:03:55.0","101","7254"],
["116.13","12.23","SYSTEM","U","2024-05-10 12:31:59.665","101","7265","naga","2024-05-10 12:03:55.0","101","7265"],
["116.13","12.23","SYSTEM","U","2024-05-10 12:31:59.665","101","7370","naga","2024-05-10 12:03:55.0","101","7370"],
["116.13","12.23","SYSTEM","U","2024-05-10 12:31:59.665","101","7453","naga","2024-05-10 12:03:55.0","101","7453"],
["116.13","12.23","SYSTEM","U","2024-05-10 12:31:59.665","101","7643","naga","2024-05-10 12:03:55.0","101","7643"]]}
Mariadb 日志,但是显示我们只更新 t_instancetest 中的一条记录,并在同一速率 ID 上执行触发器 7 次。其他rateid没有被执行。
#240509 17:27:01 server id 2 end_log_pos 127297238 CRC32 0x5e6a5fa7 Write_rows: table id 3067 flags: STMT_END_F
### UPDATE `reliabledb`.`t_instance`
### WHERE
### @1=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='12.23' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
### @3='116.13' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
### @4='' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
### @5='2024-05-09 16:49:54.962888' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @6='2024-05-09 17:22:52.812000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @7='naga' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### @8='U' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
### @9='SYSTEM' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### @10='naga' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### @11=101 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2='12.23' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
### @3='116.13' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
### @4='7181' /* VARSTRING(75) meta=75 nullable=1 is_null=0 */
### @5='2024-05-09 16:49:54.962888' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @6='2024-05-09 17:27:01.116000' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
### @7='naga' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### @8='U' /* VARSTRING(3) meta=3 nullable=1 is_null=0 */
### @9='SYSTEM' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### @10='naga' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */
### @11=101 /* INT meta=0 nullable=0 is_null=0 */
### INSERT INTO `reliabledb`.`t_datalog`
### SET
### @1=98 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='2024-05-09 17:27:01.000000' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
### INSERT INTO `reliabledb`.`t_datalog`
### SET
### @1=99 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='2024-05-09 17:27:01.000000' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
### INSERT INTO `reliabledb`.`t_datalog`
### SET
### @1=100 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='2024-05-09 17:27:01.000000' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
### INSERT INTO `reliabledb`.`t_datalog`
### SET
### @1=101 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='2024-05-09 17:27:01.000000' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
### INSERT INTO `reliabledb`.`t_datalog`
### SET
### @1=102 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='2024-05-09 17:27:01.000000' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
### INSERT INTO `reliabledb`.`t_datalog`
### SET
### @1=103 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=7181 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='2024-05-09 17:27:01.000000' /* DATETIME(6) meta=6 nullable=0 is_null=0 */
# Number of rows: 7
# at 127297238
#240509 17:27:01 server id 2 end_log_pos 127297269 CRC32 0xd7928418 Xid = 205571784
COMMIT/*!*/;
如果我们删除触发器,插入将正常完成(所有记录均已更新)。此外,如果我们删除批次,插入的行为也将完成。仅当批量大小 > 3 且触发器有效时才会出现此问题。
任何人都可以分享关于为什么会发生这种情况的任何想法?或者可能指出可能会干扰此操作的 mariadb 错误?它仅发生在架构上的此表上,其中其他表不受影响。
版本详情:
- 玛丽亚数据库 10.5
- mariadb jdbc 客户端 3.3.3 (也尝试过 2.7.4 )
- 春季启动(java)2.7.18
- 休眠5.6.15
似乎问题与 mariadb jdbc 驱动程序 + maria db 服务器本身有关。
这也会影响批量更新。其他人也发现了这个问题,例如: https://stackoverflow.com/questions/76430213/jooqs-batchinsert-doesnt-behave-as-expected-with-after-insert-trigger
几点: