MySQL Bugs: #12604: "after insert" function that insert into same table get table LOCK error & fail

Bug #12604	"after insert" function that insert into same table get table LOCK error & fail
Submitted:	16 Aug 2005 16:39	Modified:	24 Jul 2006 15:38
Reporter:	Jonathan Miller	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
/home/ndbdev/jmiller/mysql5.0/client/.libs/lt-mysqltest: At line 47: query 'INSERT INTO test.t2 VALUES(NULL,01,'Testing MySQL databases is a cool ', 'Must make it good for the customer',654321.4321,15.21,0,1965,NOW())' failed: 1100: Table 't2' was not locked with LOCK TABLES
(the last lines may be the most important ones)

CREATE TABLE test.t2 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id));
delimiter |;
CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW BEGIN
    INSERT INTO test.t2 VALUES(NULL,1,'Testing MySQL databases is a dream job come true', 'Theonlyjobbetteristestingdarkbeerprague',67891.1234,12.51,0,1965,NOW());
    UPDATE test.t2 SET f = ROUND(f);
END|
delimiter ;|

INSERT INTO test.t2 VALUES(NULL,01,'Testing MySQL databases is a cool ', 'Must make it good for the customer',654321.4321,15.21,0,1965,NOW());

How to repeat:
See above:

This bug is a duplicate of bug#11896.

It is a limitation of MySQL that you can't update a table from a trigger that is defined for that very table.  After the fix of bug#11896 the error message is more clear:

ERROR HY000: Can't update table 'test.t2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.