MySQL Bugs: #10538: Triggers not locking tables

Bug #10538	Triggers not locking tables
Submitted:	11 May 2005 10:52	Modified:	29 Jun 2005 7:45
Reporter:	Jan Lindström	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.6bk	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When testing foreign keys with on update no action and triggers on update
server returns error 1100. 

jan@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.6-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table r0(a int not null, primary key(a));
Query OK, 0 rows affected (0.23 sec)

mysql> create table r1(b int not null, primary key(b), foreign key (b) references r0(a) on update no action);
Query OK, 0 rows affected (0.20 sec)

mysql> insert into r0 values(0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into r1 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> create trigger a before update on r1 FOR EACH ROW insert into r0 values(3);
Query OK, 0 rows affected (0.00 sec)

mysql> update r1 set b = 5;
ERROR 1100 (HY000): Table 'r0' was not locked with LOCK TABLES

mysql> create table v0(a int not null, primary key(a));
Query OK, 0 rows affected (0.19 sec)

mysql> create table v1(b int not null, primary key(b), foreign key (b) references v0(a) on update no action);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into v0 values(5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into v1 values(5);
Query OK, 1 row affected (0.01 sec)

mysql> create trigger ab before update on v1 FOR EACH ROW insert into v1 values(1);
Query OK, 0 rows affected (0.00 sec)

mysql> update v0 set a = 1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> create trigger ac before update on v0 for each row insert into v0 values(1);
Query OK, 0 rows affected (0.02 sec)

mysql> update v0 set a = 1;
ERROR 1100 (HY000): Table 'v0' was not locked with LOCK TABLES
mysql> update v1 set b = 1;
ERROR 1100 (HY000): Table 'v1' was not locked with LOCK TABLES

How to repeat:
create table r0(a int not null, primary key(a));
create table r1(b int not null, primary key(b), foreign key (b) references r0(a) on update no action);
insert into r0 values(0);
insert into r1 values(0);
create trigger a before update on r1 FOR EACH ROW insert into r0 values(3);
update r1 set b = 5; // This will print ERROR 1100

Note that following works partly:

create table v0(a int not null, primary key(a));
create table v1(b int not null, primary key(b), foreign key (b) references v0(a) on update no action);
insert into v0 values(5);
insert into v1 values(5);
create trigger ab before update on v1 FOR EACH ROW insert into v1 values(1);
update v0 set a = 1; // returns ERROR 1217 OK
create trigger ac before update on v0 for each row insert into v0 values(1);
update v0 set a = 1; // returns ERROR 1100
update v1 set b = 1; // returns ERROR 1100

Suggested fix:
Trigger should lock all required tables.

I was unable for to get the error mentioned with latest source (yesterday pull):

mysql> create table r0(a int not null, primary key(a));
Query OK, 0 rows affected (0.03 sec)

mysql> create table r1(b int not null, primary key(b), foreign key (b) references r0(a)
    -> on update no action);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into r0 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into r1 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> create trigger a before update on r1 FOR EACH ROW insert into r0 values(3);
Query OK, 0 rows affected (0.00 sec)

mysql> update r1 set b = 5;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I have done bk pull today, my latest changeset is 
ChangeSet@1.1934.1.1, 2005-05-10 18:35:11+00:00, gluh@mysql.com
  Merge sgluhov@bk-internal.mysql.com:/home/bk/mysql-5.0
  into mysql.com:/home/gluh/MySQL/Devel/mysql-5.0

And starting from resh database I still get the same error:

jan@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.6-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table r0(a int not null, primary key(a));
Query OK, 0 rows affected (0.19 sec)

mysql> create table r1(b int not null, primary key(b), foreign key (b)
    -> references r0(a) on update no action);
Query OK, 0 rows affected (0.20 sec)

mysql> insert into r0 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into r1 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> create trigger a before update on r1 FOR EACH ROW insert into r0
    -> values(3);
Query OK, 0 rows affected (0.00 sec)

mysql> update r1 set b = 5;
ERROR 1100 (HY000): Table 'r0' was not locked with LOCK TABLES

Notice that the server was started with --default-storage-engine=innodb.

I'm having the same issue.  "create trigger trigger_name after update..." always complains that tables aren't locked when an update is performed.

The same situation for tables without any keys.

mysql> delimiter //
mysql> select version()//
+---------------+
| version()     |
+---------------+
| 5.0.7-beta-nt |
+---------------+
1 row in set (0.00 sec)

mysql> create table t (v TEXT)//
Query OK, 0 rows affected (0.23 sec)

mysql> create table log (dt DATETIME, old TEXT, new TEXT)//
Query OK, 0 rows affected (0.19 sec)

mysql> create trigger t
    -> after insert on t
    -> for each row
    -> insert into log values(now(),old,new)//
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values('test')//
ERROR 1100 (HY000): Table 'log' was not locked with LOCK TABLES

The same for before/after delete triggers in 5.0.7
Issueing an explicit LOCK TABLES solves the problem, unfortunately this can't be done in the trigger ...

Hi, Jan!

This is duplicate of bug #8406 "Triggers crash if referencing a table"
(Initially this bug caused server crashes but starting from version 5.0.3,
it causes only warnings/errors similar to one that you observe).
See http://bugs.mysql.com/bug.php?id=8406 for more info.