Bug #17549 can't manipulate table from within it's own trigger

• Submitted: 19 Feb 2006 0:57
• Modified: 19 Feb 2006 10:47
• Reporter: Roland Volkmann
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.18-nt
• OS: Windows (WinXP Prof.)
• CPU Architecture: Any

[19 Feb 2006 0:57] Roland Volkmann

Description:
My table should contain only a maximum number of rows. Therefore an after insert trigger should delete the wasted old rows. But this results in error "ERROR 1442 (HY000): Can't update table 'ttt' in stored function/trigger because
it is already used by statement which invoked this stored function/trigger."

Because such limitation isn't described in the manual, I guess it's a bug. I would understand the behaviour, if the current new row would be affected, but it isn't (see example below).

Roland Volkmann
Stuttgart (Germany)

How to repeat:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table ttt (id integer, primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter //
mysql> create trigger tr_ttt_ai after insert on ttt
    -> for each row
    -> begin
    -> declare $rows integer;
    -> select count(*) into $rows from ttt;
    -> if $rows > 2 then
    ->   delete from ttt order by id limit 1;
    -> end if;
    -> end //
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;
mysql> insert into ttt (id) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt (id) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ttt (id) values (3);
ERROR 1442 (HY000): Can't update table 'ttt' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql>

[19 Feb 2006 10:47] Valeriy Kravchuk

Thank you for a problem report. Sorry, but changes to the table that FOR EACH ROW trigger is created on are not allowed (and, I hope, will never be supported). Period. Partially, the reason can be found in the manual:

http://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html:

"In general, you cannot modify a table and select from the same table in a subquery."

But it is obviously should not be allowed.