| 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: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.1 | OS: | Any |
| Assigned to: | Mikael Ronström | CPU Architecture: | Any |
[3 Dec 2007 21:29]
MySQL Verification Team
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.
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;