1. Temporary Table Naming Scheme In 5.6 And Before

Temporary Table Naming Scheme In 5.6 And Before

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

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

In MySQL 5.6 a new temporary table naming scheme was introduced. One of the barely visible, yet important improvements.

Temporary table names got more random and shouldn’t ever be reused.

Some time ago, I wrote a post about how to remove orphaned records from InnoDB dictionary. The orphaned records in the dictionary are those left after an unsuccessful ALTER TABLE. The orphaned records themselves are harmless, the problem was that MySQL reused them.

The first time the ALTER TABLE might have crashed was because of power loss or because a DBA killed it, but the next time you want to run the ALTER TABLE, MySQL chooses the same temporary table name. And now the command fails because the temporary table with the same name already exists in the dictionary.

Before 5.6, InnoDB relied on a MySQL server layer to produce a temporary file name:

MySQL’s mysql_tmpfile() generates something like #sql-ib712, which is far from unique, and the worst part is the second call returns the same result.

If you want something done properly, do it yourself. This is what InnoDB does. In 5.6, it generates the temporary table name by itself in function dict_mem_create_temporary_tablename():

It provides a unique file name.
Although the orphaned record still remains in the dictionary, the second time ALTER TABLE succeeds.

By the way, 66 is the table id of the table’s old version. Which can be used for data recovery in case of wrong ALTER TABLE (if the column is dropped or something like that).

Have a question? Ask the experts!

Previous Post Next Post