Bug #32943 ALTER TABLE DROP PARTITION fails if trigger is present
Submitted: 3 Dec 2007 19:45 Modified: 28 Mar 2008 9:33
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: Mikael Ronström CPU Architecture:Any
Triage: D2 (Serious)

[3 Dec 2007 19:45] Philip Stoev
Description:
If ALTER TABLE DROP PARTITION is executed on a table that has a trigger, the statement will fail with

ERROR 1100 (HY000): Table '(null)' was not locked with LOCK TABLES

even if the trigger does not reference any tables

The contents of the trigger do not appear to matter, as long as the trigger is syntactically valid, which appears to require at least some statement between BEGIN and END. Myisam, falcon and innodb all appear to be affected.

How to repeat:
CREATE TABLE t1 (f1 integer) PARTITION BY RANGE (f1) (
    PARTITION p0 VALUES LESS THAN (4),
    PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

DELIMITER |
CREATE TRIGGER tr1 BEFORE INSERT ON t1
	FOR EACH ROW BEGIN SET @a = 1;
	END|
DELIMITER ;

ALTER TABLE t1 DROP PARTITION p0;
[3 Dec 2007 21:29] Miguel Solorzano
Thank you for the bug report. Verified as described:

c:\dev>6.0\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-nt Source distribution

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

mysql> CREATE TABLE t1 (f1 integer) PARTITION BY RANGE (f1) (
    ->     PARTITION p0 VALUES LESS THAN (4),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.42 sec)

mysql>
mysql> DELIMITER |
mysql> CREATE TRIGGER tr1 BEFORE INSERT ON t1
    ->  FOR EACH ROW BEGIN SET @a = 1;
    ->  END|
Query OK, 0 rows affected (0.12 sec)

mysql> DELIMITER ;
mysql>
mysql> ALTER TABLE t1 DROP PARTITION p0;
ERROR 1100 (HY000): Table '(null)' was not locked with LOCK TABLES
mysql>
[6 Dec 2007 14:07] Mattias Jonsson
Crashing bug in 5.1
[28 Jan 2008 15:04] Mikael Ronström
Patch

Attachment: bug32943.patch (text/x-patch), 14.09 KiB.

[1 Feb 2008 23:19] Mattias Jonsson
Ok to push.

One minor comments:
Little confusion when using both lower case and upper case syntax in test case (please use upper case for SQL commands)

For documantation later:
LOCK TABLES during ALTER TABLE for partition changes isn't really safe, since there are a small possibility that the lock can be unlocked (with a warning) during ALTER TABLE.
[17 Mar 2008 14:59] Bugs System
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/44111

ChangeSet@1.2565, 2008-03-17 15:56:53+01:00, mattiasj@witty. +2 -0
  Valgrind warnings found after bug#32943 and after merge from -main
  into -engines tree.
  
  hander::table_share was not updated after changing table->s.
[27 Mar 2008 11:19] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 17:51] Bugs System
Pushed into 6.0.5-alpha
[28 Mar 2008 9:33] Jon Stephens
Documented as follows in the 5.1.24 and 6.0.5 changelogs:

        When ALTER TABLE DROP PARTITION was executed on a table on which there
        was a trigger, the statement failed with an error. This occurred even if
        the trigger did not reference any tables.
[31 Mar 2008 19:53] Jon Stephens
Pushed to 5.1-telco-6.3, documented in the 5.1.23-ndb-6.3.11 changelog.