Bug #30102 Failed RENAME of partitioned table corrupts table files
Submitted: 27 Jul 2007 15:17 Modified: 5 Aug 2009 8:07
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.21 OS:FreeBSD
Assigned to: Anurag Shekhar CPU Architecture:Any
Tags: partition, qc, rename table

[27 Jul 2007 15:17] Martin Friebe
Description:
if you try to rename a partitioned table, and a filesystem error occors during creation of the 2nd (or nay later) of the MYD/MYI files for the partition, then reanme table will leave some renamed and some not renamed files. => table is unusable

assuming table t1 partition p1, p2.
- rename t1 to t2
- rename table does t1#p1 => t2#p1 successfull
- rename table fails on  t1#p2 =>  t2#p2

the first rename is not undone, the table is now corrupt

---
The how to repeat mayonly work on certain systems, it relies on the filesystem giving an error if a filename is to long (since mysql encodes a dot in a filename to @002e this filename will be very long.)

I have chosen the length in a way that the filename is small enough to create a partition with a short name, but to long to create a partition with a long name (2nd partition)
It is possible that the required lengt differes on other systems.

I have only teste this on FreeBSD.

How to repeat:
drop table   t1;
create table t1
 (a int)
PARTITION BY RANGE (a) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1xxxxxxxxxx VALUES LESS THAN (11)
);

rename table t1 to `.................................................`;

filesystem before rename
-rw-rw----   1 martin  martin     0 Jul 27 15:58 t1#P#p0.MYD
-rw-rw----   1 martin  martin  1024 Jul 27 15:58 t1#P#p0.MYI
-rw-rw----   1 martin  martin     0 Jul 27 15:58 t1#P#p1xxxxxxxxxx.MYD
-rw-rw----   1 martin  martin  1024 Jul 27 15:58 t1#P#p1xxxxxxxxxx.MYI
-rw-rw----   1 martin  martin  8554 Jul 27 15:58 t1.frm
-rw-rw----   1 martin  martin    36 Jul 27 15:58 t1.par

filesystem after rename
-rw-rw----   1 martin  martin     0 Jul 27 15:58 @002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e#P#p0.MYD
-rw-rw----   1 martin  martin  1024 Jul 27 15:58 @002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e#P#p0.MYI
-rw-rw----   1 martin  martin     0 Jul 27 15:58 t1#P#p1xxxxxxxxxx.MYD
-rw-rw----   1 martin  martin  1024 Jul 27 15:58 t1#P#p1xxxxxxxxxx.MYI
-rw-rw----   1 martin  martin  8554 Jul 27 15:58 t1.frm
-rw-rw----   1 martin  martin    36 Jul 27 15:58 t1.par

Suggested fix:
 undo any renamed files, if an error occurs

trying to drop the table may lead to a server crash. The crash could be realted to  Bug #29322 or Bug #28490
[27 Jul 2007 15:42] MySQL Verification Team
Thank you for the bug report. Verified as described on FC 6.0.
[3 Dec 2008 14:57] Mikael Ronström
The comment in the bug is correct that this can be fixed by
creating a rollback case in the routine del_ren_cre_table.
This routine handles failures of create table correctly. It
treats errors at rename and delete differently, in this
case it tries to continue with the rename and delete. For
delete this seems ok, since the user has asked to delete
the table we should remove as much as possible since it
isn't possible to restore deleted files.

For rename we can simply rename in reverse order where it was successful
and do that as soon as we find an error. We do however need to
integrate handler::rename_table into this as well. This routine should
also clean up after itself in error cases, so the problem can occur also
for non-partitioned tables.

