MySQL Bugs: #8406: Triggers crash if referencing a table

Bug #8406	Triggers crash if referencing a table
Submitted:	9 Feb 2005 23:07	Modified:	10 Jul 2005 15:48
Reporter:	Peter Gulutzan
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SUSE 9.)
Assigned to:	Dmitri Lenev	Target Version:

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

Thank you for the bug report. I was able to repeat.

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.

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

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).

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.

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