Bug #56228 dropping tables from within an active statement crashes server

• Submitted: 24 Aug 2010 18:19
• Modified: 11 Feb 2011 0:07
• Reporter: Lig Isler-Turmelle
• Status: Closed
• Category: MySQL Server: InnoDB Plugin storage engine
• Severity: S2 (Serious)
• Version: 5.1.46sp1, 5.1.50
• OS: Any
• Assigned to: Sunny Bains
• CPU Architecture: Any
• Tags: drop temporary table, InnoDB plugin

[24 Aug 2010 18:19] Lig Isler-Turmelle

Description:
This is a new bug and has to do with dropping tables from within an active statement. In practical terms this means that functions/procedures/triggers are run within the same statement context.  

How to repeat:
I can reproduce the bug with the following stored function.

CREATE FUNCTION bug() RETURNS INT DETERMINISTIC BEGIN INSERT INTO t1 VALUES(NULL); INSERT INTO t2 VALUES(NULL); INSERT INTO t1 VALUES(NULL); INSERT INTO t2 VALUES(NULL); DROP TEMPORARY TABLE t1; RETURN 100; END //

SELECT bug();

This will trigger the assertion.

Suggested fix:
The problem is that the InnoDB expects ALL the AUTOINC locks that were part of the statement to be released in reverse order but at the end of statement. In the above function the locks are being released only for table t1 and it asserts when it finds the autoinc lock on table t2.

The *work around* is to not issue drop table within the same "statement". The long term fix is to handle this special case without impacting performance.

[30 Nov 2010 15:19] Calvin Sun

bug#58191 is marked as a dup of this one.

[3 Jan 2011 23:20] Sunny Bains

Bug# 59235 is a duplicate of this.

[8 Feb 2011 11:57] Bugs System

Pushed into mysql-5.1 5.1.56 (revid:[e-mail]-20110208115233-t04e8k0pwhwtbc1u) (version source revid:[e-mail]-20110208115233-t04e8k0pwhwtbc1u) (merge vers: 5.1.56) (pib:24)

[8 Feb 2011 14:08] Bugs System

Pushed into mysql-5.5 5.5.10 (revid:[e-mail]-20110208140736-1173xnoipufbhowh) (version source revid:[e-mail]-20110208135903-jhzy6wq16b2fx7pg) (merge vers: 5.5.10) (pib:24)

[8 Feb 2011 16:44] Bugs System

Pushed into mysql-trunk 5.6.2 (revid:[e-mail]-20110208155412-tfy4l5hqxi0g7o41) (version source revid:[e-mail]-20110208154951-gzqgr74u4bndswi8) (merge vers: 5.6.2) (pib:24)