So solution is:
1) Make handler::rename_table atomic
2) Integrate handler::rename_table into del_ren_cre_table
3) Make del_ren_cre_table rollback rename operation if
unsuccessful with any rename operation (this also makes
del_ren_cre_table atomic for renames as it is now for
creates)
4) Try to delete as many files as possible in handler::delete_table
Currently it stops deleting if it hits an error.
5) Handle delete case when nothing still was deleted as a special case,
such that we stop immediately in case of no permission for operation.
6) Switch order of call to delete_table and del_ren_cre_table to ensure
that we don't run into problems by removing partition files before we're
sure we can remove the frm file.
[10 Dec 2008 14:15] MySQL Verification Team
See bug: http://bugs.mysql.com/bug.php?id=41272.
[19 Dec 2008 8:02] 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/62053

2742 Mattias Jonsson	2008-12-19
      Bug#30102: Rename table does corrupt tables with partition files on failure
      
      Problem was that a failing rename just left the partitions at the state
      it was at the failure
      
      Solution was to try to revert the started rename if a failure occured.
[19 Dec 2008 10:42] Mattias Jonsson
I will fix the delete code path to work as following:
If the first file to delete fails, then return directly.
If the first file was deleted, then continue and delete as much as possible.
[29 Dec 2008 17:51] 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/62429

2742 Mattias Jonsson	2008-12-29
      Bug#30102: Rename table does corrupt tables with partition files on failure
      
      Problem was that a failing rename just left the partitions at the state
      it was at the failure.
      
      Solution was to try to revert the started rename if a failure occured.
[24 Jul 2009 10:11] 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/79237

3037 Anurag Shekhar	2009-07-24
      Bug#30102: Rename table does corrupt tables with partition files on failure
      
      Problem was that a failing rename just left the partitions at the state
      it was at the failure.
      
      Solution was to try to revert the started rename if a failure occured.
     @ mysql-test/r/partition_not_embedded.result
        Bug#30102: Rename table does corrupt tables with partition files on failure
        
        New result file
     @ mysql-test/t/partition_not_embedded.test
        Bug#30102: Rename table does corrupt tables with partition files on failure
        
        New test file
        (list_files does not report the files in embedded)
     @ sql/ha_partition.cc
        Bug#30102: Rename table does corrupt tables with partition files on failure
        
        Better error handling for rename partitions (reverting the started rename
        operation)
        
        Different order of files for delete.
     @ sql/handler.cc
        Bug#30102: Rename table does corrupt tables with partition files on failure
        
        Tries to remove as many table files as possible
        if the first delete succeeds.
[27 Jul 2009 11:21] 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/79323

3039 Anurag Shekhar	2009-07-27
      Bug #30102 rename table does corrupt tables with partition files on failure.
      
      One of the tests introduced for this bug was failing 
      because of path size restriction in windows.
      Moved the test case to a new test which is disabled under windows.
     @ mysql-test/r/partition_not_embedded.result
        updated test results after removing a test case.
     @ mysql-test/r/partition_rename_longfilename.result
        Test result for partition_rename_longfilename
     @ mysql-test/t/partition_not_embedded.test
        Removed the test case which tests renaming partition table such that
        the file name is 255 char long.
     @ mysql-test/t/partition_rename_longfilename.test
        Test case to test renaming partition table such that
        the file name is 255 char long.
        Moved from partition_no_embedded.
[4 Aug 2009 19:52] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:anurag.shekhar@sun.com-20090727113741-semv4wyk33p152ps) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 20:45] Bugs System
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)
[5 Aug 2009 8:00] Jon Stephens
Fixed synopsis.
[5 Aug 2009 8:07] Jon Stephens
Documented bugfix in the 5.1.38 and 5.4.4 changelogs as follows:

      A failed RENAME TABLE operation on a partitioned table left the 
      table in an unusable state, due to only some of the table files 
      having been renamed.
[12 Aug 2009 21:57] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[14 Aug 2009 22:50] Paul DuBois
Ignore previous comment about 5.4.2.
[1 Oct 2009 5:58] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[7 Oct 2009 1:43] Paul DuBois
The 5.4 fix has been pushed into 5.4.2.