MySQL Bugs: #50423: Crash on second call of a procedure dropping a trigger

Bug #50423	Crash on second call of a procedure dropping a trigger
Submitted:	18 Jan 2010 16:41	Modified:	19 Jun 2010 0:22
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.43	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
My script:
----------
--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TRIGGER IF EXISTS tr1;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

CREATE TABLE t1 (f1 INTEGER) ;

CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @aux = 1 ;
CREATE PROCEDURE p1 () DROP TRIGGER tr1  ;
CALL p1 ();
# The next statement crashes the server.
CALL p1 ();

# Cleanup
DROP TABLE t1;
DROP PROCEDURE p1;

Result on 5.1.43 mysql-5.1-bugteam revno: 3318 2010-01-17
---------------------------------------------------------
At line 13: query 'CALL p1 ()' failed: 2013: Lost connection to MySQL server during query
Core was generated by `/work2/5.1/mysql-5.1-bugteam-work/sql/mysqld --defaults-group-suffix=.1 --defau'.
Program terminated with signal 11, Segmentation fault.
[New process 9974]
[New process 9932]
[New process 9934]
#0  0x00007f2c7be90ce6 in pthread_kill () from /lib64/libpthread.so.0
#0  0x00007f2c7be90ce6 in pthread_kill () from /lib64/libpthread.so.0
#1  0x0000000000b1daaf in my_write_core (sig=11) at stacktrace.c:329
#2  0x00000000006bbfe7 in handle_segfault (sig=11) at mysqld.cc:2569
#3  <signal handler called>
#4  0x00000000007274c8 in TABLE_LIST::reinit_before_use (this=0x122b5d8, thd=0x11bf0c8) at table.cc:4619
#5  0x0000000000775ee3 in reinit_stmt_before_use (thd=0x11bf0c8, lex=0x1246698) at sql_prepare.cc:2375
#6  0x00000000008a2bbb in sp_lex_keeper::reset_lex_and_exec_core (this=0x1247678, thd=0x11bf0c8, nextp=0x41db01b8, open_tables=false, instr=0x1247638) at sp_head.cc:2744
#7  0x00000000008a92e4 in sp_instr_stmt::execute (this=0x1247638, thd=0x11bf0c8, nextp=0x41db01b8) at sp_head.cc:2873
#8  0x00000000008a51d9 in sp_head::execute (this=0x1246108, thd=0x11bf0c8) at sp_head.cc:1255
#9  0x00000000008a5fa4 in sp_head::execute_procedure (this=0x1246108, thd=0x11bf0c8, args=0x11c1440) at sp_head.cc:1988
#10 0x00000000006d4fa5 in mysql_execute_command (thd=0x11bf0c8) at sql_parse.cc:4385
#11 0x00000000006d70fb in mysql_parse (thd=0x11bf0c8, inBuf=0x121df98 "CALL p1 ()", length=10, found_semicolon=0x41db1ef0) at sql_parse.cc:5963
#12 0x00000000006d7f76 in dispatch_command (command=COM_QUERY, thd=0x11bf0c8, packet=0x12120c9 "CALL p1 ()", packet_length=10) at sql_parse.cc:1233
#13 0x00000000006d93e0 in do_command (thd=0x11bf0c8) at sql_parse.cc:874
#14 0x00000000006c5775 in handle_one_connection (arg=0x11bf0c8) at sql_connect.cc:1127
#15 0x00007f2c7be8c040 in start_thread () from /lib64/libpthread.so.0
#16 0x00007f2c7b13a08d in clone () from /lib64/libc.so.6
#17 0x0000000000000000 in ?? ()

5.5.99-m3 (mysql-next-mr revno: 2962 2010-01-18) and 
6.0.14-alpha (mysql-6.0-codebase-bugfixing revno: 3831 2010-01-15)
do not show this bug. The second "CALL p1 ()" fails correct
with "ERROR HY000: Trigger does not exist".

My environment:
---------------
- MySQL compiled from source
  ./BUILD/compile-pentium64-debug-max
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo (64 Bit)

How to repeat:
See above

Suggested fix:
The result on mysql-next-mr and mysql-6.0-codebase-bugfixing
give the impression that this bug will be fixed within the
official/final releases soon.
And most probably this bug is just an duplicate of another
bug which was fixed in next-mr etc. Unfortunately I did not
found another bug which matched.
- Feel free to set the current bug to "duplicate" or
  "Can't repeat" or to modify the title.
- Please take care that the next release for customers
   does not suffer from this bug. Maybe it's useful to append
   my testcase to the regression tests.

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/commits/98483

3338 Davi Arnaut	2010-01-28
      Bug#50423: Crash on second call of a procedure dropping a trigger
      
      The problem was that a DROP TRIGGER statement inside a stored
      procedure could cause a crash in subsequent invocations. This
      was due to the addition, on the first execution, of a temporary
      table reference to the stored procedure query table list. In
      a subsequent invocation, there would be a attempt to reinitialize
      the temporary table reference, which by then was already gone.
      
      The solution is to backup and reset the query table list each
      time a trigger needs to be dropped. This ensures that any temp
      changes to the query table list are discarded. It is safe to
      do so at this time as drop trigger is restricted from more
      complicated scenarios (ie, not allowed within stored functions,
      etc).
     @ mysql-test/r/sp-bugs.result
        Add test case result for Bug#50423
     @ mysql-test/t/sp-bugs.test
        Add test case for Bug#50423
     @ sql/sql_trigger.cc
        Backup and reset the query table list.
        Remove now unnecessary manual reset of the query table list.

Queued to 5.1-bugteam

Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:davi.arnaut@sun.com-20100128144114-qrv4l81fnu5e0v7i) (merge vers: 5.1.45) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)

Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

The second or subsequent invocation of a stored procedure containing
DROP TRIGGER could cause a server crash.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)