Bug #63884 Algorithm used for dropping partitioned table can lead to inconsistency

• Submitted: 30 Dec 2011 20:05
• Modified: 17 Jun 2013 13:03
• Reporter: Sveta Smirnova
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1.62, 5.5.20
• OS: Any
• CPU Architecture: Any

[30 Dec 2011 20:05] Sveta Smirnova

Description:
When mysqld drops partitioned table it drops *par file first. If drop operation fails, for example, because server crash, data directory is in inconsistent state and it is hard to finally drop the table.

How to repeat:
1. Modify 5.1 sources as follow:

$bzr diff sql/ha_partition.cc 
=== modified file 'sql/ha_partition.cc'
--- sql/ha_partition.cc	2011-09-15 17:26:38 +0000
+++ sql/ha_partition.cc	2011-12-30 19:42:42 +0000
@@ -1902,6 +1902,8 @@
     if ((error= handler::delete_table(from)))
       DBUG_RETURN(error);
   }
+if (!create_info)
+exit(-1);
   /*
     Since ha_partition has HA_FILE_BASED, it must alter underlying table names
     if they do not have HA_FILE_BASED and lower_case_table_names == 2.

2. Create test case for MTR:

$cat ../tests/t/bug.test 
--source include/have_innodb.inc

create table t1(
f1 int,
f2 int) engine=innodb
PARTITION BY RANGE (f2) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;

drop table t1;
select 1;

$cat ../tests/t/bug-master.opt 
--innodb-file-per-table=1

3. Run test case. It fails at DROP TABLE, this is OK

4. Examine content of data directory:

$ls -l var/mysqld.1/data/test/
total 1660
-rw-rw---- 1 ssmirnov wheel   8582 Dec 30 20:51 t1.frm
-rw-rw---- 1 ssmirnov wheel 475136 Dec 30 20:54 t1#P#p0.ibd
-rw-rw---- 1 ssmirnov wheel 475136 Dec 30 20:54 t1#P#p1.ibd
-rw-rw---- 1 ssmirnov wheel 475136 Dec 30 20:54 t1#P#p2.ibd
-rw-rw---- 1 ssmirnov wheel 245760 Dec 30 20:54 t1#P#p3.ibd

5. Restart test as follow: perl ./mtr --start-dirty bug &

6. Connect to the server and try to access and drop the table:

$../client/mysql -h127.0.0.1 -P13000 -uroot test
...
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> select * from t1;
ERROR 1105 (HY000): Failed to read from the .par file

Suggested fix:
Either don't drop par file before all data is finally dropped (rename to some temporary name, for example) or provide a way to drop the table if par file is missed

[17 Jun 2013 13:03] Jon Stephens

Fixed in 5.1+. Documented as follows in the MySQL 5.1.71, 5.5.33, 5.6.13, and 5.7.2 changelogs:

      When dropping a partitioned table, the table .par file was deleted first,
      before the table definition or data. This meant that, if the server failed
      during the drop operation, the table could be left in an inconsistent
      state in which it could neither be accessed nor dropped.

      The fix for this problem makes the following changes:

          ·Now, the .par file is not removed until all table data 
          has been deleted. 

          ·When executing DROP TABLE of a partitioned table, in the 
          event that the .par file is missing, the .frm file is 
          immediately deleted.

Closed.