How to get rid of orphaned tables in MySQL?

By | March 7, 2016

This post is about situation where comments in BUG reports are really valuable in term of learning and getting some internal view of going process.
Let’s take a look at -> http://bugs.mysql.com/bug.php?id=80183

The report is about how you can not drop general tablespace if it was a crash of MySQL instant while running alter statement.


mysql> create tablespace ts1 add datafile '/var/lib/mysql_tablespaces/ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Kill -9 process while altering:


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

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`)
) ENGINE=InnoDB AUTO_INCREMENT=808237 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

mysql> drop tablespace ts1;
ERROR 1529 (HY000): Failed to drop TABLESPACE ts1

As you see in the middle of alter process i killed the process and then can not drop the general tablespace file.

And here is the original explanation from developer Kevin Lewis (KLEWIS2)

The test crashes the server in the middle of an ALTER TABLE that moves
a table into a general tablespace. The recovery process restores the
table in its original location, but the temporary table is left as an
orphan table in the general tablespace.

You can see it inside a general tablespace this way:

mysql> select * from information_schema.innodb_sys_tables where name like
'%#sql%';
+----------+----------------------------+------+--------+-------+-------------
+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT
| ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+----------------------------+------+--------+-------+-------------
+------------+---------------+------------+
| 289 | test/#sql-ib288-3493953630 | 161 | 6 | 24 | Barracuda
| Dynamic | 0 | General |
+----------+----------------------------+------+--------+-------+-------------
+------------+---------------+------------+

mysql> select space, name, space_type from
information_schema.innodb_sys_tablespaces where name='ts1';
+-------+------+------------+
| SPACE | NAME | SPACE_TYPE |
+-------+------+------------+
| 24 | ts1 | General |
+-------+------+------------+

The FRM file is named differently. It will be something like this in
the database directory: #sql-1874_2.frm

This table is located in the destination general tablespace and was the
table that would have been renamed to sbtest1 had the ALTER TABLE succeeded.

DROP TABLE #sql-1874_2 does not work because the frm file and the table are
named differently. In order to drop this orphaned temporary table you need
to rename the FRM file to the same base name as the InnoDB table:
mv #sql-1874_2.frm #sql-ib288-3493953630.frm

Then you can drop this table using the `#mysql50#` prefix before the table
name
which keeps mysql from expanding `#` to `@0023`. NOTE: You must use the
backticks!

mysql> drop table `#mysql50##sql-ib288-3493953630`;
Query OK, 0 rows affected (0.02 sec)

mysql> drop tablespace ts1;
Query OK, 0 rows affected (0.00 sec)

[1 Feb 2016 22:11] Kevin Lewis (KLEWIS2)

The problem of orphaned temporary tables has been around a long time. The
process for deleting these tables has also been known since at least 5.0.

When a crash happens during an ALTER TABLE that is rebuilding the table using
a temporary table name, that temporary table will remain after recovery and
is called an ‘orphan temp table’.

Usually, these tables are file-per-table so they can be deleted to save
space. Of course the proper way to dispose of them is to use DROP TABLE
`#mysql50#{frm and ibd base file name}`; The `#mysql50#` prefix tells MySQL
server not to convert `#` characters to `@0023`.

These orphaned files can exist inside the system tablespace if that is where
the ALTER TABLE is doing it work.

MySQL 5.7 has added General tablespaces and they can also have these orphaned
tables. But unlike the system tablespace, general tablespaces can be
dropped… if they are empty. An orphaned temp table will keep it from being
dropped. And just like an orphaned table in the system tablespace, it can use
pages in the tablespace, wasting that space.

What a great explanation 🙂

And the clean thing is here is, this bug will be fixed in MySQL 5.8.

Leave a Reply

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