We have a simple audit system on several tables. All this works fine, but from time to time we get an error similar to this operation that causes the trigger to start:
java.sql.SQLException: Explicit or implicit commit is not allowed in stored function or trigger.
Unfortunately, we cannot understand how our triggers can cause commit.
These are triggers that (sometimes) cause an error:
CREATE
TRIGGER `my_schema`.`fileDescriptorInsertTrigger`
AFTER INSERT ON `my_schema`.`FILE_DESCRIPTOR`
FOR EACH ROW
insert into `AUDIT_EVENT`
(`applicationId`,`classifier`,`lastModified`)
values
(NEW.application,'FILE_AND_DIR',NOW())
on duplicate key
update lastModified=NOW();
CREATE
TRIGGER `my_schema`.`fileDescriptorUpdateTrigger`
AFTER UPDATE ON `my_schema`.`FILE_DESCRIPTOR`
FOR EACH ROW
update `AUDIT_EVENT`
set lastModified=NOW()
where classifier='FILE_AND_DIR'
and applicationId=NEW.application;
CREATE
TRIGGER `my_schema`.`fileDescriptorDeleteTrigger`
AFTER DELETE ON `my_schema`.`FILE_DESCRIPTOR`
FOR EACH ROW
update `AUDIT_EVENT`
set lastModified=NOW()
where classifier='FILE_AND_DIR'
and applicationId=OLD.application;
Edit: on request withdrawal show variables like '%commit%'
Variable_name Value
innodb_commit_concurrency 0
innodb_flush_log_at_trx_commit 1
Edit 2
The error only matters after INSERTon my_schema.FILE_DESCRIPTOR, so it will narrow down to INSERT TRIGGER, but even then I don’t know how this might cause a commit.
We have Hibernate on top of this, so Hibernate actually does the inserts, and we also have the object displayed in the table AUDIT_EVENT, but Hibernate (should) never write to the table AUDIT_EVENT.
stacktrace, .