I stumbled on to this when testing things out prior to upgrading our server. Starting at server version 10.5.2, and seemingly going until version 10.6.11 at least (haven't tested newer versions), this minimal script seems to trigger this error. 10.5.1 and earlier seems not to show it. (We've been using the official docker image to test different versions)

Please note the inline comments about how modifying the script can make the issue disappear – it seems to only happen in very particular circumstances, with both insert and update triggers present that insert into a table, and a view that references that same table.

Code that shows the issue (also attached to this ticket)

CREATE DATABASE IF NOT EXISTS test1;

USE test1;

CREATE TABLE `a` (

  `a_id` int(10) unsigned

) ENGINE=InnoDB;

CREATE TRIGGER a_audit_after_insert AFTER INSERT ON a FOR EACH ROW

INSERT INTO a_log (a_id) VALUES (NEW.a_id);

# !!! removing this AFTER UPDATE trigger makes the error go away -- even though we're not updating the a table

CREATE TRIGGER a_audit_after_update AFTER UPDATE ON a FOR EACH ROW

INSERT INTO a_log (a_id) VALUES (NEW.a_id);

DROP TABLE IF EXISTS `a_log`;

CREATE TABLE `a_log` (

  `a_id` int(10) unsigned

) ENGINE=InnoDB;

create view view_referencing_a_log as select 1 from a_log; # !!! removing the view makes the error go away

LOCK TABLES a WRITE; # !!! write-locking `a_log` as well makes the error go away (as shown below), as does removing the lock entirely

#LOCK TABLES a WRITE, a_log WRITE;

INSERT INTO a (a_id) VALUES (1);

UNLOCK TABLES;

The error is:
ERROR 1442 (HY000) at line 25: Can't update table 'a_log' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

From my point of view, this seems like a bug in newer versions. That is, unless I'm misunderstanding things and an error should always have occurred in this situation, though the message doesn't really make much sense to me or seem relevant to what the script is doing. And it's very strange to me that the presence or absence of a view referencing the table that the trigger inserts into makes a difference, as well as the presence or absence of a trigger that isn't even being executed.

This is something I can work around by fiddling with the lock statement as described in the script comments, it was just rather confusing and I wanted to make sure that people were aware of it, and maybe help out some others who have run into the same issue.

Thanks to everyone who works on MariaDB!