| Bug #32943 | ALTER TABLE DROP PARTITION fails if trigger is present | ||
|---|---|---|---|
| Submitted: | 3 Dec 2007 20:45 | Modified: | 28 Mar 10:33 |
| Reporter: | Philip Stoev | ||
| Status: | Closed | ||
| Category: | Server: Partition | Severity: | S2 (Serious) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Mikael Ronstrom | Target Version: | 5.1+ |
| Triage: | D2 (Serious) | ||
[3 Dec 2007 22: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 15:07]
Mattias Jonsson
Crashing bug in 5.1
[28 Jan 16:04]
Mikael Ronstrom
Patch
Attachment: bug32943.patch (text/x-patch), 14.09 KiB.
[2 Feb 0: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 15: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 12:19]
Bugs System
Pushed into 5.1.24-rc
[27 Mar 18:51]
Bugs System
Pushed into 6.0.5-alpha
[28 Mar 10: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 21:53]
Jon Stephens
Pushed to 5.1-telco-6.3, documented in the 5.1.23-ndb-6.3.11 changelog.
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;