Bug #13399 Crash when executing PS/SP which should activate trigger which is now dropped

• Submitted: 22 Sep 2005 10:53
• Modified: 20 Nov 2005 3:17
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: 5.0-bk-2005-09-22
• OS: Linux (Linux/x86)
• Assigned to: Dmitry Lenev
• CPU Architecture: Any

[22 Sep 2005 10:53] Dmitry Lenev

Description:
Attempt to execute prepared statement which updates table for which trigger existed during preparation of this statement, but was dropped later, leads to server crash.

Similar problem can be observed for stored routines if we will drop trigger (or simply do FLUSH TABLES) between two routine re-executions.

How to repeat:
# Crash 1
drop table if exists t1, t2;
drop trigger t1_bi;
create table t1 (id int);
create table t2 (id int);
create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id);
prepare stmt1 from "insert into t1 values (10)";
# Actually it is enough to do FLUSH TABLES instead of DROP TRIGGER
drop trigger t1_bi;
# Server crashes here
execute stmt1;

# Crash 2
drop table if exists t1, t2;
drop trigger t1_bi;
drop procedure p1; 
create table t1 (id int);
create table t2 (id int);
create trigger t1_bi before insert on t1 for each row insert into t2 values (new.id);
create procedure p1() insert into t1 values (10); 
call p1();
# FLUSH TABLES will cause crash too
drop trigger t1_bi;
# Server crashes again
call p1();

Suggested fix:
We should either invalidate PS/SP's which depend on triggers when we are dropping or changing them or make them robust against such changes by removing this dependancy
(this could be achieved by rebuilding of prelocking set for each execution for example).

[28 Oct 2005 15:27] Dmitry Lenev

Also see similar bug for stored routines - bug #12329

[17 Nov 2005 12:54] Dmitry Lenev

Fixed in version 5.0.17

[20 Nov 2005 3:17] Paul DuBois

Noted in 5.0.17 changelog.