The magical abandoned .ibd files after crash recovery with MySQL 5.7.10

By | February 4, 2016

If you are running ‘OPTIMIZE TABLE’ or altering ‘TABLESPACE’ just be careful not to kill MySQL service while running these commands 🙂
Here is a test case:
We have a table:

CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2427738 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

After a while we want to ‘OPTIMIZE’ this table:

mysql> optimize table sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Uups 🙂 MySQL process was killed/crashed.

[root@ps-5 mysql]# ls -l dbtest/
total 1092792
-rw-r-----. 1 mysql mysql 65 фев 4 03:43 db.opt
-rw-r-----. 1 mysql mysql 8632 фев 4 03:46 sbtest1.frm
-rw-r-----. 1 mysql mysql 784334848 фев 4 03:53 sbtest1.ibd
-rw-r-----. 1 mysql mysql 8632 фев 4 03:43 #sql-21ee_3.frm
-rw-r-----. 1 mysql mysql 8632 фев 4 03:53 #sql-3037_2.frm
-rw-r-----. 1 mysql mysql 201326592 фев 4 03:43 #sql-ib41-423909887.ibd
-rw-r-----. 1 mysql mysql 159383552 фев 4 03:53 #sql-ib47-3672662344.ibd

As a reasult for each crash we will have #sql-ib47-3672662344.ibd like files(tablespaces).

Another case if you try, to alter table’s tablespace.
Here is a table in general tablespace, which we want to copy back to innodb_file_per_table:

CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) /*!50100 TABLESPACE `ts6` */ ENGINE=InnoDB AUTO_INCREMENT=808237 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

alter table sbtest1 tablespace=innodb_file_per_table; -> of course just run kill -9 for with MySQL PID.

As a result for each crash again magical ‘.ibd’ files.

[root@mysql-57 mysql]# du -hs dbtest/*
4,0K dbtest/db.opt
12K dbtest/sbtest1.frm
220M dbtest/#sql-ib97-1735019704.ibd
220M dbtest/#sql-ib97-2605617507.ibd
220M dbtest/#sql-ib97-2732989978.ibd
220M dbtest/#sql-ib97-4027484021.ibd

The weird thing here is that, it is impossible to drop/delete or something similar from MySQL side on this abandoned ‘tablespace’ files.
They are all searchable from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES:

mysql> select * from information_schema.innodb_sys_tablespaces where name like '%dbtest%';
dbtest/#sql-ib41-423909887 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 201326592 | 201326592 |
dbtest/sbtest1 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 784334848 | 758312960 |
dbtest/#sql-ib47-3672662344 | 33 | Barracuda | Dynamic | 16384 | 0 | Single | 4096 | 159383552 | 159338496 |

The only option is to remove manually, but as a result you will get a warnings about missing tablespace files in error log after each start.

See Related BUG reports:

http://bugs.mysql.com/bug.php?id=80263
http://bugs.mysql.com/bug.php?id=80181

One thought on “The magical abandoned .ibd files after crash recovery with MySQL 5.7.10

  1. Aleksandr Kuzminsky

    Well, that’s not the only option.
    You can actually clean up the dictionary:

    1. Create empty temporary database. Let it be tmp1234
    2. Move all tables from the original database to tmp1234
    3. Drop the original database (it’s empty by now, all tables are in tmp1234)
    4. Create the original database again
    5. Move all tables from the temporary database to the original one.
    6. Drop the empty temporary database.

    I blogged a while ago about it (https://twindb.com/resolving-error-1050-42s01-at-line-1-table-already-exists/ )

    A dangling record in the dictionary causes almost no trouble since MySQL 5.6. Then if InnoDB ever needs to create a temporary table it generates a unique name.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *