MySQL Bugs: #72427: Crash during ALTER TABLE may lead to non-usable table

Bug #72427	Crash during ALTER TABLE may lead to non-usable table
Submitted:	22 Apr 2014 22:06	Modified:	8 Sep 2014 15:18
Reporter:	curt mayer	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.5.16	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When adding or dropping partitions, an intervening crash of the mysql server often results in an inconsistency between the InnoDB data dictionary and the filesystem containing the partition data files.

the database state is not recoverable, since the tables can be neither opened or dropped.

either innodb sees the partition, and mysql does not, which prevents adds,
or mysql sees the partition, and innodb does not, which prevents drops.

either way, the database logs contain the following messages:
the referenced recovery methods are invariably ineffective.

InnoDB: Error: trying to open a table, but could not
InnoDB: open the tablespace file './controller/metricdata_ten_min_agg#P#PART183335.ibd'!
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: It is also possible that this is a temporary table #sql...,
InnoDB: and MySQL removed the .ibd file for this.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
and:
InnoDB: Error; possible reasons:
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.
InnoDB: 2) table `controller`.`metricdata_ten_min_agg` /* Partition `PARTMAX` */ exists in the InnoDB internal data
InnoDB: dictionary though MySQL is trying to rename table 130402 15:08:05 [Warning] Invalid (old?) table or database name '#sql-58ff_1#P#PARTMAX'
`controller`.<result 2 when explaining filename '#sql-58ff_1#P#PARTMAX'> to it.
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
InnoDB: If table `controller`.`metricdata_ten_min_agg` /* Partition `PARTMAX` */ is a temporary table #sql..., then it can be that
InnoDB: there are still queries running on the table, and it will be
InnoDB: dropped automatically when the queries end.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

How to repeat:
create a partitioned table, where each partition contains rows.
alter table <foo> drop partition <bar> ; while this is running, kill -9 the mysqldaemon.

restart mysql, and

select count(*) from foo, see the messages;

alter table <foo> drop partition <bar>
alter table <foo> add partition <bar>

both fail.

Thank you for the report.

Please follow instructions from http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html and inform us if it does not solve the issue.

as indicated in the original bug report, the recovery method in the error message is ineffective.

Thank you for the feedback.

Please provide log of commands you used to recover the tables.

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".

I was able to recreate this issue in 5.6.19 by killing mysqld with signal 9 in the middle of the partition creation.  When I try again to create the partitions, I get this:
                                                                                      
ERROR 1050 (42S01): Table './test/testtable#P#p_06_07_2014' already exists

SHOW CREATE TABLE testtable shows no partitions.

Dropping the table testtable and recreating it has no effect.  Still can't create the partitions.

Restarting MySQL has no effect.  

Error log shows:  

2014-09-05 16:37:58 7f5a8d0e3700  InnoDB: Error; possible reasons:
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.
InnoDB: 2) table `test`.`testtable` /* Partition `p_06_07_2014` */ exists in the InnoDB internal data
InnoDB: dictionary though MySQL is trying to rename table `test`.`#sql-1d5e_1#P#p_06_07_2014` /* Partition `p_06_07_2014` */ to it.
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
InnoDB: If table `test`.`testtable` /* Partition `p_06_07_2014` */ is a temporary table #sql..., then it can be that
InnoDB: there are still queries running on the table, and it will be
InnoDB: dropped automatically when the queries end.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.

The only way I was able to fix this was to dump, drop, and restore the entire DB.

I have verified the '[8 Sep 11:05] Jim Parks' in current mysql-5.6 branch.

1) patch the server with:
=== modified file 'sql/ha_partition.cc'
--- sql/ha_partition.cc
+++ sql/ha_partition.cc 2014-09-08 14:13:02 +0000
@@ -2312,6 +2312,7 @@ int ha_partition::del_ren_table(const ch
       error= (*file)->ha_rename_table(from_buff, to_buff);
       if (error)
         goto rename_error;
+      DBUG_EXECUTE_IF("ha_partition_crash_rename", DBUG_SUICIDE(););
     }
     else                                        // delete branch
     {

2) run this mysql-test-run.pl test:
let $MYSQLD_DATADIR=`SELECT @@datadir`;
SET GLOBAL innodb_file_per_table = 0;
CREATE TABLE t1 (a int PRIMARY KEY, b varchar(64)) ENGINE = InnoDB;

--echo # Crashing the server during renaming partitions
SET SESSION debug="+d,ha_partition_crash_rename";
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--disable_reconnect
# CR_SERVER_LOST
--error 2013
ALTER TABLE t1 PARTITION BY HASH (a) PARTITIONS 2;
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--enable_reconnect
let $WAIT_COUNT=6000;
--source include/wait_time_until_connected_again.inc

--echo # Server is started again
SET GLOBAL innodb_file_per_table = 0;

--echo # Files in test database:
--list_files $MYSQLD_DATADIR/test
SHOW TABLES;
--echo # Entries in SYS_TABLES (InnoDB internal data dictionary):
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/%';

create table t1 (a int PRIMARY KEY, b varchar(64)) ENGINE = InnoDB;
--echo # This will fail due to the partition names already exists in the
--echo # InnoDB internal data dictionary/SYS tables.
--error ER_TABLE_EXISTS_ERROR
ALTER TABLE t1 PARTITION BY HASH (a) PARTITIONS 2;

This is a general problem in ALTER TABLE ... ALGORITHM=COPY, which gets worse by partitions.

During ALTER ... ALGORITHM=COPY (old non in-place alter) it will create a new temporary table, and in the final step replace the original table with it like this:
t - original table, tt - new table that is altered
1) rename t to #sql2-xxx (SE API)
2) rename tt from #sql-xxx to t (SE API)
3) rename tt frm file from #sql-xxx.frm to t.frm

If the crash happens between 1 and 2 then it will look like even after recovery:
#sql-79d0_1.frm
#sql-79d0_1.ibd
#sql2-79d0-1.ibd
t1.frm
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
and the innodb internal data dictionary:
mysql> select name from information_schema.innodb_sys_tables where name like 'te
st/%';
+-------------------+
| name              |
+-------------------+
| test/#sql-79d0_1  |
| test/#sql2-79d0-1 |
+-------------------+

So even without partitions the state will be a non-usable table.

If the crash occurs during 2 on a partitioned table, it will cause the internal data dictionary in an inconsistent state where some partitions have the correct name and some will have the #sql-xxx temporary name.