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 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 orphaned records from InnoDB dictionary. The orphaned records in the dictionary left after unsuccessful ALTER TABLE. The orphaned records themselves are harmless, the problem was MySQL reused them. First time the ALTER TABLE might have crashed because of power loss or because a DBA kills it, but 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 MySQL server layer to produce a temporary file name:

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

If you want something get done properly – do it yourself. So does InnoDB. In 5.6 it generates the temporary table name by itself in function dict_mem_create_temporary_tablename():

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

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

Have a question? Ask the experts!

Previous Post Next Post
  • I believe the 5.6 behavior changed to this in 5.6.21 with BUG #72594. Thanks to Jeremy Cole for reporting, and providing a test-case.