Recovery after DROP & CREATE

In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because  mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:

If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of table sakila/actor still resides in InnoDB dictionary although marked as deleted.

Knowing index_id, it’s easy to get the table back:

How does the following “CREATE TABLE” make the recovery any harder?

When a table gets dropped InnoDB deletes respective records from the dictionary – tables SYS_TABLES and SYS_INDEXES and others. Physically the records remain on their places, they’re just marked as deleted. That’s why it was possible to recover them with -D option.

When a user immediately creates the same table InnoDB adds the records to the dictionary. The size of the new records will be the same. Indeed the only variable field in SYS_* is NAME. Hence, InnoDB will put the new records into the same position in the pages. So, table_id and index_id of the dropped table gets overwritten.

If index_id is unknown there are several ways to learn it.

Grepping for a known string

In particular this example we know that PENELOPE GUINESS was in the table. We can concatenate neighbor strings and search them in the pages:

Earlier Aurimas explained how to deal with tables with binary only values

This method has drawbacks:

  • It assumes a lot of manual work and thus slow
  • There is no way to differentiate tables based on values only. For example, if you dropped table actor from db1 and db2 you’ll get two different index_id. Both are valid, but no general way to know  which index_id belongs to which database/table.
  • Sometime it’s not possible to find the index_id. Either because too many matches or there is no suitable string to grep etc.

Using information from UNDO segment

SYS_TABLES and SYS_INDEXES are normal InnoDB tables. They’re also ruled by MVCC.

When a record is deleted from SYS_TABLES  it is copied to the undo segment. A pointer to the old version is stored in the internal field DB_ROLL_PTR.

Unfortunately this pointer is lost when new record is inserted. But the actual old values remain in the undo segment for some time.

Let’s review some headers of undo slot:

We know that sakila/actor is field #0, its length is 12 bytes. So, if we ever meet byte sequence

the next eight bytes will be will be table_id.

The same approach is applicable for SYS_INDEXES. We can search for sequence

and next 8 bytes is index_id.

In revision 69 of Percona Data Recovery Tool for InnoDB two tools are added: s_tables and s_indexes. They scan a given pattern and output next 8 bytes – either table_id or index_id.
Let’s review an example:

Apparently 0-18 is PRIMARY index and 0-19 is index idx_actor_last_name.

It is not guaranteed though an undo slot will be flushed on disk. InnoDB pages are modified in memory first and only after some time InnoDB writes them permanently on disk. But the changes a transaction does are written to the redo log right before a successful response to COMMIT(or in a second if innodb_flush_trx_at_commit != 1).
In that case we can scan the redo log:

Now recovery of sakila/actor becomes trivial again as it was before the “CREATE TABLE”.

Share this post

Comments (3)

  • evren baskut

    ı wrongly delete tables and form new one 🙁 . ı only have ibdata1 and ib_logfiles0 and ib_logfiles1 ı want to turn back before ı delete the tables. Could you please help me ? ı am not specialist if you could could you send me step by step instruction to recover my data

    December 23, 2012 at 2:49 pm
  • lyxing

    My database’s all datafiles were deleted by someone.I copy ibdata and ibd files from /proc.I used s_tables command search table id.but i c