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:

$ cat storage/innobase/handler/ha_innodb.cc
extern "C" UNIV_INTERN
        fd = mysql_tmpfile("ib");


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():

/** Create a temporary tablename like "#sql-ibtid-inc where
  tid = the Table ID
  inc = a randomly initialized number that is incremented for each file
The table ID is a 64 bit integer, can use up to 20 digits, and is
initialized at bootstrap. The second number is 32 bits, can use up to 10
digits, and is initialized at startup to a randomly distributed number.
It is hoped that the combination of these two numbers will provide a
reasonably unique temporary file name.
        mem_heap_t*     heap,
        const char*     dbtab,
        table_id_t      id)

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

mysql> select * from INNODB_SYS_TABLES WHERE NAME like 'test/#sql%'\G
*************************** 1. row ***************************
     TABLE_ID: 67
         NAME: test/#sql-ib66-2731572493
         FLAG: 1
       N_COLS: 6
        SPACE: 56
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
1 row in set (0.00 sec)

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