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 …

MySQL 5.6.23 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 3 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1j to version 1.0.1k. Issues fixed in the new version are described at http://www.openssl.org/news/vulnerabilities.html.
2. Functionality Changed: Support for the SSL 2.0 and SSL 3.0 protocols has been disabled because they provide weak encryption. (Bug #19820550, Bug #19921150)
3. Functionality Changed: yaSSL was upgraded to version …

MySQL 5.6.22 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there is 1 “Security Note”, 2 “Functionality Changed”, and 5 “Compilation Notes”, all benign, but let me address them:

1. Security Note: The linked OpenSSL library for the MySQL Commercial Server has been updated from version 1.0.1h to version 1.0.1j. Issues fixed in the new version are described at http://www.openssl.org/news/vulnerabilities.html.
2. Functionality Changed: Replication: The variable binlogging_impossible_mode has been renamed binlog_error_action. binlogging_impossible_mode is now deprecated. (Bug #19507567)
3. Functionality Changed: …

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 …

To recover a dropped or corrupt table with Percona Data Recovery Tool for InnoDB you need two things: media with records(ibdata1, *.ibd, disk image, etc.) and a table structure. Indeed, there is no information about the table structure in an InnoDB page. Normally we either recover the structure from .frm files or take it from some old backup.

A new tool sys_parser can recover the table structure from InnoDB dictionary.

Why do we need a new tool anyway? It is absolutely critical to have an accurate table definition to ensure a successful recovery. Even an unnoticeable difference like NULL or NOT NULL can shift all values by a byte and thus will spoil the result. That’s why I prefer the structure from .frm …