When a table gets dropped, MySQL removes the respective .frm file. This post explains how to recover the table structure if the table was dropped.

You need the table structure to recover a dropped table from the InnoDB tablespace. The B+tree structure of the InnoDB index doesn’t contain any information about field types. MySQL needs to know that in order to access records of the InnoDB table. Normally, MySQL gets the table structure from the .frm file. But when MySQL drops a table the respective frm file removed too.

Fortunately, there’s one more place where MySQL keeps the table structure. It’s the InnoDB dictionary.

The InnoDB dictionary is a set of tables where InnoDB keeps information about the tables. I reviewed them in detail in a separate InnoDB Dictionary post earlier. After the DROP, InnoDB deletes records related to the dropped table from …

Why Does InnoDB Need A Dictionary

An InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer to tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name and index_id.

The dictionary tables are normal InnoDB tables, but they’re not visible to a user. However, some versions of MySQL provide read-only access to the dictionary in information_schema database.

The dictionary is stored in ibdata1. Root page of SYS_TABLES, for example, has id 8, so it’s the eighth page from the beginning of ibdata1.

The dictionary pages are in REDUNDANT format even if you use MySQL 5.6. I hope to write more about record formats in future posts. For now, it’s enough to mention that REDUNDANT is the oldest record format. It was available since 4.0 …

On 5 November, I’ll be speaking at #SFMySQL Meetup about Data Recovery Software for MySQL

Add Slipped & DROP’d your TABLE? Recover w/TwinDB’s Undrop for InnoDB toolkit to your calendar.

There will be a demo and if you want to try to undrop a table yourself bring in a laptop with Linux.

Download the latest revision of the TwinDB Data Recovery Toolkit from LaunchPad.

Internet connection isn’t necessary, but make sure you install dependencies: gcc, make, flex, bison.

The post Presenting TwinDB Data Recovery …

Benchmarking is a popular topic. People love drawing graphs as much as watching how X is 10% faster than Y; there must be something special in measurements.

For a DBA, however, more tangible improvements come from less popular area of database maintenance. While MariaDB spreads FUD around InnoDB (nonetheless still uses it) I have to admit InnoDB gets more friendly to DBAs.

In MySQL 5.6 new temporary table naming scheme was introduced – one of improvements. Invisible, yet important.

Temporary table names became more random and should not ever be reused.

Some time ago I wrote a post about how to remove …

When ALTER TABLE crashes MySQL server it leaves orphaned records in InnoDB dictionary. It is annoying because next time you run the same ALTER TABLE query it will fail with error:

ERROR 1050 (42S01) at line 1: Table 'sakila/#sql-ib712' already exists

The post explains why it happens and how to fix it.

When you run ALTER table InnoDB follows the plan:

1. Block the original table
2. Create an empty temporary table with the new structure. The name of the new table is something like #sql-ib712.
3. Copy all records from the original table to the temporary one
4. Swap the temporary and original tables
5. Unblock the original table

The temporary table is a normal InnoDB table except it’s not visible to a user. InnoDB creates a record in the dictionary for the temporary table as for any other table.

If MySQL crashes in the middle of the …

When a table gets dropped MySQL removes respective .frm file. This post explain how to recover table structure if the table was dropped.

You need the table structure to recover a dropped table from InnoDB tablespace. The B+tree structure of InnoDB index doesn’t contain any information about field types. MySQL needs to know that in order to access records of InnoDB table. Normally MySQL gets the table structure from .frm file. But when MySQL drops a table the respective frm file removed too.

Fortunately there is one more place where MySQL keeps the tables structure . It is the InnoDB dictionary.

InnoDB dictionary is a set of tables where InnoDB keeps some information about the tables. I reviewed them in details is a separate InnoDB Dictionary post earlier. After the DROP InnoDB deletes records related to the dropped table from the dictionary. So we need …

Why do we need to recover InnoDB dictionary

c_parser is a tool from TwinDB recovery toolkit that can read InnoDB page and fetch records out of it. Although it can scan any stream of bytes recovery quality is higher when you feed c_parser with pages that belong to the PRIMARY index of the table. All InnoDB indexes have their identifiers a.k.a. index_id. The InnoDB dictionary stores correspondence between table name and index_id. That would be reason number one.

Another reason – it is possible to recover table structure from the InnoDB dictionary. When a table is dropped MySQL deletes respective .frm file. If you had neither backups nor table schema it becomes quite a challenge to recover the table structure. This topic however deserves a separate post which I write some other day.

Let’s assume you’re convinced …

Why Does InnoDB Need Dictionary

InnoDB dictionary is a set of internal tables InnoDB uses to maintain various information about user tables. It serves as API between a human and the database. While the humans refer tables by their names, InnoDB works with integer identifiers. The dictionary stores correspondence between table name and index_id.

The dictionary tables are normal InnoDB tables, but they’re not visible for a user. However some versions of MySQL provide read-only access to the dictionary in information_schema database.

The dictionary is stored in ibdata1. Root page of SYS_TABLES, for example, has id 8, so it’s eighth page from the beginning of ibdata1.

The dictionary pages are in REDUNDANT format even if you use MySQL 5.6. More about record formats I will write in future posts, I hope. For now it’s enough to mention REDUNDANT is the oldest record format. It was available since 4.0 and was the …