Description:
Table locking in not handled correctly and a wrong error messageis returned when triggers are defined in a recursive manner, ie
- a before insert trigger is set on table 1 and inserts on table 2
- a before insert trigger is set on table 2 and inserts on table 3
- a before insert trigger is set on table 3 and inserts on table 4
- a before insert trigger is set on table 4 and inserts on table 1
What is observed is that on insert to table 1, the triggers are fired and rows are inserted to tables 2,3,4 and back at one, but at that point a locking error is returned on table 2.
- It seems that 'recursive locking is only partly implemented (one level for
table '1' -and not more)
- If recursive locking is not supported it should have failed on
the second insert to table '1' (not table 2)
- A proper error message needs to be returned
As described below:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.10-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t3;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t4;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (f1 integer) engine = myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2 (f2 integer) engine = myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t3 (f3 integer) engine = myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t4 (f4 integer) engine = myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger tr1 after insert on t1
-> for each row insert into t2 (f2) values (new.f1+1);
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger tr2 after insert on t2
-> for each row insert into t3 (f3) values (new.f2+1);
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger tr3 after insert on t3
-> for each row insert into t4 (f4) values (new.f3+1);
Query OK, 0 rows affected (0.00 sec)
mysql> create trigger tr4 after insert on t4
-> for each row insert into t1 (f1) values (new.f4+1);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (1);
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
| 5 |
+------+
2 rows in set (0.00 sec)
OBN> The second insert to t1 happend (value '5').
mysql> select * from t2;
+------+
| f2 |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> select * from t3;
+------+
| f3 |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> select * from t4;
+------+
| f4 |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
mysql>
Note: That in the case of innodb, the same error is observed but the selects
return nothing since the whole transaction is rolled back
How to repeat:
use test;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 (f1 integer) engine = myisam;
create table t2 (f2 integer) engine = myisam;
create table t3 (f3 integer) engine = myisam;
create table t4 (f4 integer) engine = myisam;
create trigger tr1 after insert on t1
for each row insert into t2 (f2) values (new.f1+1);
create trigger tr2 after insert on t2
for each row insert into t3 (f3) values (new.f2+1);
create trigger tr3 after insert on t3
for each row insert into t4 (f4) values (new.f3+1);
create trigger tr4 after insert on t4
for each row insert into t1 (f1) values (new.f4+1);
insert into t1 values (1);
select * from t1;
select * from t2;
select * from t3;
select * from t4;
Description: Table locking in not handled correctly and a wrong error messageis returned when triggers are defined in a recursive manner, ie - a before insert trigger is set on table 1 and inserts on table 2 - a before insert trigger is set on table 2 and inserts on table 3 - a before insert trigger is set on table 3 and inserts on table 4 - a before insert trigger is set on table 4 and inserts on table 1 What is observed is that on insert to table 1, the triggers are fired and rows are inserted to tables 2,3,4 and back at one, but at that point a locking error is returned on table 2. - It seems that 'recursive locking is only partly implemented (one level for table '1' -and not more) - If recursive locking is not supported it should have failed on the second insert to table '1' (not table 2) - A proper error message needs to be returned As described below: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.10-beta-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql> drop table if exists t2; Query OK, 0 rows affected (0.00 sec) mysql> drop table if exists t3; Query OK, 0 rows affected (0.00 sec) mysql> drop table if exists t4; Query OK, 0 rows affected (0.00 sec) mysql> create table t1 (f1 integer) engine = myisam; Query OK, 0 rows affected (0.01 sec) mysql> create table t2 (f2 integer) engine = myisam; Query OK, 0 rows affected (0.00 sec) mysql> create table t3 (f3 integer) engine = myisam; Query OK, 0 rows affected (0.01 sec) mysql> create table t4 (f4 integer) engine = myisam; Query OK, 0 rows affected (0.00 sec) mysql> create trigger tr1 after insert on t1 -> for each row insert into t2 (f2) values (new.f1+1); Query OK, 0 rows affected (0.00 sec) mysql> create trigger tr2 after insert on t2 -> for each row insert into t3 (f3) values (new.f2+1); Query OK, 0 rows affected (0.00 sec) mysql> create trigger tr3 after insert on t3 -> for each row insert into t4 (f4) values (new.f3+1); Query OK, 0 rows affected (0.00 sec) mysql> create trigger tr4 after insert on t4 -> for each row insert into t1 (f1) values (new.f4+1); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (1); ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES mysql> select * from t1; +------+ | f1 | +------+ | 1 | | 5 | +------+ 2 rows in set (0.00 sec) OBN> The second insert to t1 happend (value '5'). mysql> select * from t2; +------+ | f2 | +------+ | 2 | +------+ 1 row in set (0.00 sec) mysql> select * from t3; +------+ | f3 | +------+ | 3 | +------+ 1 row in set (0.00 sec) mysql> select * from t4; +------+ | f4 | +------+ | 4 | +------+ 1 row in set (0.00 sec) mysql> Note: That in the case of innodb, the same error is observed but the selects return nothing since the whole transaction is rolled back How to repeat: use test; drop table if exists t1; drop table if exists t2; drop table if exists t3; drop table if exists t4; create table t1 (f1 integer) engine = myisam; create table t2 (f2 integer) engine = myisam; create table t3 (f3 integer) engine = myisam; create table t4 (f4 integer) engine = myisam; create trigger tr1 after insert on t1 for each row insert into t2 (f2) values (new.f1+1); create trigger tr2 after insert on t2 for each row insert into t3 (f3) values (new.f2+1); create trigger tr3 after insert on t3 for each row insert into t4 (f4) values (new.f3+1); create trigger tr4 after insert on t4 for each row insert into t1 (f1) values (new.f4+1); insert into t1 values (1); select * from t1; select * from t2; select * from t3; select * from t4;