Bug #11889 Server crash when dropping a trigger using non-cached stored routine
Submitted: 12 Jul 2005 16:28 Modified: 14 Jul 2005 15:41
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.10 OS:Linux (Linux / All)
Assigned to: Dmitry Lenev CPU Architecture:Any

[12 Jul 2005 16:28] Omer Barnir
Description:
A server crash is observed when dropping a table that that had a trigger set on it that was used in a recursive stored procedure definition (i.e. the stored procedure was inserting to the table, the table had a trigger defined that calls the stored procedure).

As the following shows:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10-beta-log

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

mysql> use test;
Database changed
mysql>        create table t1_sp (
    ->                 count integer,
    ->                 var136 tinyint,
    ->                 var151 decimal);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>         create procedure trig_sp()
    ->                 insert into t1_sp values (1,2,3);
Query OK, 0 rows affected (0.00 sec)

mysql>         create trigger trg before insert on t1_sp
    ->                 for each row call trig_sp();
Query OK, 0 rows affected (0.00 sec)

mysql>         call trig_sp();
ERROR 1424 (HY000): Recursive stored routines are not allowed.
OBN> This eror is exppected.

mysql>         drop procedure trig_sp;
Query OK, 0 rows affected (0.00 sec)

mysql>         drop trigger t1_sp.trg;
Query OK, 0 rows affected (0.00 sec)

mysql>         drop table t1_sp;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

How to repeat:
use test;
drop table if exists t1_sp;
create table t1_sp (
                count integer,
                var136 tinyint,
                var151 decimal);
create procedure trig_sp()
              insert into t1_sp values (1,2,3);
create trigger trg before insert on t1_sp
       for each row call trig_sp();
call trig_sp();
drop procedure trig_sp;
drop trigger t1_sp.trg;
drop table t1_sp;
[13 Jul 2005 1:18] Omer Barnir
Additional information:
The crash (when dropping the table) happens even if the stored procedure is not called.

The crash does not happen if the the trigger is not defined
[13 Jul 2005 20:13] Dmitry Lenev
Hi, Omer!

Actually this bug has nothing to do with trigger recursivity.
The simplified test case for this problem looks like:

use test;
drop table if exists t1_sp;
create table t1_sp (
                count integer,
                var136 tinyint,
                var151 decimal);
create procedure trig_sp() begin end;
create trigger trg before insert on t1_sp
       for each row call trig_sp();
# If procedure is not cached in sp-cache the following DROP TRIGGER will lead to crash.
drop trigger t1_sp.trg;

This bug was fixed by the same patch as bug #11554. 

Fixed in 5.0.10.
[14 Jul 2005 15:41] Paul Dubois
Noted in 5.0.10 changelog.