How Crash Recovery Works with Fast Index Creation

No data is lost if the server crashes while an ALTER TABLE statement is executing. Recovery, however, is different for clustered indexes and secondary indexes.

If the server crashes while creating a secondary index, upon recovery, InnoDB drops any partially created indexes. You must re-run the ALTER TABLE or CREATE INDEX statement.

When a crash occurs during the creation of a clustered index, recovery is somewhat more complicated, because the data in the table must be copied to an entirely new clustered index. Remember that all InnoDB tables are stored as clustered indexes. In the following discussion, we use the word table and clustered index interchangeably.

The InnoDB storage engine creates the new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to this temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is then renamed with the name of the original table, and the original table is then dropped from the database.

If a system crash occurs while creating a new clustered index, no data is lost, but you must complete the recovery process using the temporary tables that exist during the process. Since it is rare to re-create a clustered index or re-define primary keys on large tables, and even rarer to encounter a system crashes during this operation, this manual does not provide information on recovering from this scenario. Instead, please see the InnoDB web site: http://www.innodb.com/support/tips.

Copyright © 2010-2023 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout