Bug #8406 Triggers crash if referencing a table
Submitted: 9 Feb 2005 22:07 Modified: 10 Jul 2005 13:48
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SUSE 9.)
Assigned to: Dmitry Lenev CPU Architecture:Any

[9 Feb 2005 22:07] Peter Gulutzan
Description:
If a trigger references a table, then a crash occurs
when the update/insert/delete occurs. This is demonstrable
when the references is indirect i.e. in a function, or
direct i.e. in the trigger body. It also is demonstrable
if there is a reference to a nonexistent table. The
crash occurs whether or not the table in the trigger
body is the same as the table being updated/deleted/inserted.
(This depends on a new 'feature' to allow table references in functions.)

How to repeat:
mysql> create table t18 (s1 int)//
Query OK, 0 rows affected (0.00 sec)

mysql> create table t18a (s1 int)//
Query OK, 0 rows affected (0.00 sec)

mysql> create function f18 () returns int begin declare v int; declare c cursor for select s1 from t18a; open c; fetch c into v; return v; end;//
Query OK, 0 rows affected (0.00 sec)

mysql> create trigger t18_bi before insert on t18 for each row set new.s1 = f18()//
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t18 values (0)//
ERROR 2013 (HY000): Lost connection to MySQL server during query

...

mysql> create table t20 (s1 tinyint)//
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger t20_bu before update on t20 for each row insert into t20a values (new.s1)//
Query OK, 0 rows affected (0.00 sec)

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

mysql> update t20 set s1 = 0//
ERROR 2013 (HY000): Lost connection to MySQL server during query

...

mysql> create table t20 (s1 tinyint)//
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger t20_bu before update on t20 for each row insert into t20a values (new.s1)//
Query OK, 0 rows affected (0.00 sec)

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

mysql> update t20 set s1 = 0//
ERROR 2013 (HY000): Lost connection to MySQL server during query

...

mysql> create table t42 (s1 int, s2 int)//
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t42 values (1,1),(2,2)//
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create trigger t42_bu before update on t42 for each row set new.s1 = f42()//
Query OK, 0 rows affected (0.00 sec)

mysql> create function f42 () returns int return (select sum(s1) from t42)//
Query OK, 0 rows affected (0.00 sec)

mysql> update t42 set s1 = 7//
ERROR 1305 (42000): FUNCTION db18.f42 does not exist
mysql> select f42()//
ERROR 2013 (HY000): Lost connection to MySQL server during query
[9 Feb 2005 22:15] Miguel Solorzano
Thank you for the bug report. I was able to repeat.
[29 Jun 2005 7:17] Dmitry Lenev
Please note that:
- Starting from 5.0.3 server does not crash in this situation.
  Instead trigger will emit following  warning:
  "Error 1100: Table 'some_table' was not locked with LOCK TABLES"
- Starting from 5.0.7 this problem is reported as error and trigger
  failure causes failure of statement which invokes this trigger.
[4 Jul 2005 20:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26654
[9 Jul 2005 20:09] Dmitry Lenev
Fixed in 5.0.10.

Please also mention in ChangeLog that now we support access to tables from triggers without any additional tricks (i.e. without doing explicit LOCK TABLES for all tables used in  trigger before execution of statement which should invoke this trigger).
[10 Jul 2005 13:48] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Bugfix and functionality changes documented in 5.0.10 change history; closed.
[11 Jul 2005 5:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26825