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:
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

[18 Mar 2009 14:46] Oli Sennhauser
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.
[9 May 2009 23:00] Bugs System
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".
[26 Aug 2009 7:32] Sveta Smirnova
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_PRINT("my",("name %s MyFlags %d", name, MyFlags));
   if ((err = unlink(name)) == -1)

Then run following test for the testsuite:

create table `data` (
`id` bigint(20) default null,
`f1` timestamp not null default current_timestamp on update
) engine=myisam default charset=latin1 partition by list (
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
[27 Aug 2009 18:27] Omer Barnir
triage: setting to 2343 W3 - flush tables between drop/create
Need e/r values to set target
[7 Sep 2009 9:28] Mikael Ronström
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.
[18 Jan 2010 15:21] Mattias Jonsson
May be related to bug#47343.
[5 Oct 2010 13:53] Mattias Jonsson
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.
[5 Oct 2010 16:08] Sveta Smirnova

thank you for the feedback. But test explicitly adds ENGINE=MyISAM option. Set it back to "Open" if you think this is still OK.
[15 Oct 2010 15:51] Mattias Jonsson
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.
[29 Nov 2013 18:20] Sveta Smirnova
I tried to repeat it with same test and InnoDB and could not.

If somebody hit it in latest versions, please comment.