Bug #46483 drop table of partitioned table may leave extraneous file
Submitted: 30 Jul 2009 18:53 Modified: 19 Dec 2009 11:05
Reporter: Zardosht Kasheff (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.36 OS:Any
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: add_index, partitions, regression
Triage: Triaged: D3 (Medium)

[30 Jul 2009 18:53] Zardosht Kasheff
Description:
The following happens on storage engines that implement handler::add_index, and have the flag HA_ONLINE_ADD_INDEX_NO_WRITES returned in handlerton->alter_table_flags.

The following commands leave a temporary .par file lying around in a databases' data directory:
CREATE TABLE t1 (a int, b int)
PARTITION BY list(a) (
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2)
);
INSERT INTO t1 VALUES (1, 1);
CREATE INDEX i1 ON t1 (a);
DROP TABLE t1;

After the drop table, there is a temporary file (e.g. #sql-5830_1.par) lying around. As a result, trying to drop the database that held t1 fails.

This worked in 5.1.30. This seems to be a regression in 5.1.36.

Although we have not really debugged the issue, we noticed the following.

When stepping through code, in 5.1.36, quick_rm_table gets called with base=NULL, which causes ha_delete_table to do nothing. This did not happen in 5.1.30. In 5.1.30, base was not NULL.

How to repeat:
written above

Suggested fix:
the .par file should be removed as well.
[31 Jul 2009 6:49] Sveta Smirnova
Thank you for the report.

Please indicate all index flags you use.
[31 Jul 2009 6:49] Sveta Smirnova
Bug #45961 can be related.
[31 Jul 2009 11:54] Zardosht Kasheff
Here are our flags:

HA_ONLINE_ADD_INDEX_NO_WRITES| 
HA_ONLINE_DROP_INDEX_NO_WRITES |
HA_ONLINE_ADD_UNIQUE_INDEX_NO_WRITES| 
HA_ONLINE_DROP_UNIQUE_INDEX_NO_WRITES
[1 Aug 2009 13:00] Sveta Smirnova
Thank you for the feedback.

Verified as described, although in my test CREATE INDEX fails.

Test used:

CREATE TABLE t1 (a int, b int)
PARTITION BY list(a) (
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2)
);
INSERT INTO t1 VALUES (1, 1);

--error 1031
CREATE INDEX i1 ON t1 (a);
DROP TABLE t1;

--let $MYSQLD_DATADIR=`select @@datadir`
--exec ls $MYSQLD_DATADIR/test

$bzr diff
=== modified file 'sql/ha_partition.cc'
--- sql/ha_partition.cc 2009-07-08 12:11:34 +0000
+++ sql/ha_partition.cc 2009-08-01 12:47:34 +0000
@@ -142,7 +142,10 @@
 static uint alter_table_flags(uint flags __attribute__((unused)))
 {
   return (HA_PARTITION_FUNCTION_SUPPORTED |
-          HA_FAST_CHANGE_PARTITION);
+          HA_FAST_CHANGE_PARTITION
+                 |
+                 HA_ONLINE_ADD_INDEX_NO_WRITES
+                 );
 }
 
 const uint ha_partition::NO_CURRENT_PART_ID= 0xFFFFFFFF;

Apply this patch, then run the test to repeat described behavior.
[1 Aug 2009 15:16] Zardosht Kasheff
Has it been verified to be a regression from 5.1.30?
[4 Aug 2009 6:33] Sveta Smirnova
Zardosht, thank you for the feedback.

You are right: bug is not repeatable with version 5.1.30
[2 Sep 2009 11:20] 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/82203

3093 Sergey Vojtovich	2009-09-02
      BUG#46483 - drop table of partitioned table may leave
                  extraneous file
      
      Online/fast ALTER TABLE of a partitioned table may leave
      temporary file in database directory.
      
      Fixed by removing unnecessary call to
      handler::ha_create_handler_files(), which was creating
      partitioning definition file.
     @ mysql-test/r/partition_innodb.result
        A test case for BUG#46483.
     @ mysql-test/t/partition_innodb.test
        A test case for BUG#46483.
     @ sql/unireg.cc
        Do not call ha_create_handler_files() when we were requested
        to create only dot-frm file.
[3 Sep 2009 11:24] Mattias Jonsson
Patch approved, OK to push
[11 Sep 2009 10:45] 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/83035

3599 Sergey Vojtovich	2009-09-11
      Follow-up to fix for
      BUG#46483 - drop table of partitioned table may leave extraneous file
      
      With 6.0 code base we need to create and drop handler files for
      temporary altered table. This is different from 5.1 where temporary
      altered table is never referenced during fast add/drop index.
     @ sql/sql_base.cc
        When requested to drop only dot-frm file of temporary table,
        drop handler files as well (they're accompanying dot-frm).
     @ sql/sql_partition.cc
        Added missing DBUG_RETURN.
     @ sql/unireg.cc
        Revert fix for BUG#46483.
[14 Sep 2009 16:03] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[16 Sep 2009 8:36] Jon Stephens
Documented bugfix in the 5.4.4 changelog as follows:

        An online or fast ALTER TABLE of a partitioned table could 
        leave behind temporary files in the database directory.

Set status to NDI, waiting for push to 5.1.
[2 Oct 2009 1:25] Paul Dubois
Moved 5.4 changelog entry from 5.4.4 to 5.4.3.
[6 Oct 2009 8:59] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:ingo.struewing@sun.com-20090916070128-6053el2ucp5z7pyn) (merge vers: 5.1.39) (pib:11)
[6 Oct 2009 9:38] Jon Stephens
Bugfix also documented in the 5.1.40 changelog. Closed.
[18 Dec 2009 10:34] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:49] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:04] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:19] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[19 Dec 2009 11:05] Jon Stephens
No additional changelog entries needed. Closed.