MySQL trigger for some reason causes a commit

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

+5

All Articles