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: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.6bk | OS: | Linux (Linux) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[11 May 2005 10:52]
Jan Lindström
[11 May 2005 11:54]
MySQL Verification Team
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
[11 May 2005 11:59]
Jan Lindström
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
[11 May 2005 20:06]
MySQL Verification Team
Notice that the server was started with --default-storage-engine=innodb.
[16 Jun 2005 16:45]
[ name withheld ]
I'm having the same issue. "create trigger trigger_name after update..." always complains that tables aren't locked when an update is performed.
[21 Jun 2005 13:31]
Alexander Prohorenko
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
[28 Jun 2005 18:37]
[ name withheld ]
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 ...
[29 Jun 2005 7:45]
Dmitry Lenev
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.