Fast, Reliable, Proven transactional storage for MySQL

Recovering After a System Crash During ALTER TABLE

The following information is presented to help users of the InnoDB Plugin recover in case of a system crash that occurs during execution of an ALTER TABLE command to rebuild the clustered (primary key) index of a table. Please read Chapter 2 of the User’s Guide for the InnoDB Plugin for background on the process of creating indexes, and specifically section 2.5 on Crash Recovery.

No special recovery is needed if the server crashes while creating a secondary index, since the InnoDB Plugin will drop any partially created secondary indexes when the system is restarted. All you need to do to create the index is to re-run the ALTER TABLE or CREATE INDEX command.

Although there will not be a loss of data, recovery from a crash that occurs during the creation of a new clustered index will require manual intervention. As described in the User’s Guide, the InnoDB Plugin uses temporary tables to build a new clustered index. If a crash occurs during the process of renaming these temporary tables, you will need to deal with the temporary tables.

Processing CREATE INDEX

Consider an example. If you use the ALTER TABLE command to redefine the primary key of table T (or otherwise cause the clustered index to be recreated, for example by compressing an uncompressed table), the InnoDB Plugin will perform the following sequence of steps:

  1. Lock table T in exclusive mode.
  2. Create a new temporary table T1, with the new index structure.
  3. Copy data from table T to temporary table T1.
  4. Rename the original table T to temporary table T2.
  5. Rename table T1 (the new table) to T (the original table name).
  6. Drop table T2 (the original table, with the old index structure).

Recovery Scenarios

Since the InnoDB plugin does not drop the temporary table containing the original data (T2 above) until the very last step, there will never be a loss of data. Furthermore, until the original table is dropped, it is always safe to revert back to that table, with its original index structure. However, if the process of copying the data (step 3) is complete, you can chose to use the new table by appropriately renaming or dropping the temporary table(s).

Until the last step is completed, one or both of the temporary tables may exist following a crash during ALTER TABLE. It is up to the user to complete the recovery process using these temporary tables. Following a crash during ALTER TABLE, you should first determine which steps of the above process have been completed. There are the following possibilities and remedial steps.

Case A: Processing was interrupted after step 2, and during step 3 above. Some, but not all of the data from original table T has been copied to temporary table T1.

In this case, table T will still exist and may be used by the application. You should drop temporary table T1.

Case B: Processing was interrupted after step 3 above completed, but before step 4. All of the original data has been copied to temporary table T1, but the original table T still exists by that name. There will be no temporary table named T2.

Because step 4 has not been completed, you cannot tell that all the data has been copied. Therefore, this is equivalent to case A, and you should revert to the original table T, and drop temporary table T1.

Case C: Processing was interrupted after step 4 above, but before step 5. All of the data from original table T has been copied, and table T has now been renamed to temporary table T2. The new table is complete, but is still named temporary table T1.

In this case, as with case B, you may choose to use the original table T, by renaming temporary table T2 to T, and dropping temporary table T1. Or, you may decide to use the new table, renaming temporary table T1 to T, and dropping temporary table T2.

Case D: Processing was interrupted after step 5 above, but before step 6. Table T now has the new structure, and the original table is present as temporary table T2.

In this case, you can simply complete the processing of the ALTER TABLE command by dropping temporary table T2, and the application can use the newly-indexed table. T. Or, if you wish, you can go back to using the original index structure, dropping table T, and renaming temporary table T2 (which is the original table) back to T.

The InnoDB and MySQL Data Dictionaries

The recovery process outlined above is a bit tricky, because there are two data dictionaries involved: the SYS_TABLES table in the InnoDB system tablespace (which is not directly accessible from MySQL), and the .frm files in the MySQL database directories.

The temporary tables T1 and T2 in the example above will be created within the InnoDB data dictionary only, and not as MySQL .frm files. Furthermore, the creation or deletion of .frm files is not transactional, and InnoDB crash recovery does not cover .frm files. If the operating system crashes immediately after the InnoDB transaction has been committed, it is possible that the .frm file for the modified table will be lost, in which case the internal InnoDB data dictionary and that of MySQL will be out of sync.

After a crash that occurs while a new clustered index is being created, the table may not appear to exist, and the following error will be returned: ERROR 1146 (42S02): Table ‘test.T‘ doesn’t exist. This may be due to the file test/T.frm missing from the MySQL data directory, but it can also be due to InnoDB.

To diagnose the error condition, first examine the error log to see if InnoDB has reported some error. For further analysis, have InnoDB dump the internal data dictionary, as follows:

CREATE TABLE innodb_table_monitor (a INT) ENGINE=InnoDB;

DROP TABLE innodb_table_monitor;

Between the two commands, monitor the MySQL error log for up to one minute, until a section labeled INNODB TABLE MONITOR OUTPUT appears. In the dumped table names, the table name suffixes will be shown as something like @00231 or @00232.

The following sections describe how to recover under two scenarios: and a missing .frm file, and mis-match in tablespace IDs.

Recovering a Missing .frm File

Before creating an index, the MySQL server writes the data dictionary information of the table in a temporary file that is named like #sql-110b_1.frm. The file should have a recent timestamp. If the file T.frm is missing, you may try renaming it from the temporary file.

The MySQL server cannot drop a table unless there is a .frm file for it. You can create the file by creating a table under a different name and copying the file, say, to T@00231.frm to “create” the table T.

Recovering from a Tablespace ID Mismatch

If InnoDB reports a tablespace ID mismatch in the error log for table T, the situation should be like this:

  • Table T in SYS_TABLES points to the table definition as it was before creating the index.
  • Table T@00231 in SYS_TABLES points to table definition as it is after creating the index.
  • There is no T@00232 in SYS_TABLES.
  • The file T.frm points to the table definition as it was before creating the index.
  • The file T@00232.ibd points to the table definition as it was before creating the index. This file may or may not exist.
  • The file T@00231.ibd may or may not exist.
  • The file T.ibd points to the table definition as it was after creating the index.

    • To correct this situation, do the following:

    1. Perform a “slow” shutdown of the MySQL server (SET GLOBAL innodb_fast_shutdown=0 followed by a clean shutdown).
    2. Back up the MySQL data directory.
    3. If the file T@00232.ibd exists, restore the situation as it was before creating the table:
      1. Remove the files T@00231.ibd (if it exists) and T.ibd.
      2. Rename the file T@00232.ibd to T.ibd.
      3. Copy the file T.frm to T@00231.frm.
      4. Start the MySQL server.
      5. DROP TABLE `T1`;
      6. There may be an orphan file #sql-110b_1.frm that you may want to delete. It could contain the table definition after creating the index.

      If the file T@00232.ibd does not exist, try to restore the situation as it was after creating the table:

      1. Look for an orphan file like #sql-110b_1.frm with a recent timestamp. It should contain the table definition after creating the index. Rename the file to T@00231.frm.
      2. Rename the file T.ibd to T@00231.ibd.
      3. Start the MySQL server.
      4. DROP TABLE T;
      5. RENAME TABLE `T#1` TO T;
    4. Ensure with SHOW CREATE TABLE T and SELECT * FROM T LIMIT 10 that the table definition is correct. Compare the output of SHOW CREATE TABLE with the innodb_table_monitor dump of the table.