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;

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>

Crashing bug in 5.1

Patch

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

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.

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.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

Pushed to 5.1-telco-6.3, documented in the 5.1.23-ndb-6.3.11 changelog.