MySQL Bugs: #43729: Dropping and recreating a partition causes duplicate partition error

Bug #43729	Dropping and recreating a partition causes duplicate partition error
Submitted:	18 Mar 2009 14:46	Modified:	29 Nov 2013 18:20
Reporter:	Oli Sennhauser	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.30, 5.1 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	InnoDB Partition

Description:
Customer does a drop partition followed by a create partition (because of the lack of truncate partition).

Sometimes he gets a

ERROR 1517 (HY000) at line 1: Duplicate partition name p14

InnoDB: though there are still open handles to it.
InnoDB: Adding the table to the background drop queue.
090317 23:00:11 InnoDB: Dropped table xxx in background drop queue.

This happens under high load.

How to repeat:
I was not yet able to reproduce the situation. Customer tries to reproduce as well.

Suggested fix:
Either first query (drop) should not return or second should wait.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Thank you for the report.

Verified as described.

Customer says this happens only on slow disk. So to recreate modify code as following:

=== modified file 'mysys/my_delete.c'
--- mysys/my_delete.c   2007-12-07 20:27:48 +0000
+++ mysys/my_delete.c   2009-08-26 06:44:13 +0000
@@ -22,7 +22,7 @@
   int err;
   DBUG_ENTER("my_delete");
   DBUG_PRINT("my",("name %s MyFlags %d", name, MyFlags));
-
+sleep(5);
   if ((err = unlink(name)) == -1)
   {
     my_errno=errno;

Then run following test for the testsuite:

create table `data` (
`id` bigint(20) default null,
`f1` timestamp not null default current_timestamp on update
current_timestamp
) engine=myisam default charset=latin1 partition by list (
dayofmonth(f1))
(
partition p14 values in (14) engine = myisam,
partition p29 values in (29) engine = myisam,
partition p30 values in (30) engine = myisam
);

--send alter table `data` drop partition p14

connect (addconroot, localhost, root,,);
connection addconroot;

alter table `data` add partition ( partition p14 values in (14));

Probably you have to run it several times like ./mtr bug43729 --repeat=10

triage: setting to 2343 W3 - flush tables between drop/create
Need e/r values to set target

InnoDB does do a certain part of the drop table processing in the background.
So I am sure we can get the same problem by doing DROP TABLE and CREATE TABLE
with the same name after each other.

A very simple workaround is to not reuse the same name of the partition
in the statement immediately following, or have an extra check in the application.
Maybe a FLUSH of some kind between solves it as well.

May be related to bug#47343.

Changing to 'Open' because of the verification is not valid.

The verified test does not need the '+sleep(5)' to be reproducable, but it does not verify the bug, since it is a correct failure (due to timing the 'add partition p14' can lock the table before the 'drop partition p14' and hence return 'duplicate partition' error).

I think this is only happens with InnoDB. InnoDB does sometimes queue 'drop table/partition' into a background drop queue (as seen in the report) which is one ingredient to this bug. There has been several bugfixes in this area (and even more in 5.5+) which might have solved this bug too.

Mattias,

thank you for the feedback. But test explicitly adds ENGINE=MyISAM option. Set it back to "Open" if you think this is still OK.

Sveta, The MyISAM test is not a bug, that is expected behavior (i.e. due to timing the 'ADD PARTITION' can lock the table before 'DELETE PARTITION' which results in the behavior).

I think it needs to be verified with InnoDB including getting the 'InnoDB: though there are still open handles to it.' message in the error log.

If not possible to verify on latest version and if important, verify it in 5.1.30 and then I can see which changeset that fixed the bug or investigate further.

I tried to repeat it with same test and InnoDB and could not.

If somebody hit it in latest versions, please comment.