Bug #17128 Partitions: crash if event adds and drops
Submitted: 5 Feb 2006 1:41 Modified: 16 Jun 2006 11:33
Reporter: Peter Gulutzan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.7-beta-debug OS:Linux (SUSE 10.0)
Assigned to: Assigned Account CPU Architecture:Any

[5 Feb 2006 1:41] Peter Gulutzan
Description:
I create a partitioned InnoDB table. I start an event which
repeatedly adds and drops a partition (well, it tries to).
I start the event scheduler. I say "alter table drop partition".
Crash.

How to repeat:
/* Assume current database is db1.
   If you repeat this test, you must use a different table
   name each time, replacing all occurrences of 'txe' with
   the new table name.
   Allow up to 2 minutes for crash to occur. */

delimiter //

CREATE TABLE txe (s1 int) PARTITION BY RANGE (s1) (PARTITION p1 VALUES LESS THAN (1) engine=innodb, PARTITION p2 VALUES LESS THAN (2) engine=innodb)//

create event e on schedule every 1 second do begin alter table db1.txe add partition (partition p2 values less than (2)); alter table db1.txe drop partition p2; end//

set global event_scheduler = 1//

alter table txe drop partition p2//
[6 Feb 2006 1:12] MySQL Verification Team
Thank you for the bug report.

060205 23:10:25 [Note] libexec/mysqld: ready for connections.
Version: '5.1.7-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
060205 23:11:17 [Note] Scheduler loaded 1 event
060205 23:11:17 [Note] Scheduler thread started
060205 23:11:17 [Note]     EVEX EXECUTING event db1.e [EXPR:1]
060205 23:11:17 [Note]     EVEX EXECUTED event db1.e  [EXPR:1]. RetCode=1
060205 23:11:18 [Note]     EVEX EXECUTING event db1.e [EXPR:1]
060205 23:11:18 [Note]     EVEX EXECUTED event db1.e  [EXPR:1]. RetCode=1
060205 23:11:19  InnoDB: Warning: MySQL is trying to drop table `db1/txe#P#p2`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
060205 23:11:19 [Note]     EVEX EXECUTING event db1.e [EXPR:1]
060205 23:11:19  InnoDB: Dropped table db1/txe#P#p2 in background drop queue.
060205 23:11:19  InnoDB: Warning: MySQL is trying to drop table `db1/txe#P#p2`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
060205 23:11:19 [Note]     EVEX EXECUTED event db1.e  [EXPR:1]. RetCode=1
060205 23:11:20 [Note]     EVEX EXECUTING event db1.e [EXPR:1]
060205 23:11:20  InnoDB: Dropped table db1/txe#P#p2 in background drop queue.
060205 23:11:20  InnoDB: Warning: MySQL is trying to drop table `db1/txe#P#p2`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
060205 23:11:20 [Note]     EVEX EXECUTED event db1.e  [EXPR:1]. RetCode=1
060205 23:11:21 [Note]     EVEX EXECUTING event db1.e [EXPR:1]
060205 23:11:21  InnoDB: Dropped table db1/txe#P#p2 in background drop queue.
060205 23:11:21  InnoDB: Warning: MySQL is trying to drop table `db1/txe#P#p2`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
060205 23:11:21 [Note]     EVEX EXECUTED event db1.e  [EXPR:1]. RetCode=1
060205 23:11:22 [Note]     EVEX EXECUTING event db1.e [EXPR:1]
060205 23:11:22  InnoDB: Error: table `db1/txe#P#p2` already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html
060205 23:11:22InnoDB: Assertion failure in thread 1132432304 in file row0mysql.c line 1634
InnoDB: Failing assertion: index
[1 Jun 2006 10:16] Andrey Hristov
This is likely partitioning problem. Do manually the following:

CREATE TABLE txe (s1 int) PARTITION BY RANGE (s1) (PARTITION p1 VALUES LESS THAN
(1) engine=innodb, PARTITION p2 VALUES LESS THAN (2) engine=innodb);
alter table db1.txe add partition (partition p2 values less than (2));
alter table txe drop partition p2;

and you get the following in the error log :
060601 12:14:25  InnoDB: Warning: MySQL is trying to drop table `db1/txe#P#p2`
InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
060601 12:14:26  InnoDB: Dropped table db1/txe#P#p2 in background drop queue.

mysql> CREATE TABLE txe (s1 int) PARTITION BY RANGE (s1) (PARTITION p1 VALUES LESS THAN
    -> (1) engine=innodb, PARTITION p2 VALUES LESS THAN (2) engine=innodb);
Query OK, 0 rows affected (0.04 sec)

mysql> alter table db1.txe add partition (partition p2 values less than (2));
ERROR 1504 (HY000): Duplicate partition name p2
mysql> alter table db1.txe drop partition p2;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
[7 Jun 2006 18:33] Peter Gulutzan
Here is another case. This time there is no event and no procedure, but the statements must be run without pausing. So cut and paste all of them at once into mysql client.

create database dh;
use dh;
create table tn (s1 int) engine=innodb partition by range (s1) (partition p0 values less than (0), partition p1 values less than (1));
alter table tn drop partition p1;
alter table tn drop partition p0;
alter table tn add partition (partition p1 values less than (1));
[15 Jun 2006 18:15] Mikael Ronström
I verified that the test PeterG put in last works without problem using the latest tree.
I also verified that it didn't work on an older tree.
So it should safe to now put this into duplicate (of bug#19122)
[16 Jun 2006 11:33] Alexander Barkov
Duplicate with bug#19122

Thanks, Mikael.