Bug #20903 Crash when using CREATE TABLE .. SELECT and triggers
Submitted: 7 Jul 2006 12:42 Modified: 16 May 2007 19:28
Reporter: Tomash Brechko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.23-BK, 5.1.12-BK OS:Linux (Linux Suse 10)
Assigned to: Dmitry Lenev CPU Architecture:Any
Tags: rt_q1_2007

[7 Jul 2006 12:42] Tomash Brechko
Description:
CREATE TABLE IF NOT EXISTS table SELECT crashes the server if table already exists and have a BEFORE INSERT trigger.

How to repeat:
CREATE TABLE t1 (i INT);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET NEW.i = 1;
CREATE TABLE t2 (i INT);
INSERT INTO t2 VALUES (1), (2);
CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
-- Crash happens here
[7 Jul 2006 12:59] Miguel Solorzano
Back trace 5.0.23 Linux Suse 32-bit

Attachment: bt-20903.txt (text/plain), 12.41 KiB.

[7 Jul 2006 13:00] Miguel Solorzano
Thank you for the bug report. Verified as described.
[5 Dec 2006 12:57] Dmitry Lenev
ChangeSet@1.2342, 2006-12-05 15:44:34+03:00, dlenev@mockturtle.local +12 -0
  Proposed patch for bugs:
    #20662 "Infinite loop in CREATE TABLE IF NOT EXISTS ... SELECT
          with locked tables"
    #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
    #24738  "CREATE TABLE ... SELECT is not isolated properly"

http://lists.mysql.com/commits/16454
[8 Mar 2007 7:58] Konstantin Osipov
Setting back to In Progress as this is the same as Bug#20662
[16 May 2007 13:48] Bugs System
Pushed into 5.0.42
[16 May 2007 13:49] Bugs System
Pushed into 5.1.19-beta
[16 May 2007 19:28] Paul Dubois
Noted in 5.0.42, 5.1.19 changelogs.

CREATE TABLE IF NOT EXISTS ... SELECT caused a server crash if the
target table already existed and had a BEFORE INSERT trigger.