MySQL Bugs: #80181: Abandoned .ibd files after each crash recovery,killing while altering tablespace

Bug #80181	Abandoned .ibd files after each crash recovery,killing while altering tablespace
Submitted:	28 Jan 2016 7:02	Modified:	4 Feb 2016 15:21
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.7.10	OS:	CentOS (7)
Assigned to:		CPU Architecture:	Any

Description:
Hi dear experts,
Here is the overall picture of abandoned files after each crash recovery:

[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

Here is a reproduce steps:

create tablespace ts6 add datafile '/var/lib/mysql_tablespaces/ts6.ibd' engine=innodb;
alter table sbtest1 tablespace ts6;
alter table sbtest1 compression='lz4';
optimize table sbtest1;

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create 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'
1 row in set (0,00 sec)

Kill MySQL process (kill -9) while doing:

alter table sbtest1 tablespace=innodb_file_per_table;

How to repeat:
See description

Suggested fix:
Those files should be removed after each crash recovery.

Changed Severity to S1

Hello Shahriyar,

Thank you for the report.
Verified as described with 5.7.10.

Thanks,
Umesh

[3 Feb 2016 11:55] Kevin Lewis (KLEWIS2)

The problem of orphaned intermediate tables that remain after a crashed alter
table has been around for a long time.  This problem is not unique to
file-per-table either.  These orphaned tables can occur inside the system
tablespace or in general shared tablespaces also.  It just depends on the
target location of the ALTER TABLE statement.

In earlier versions, the names of these tables and related file-per-table
tablespace files were much simpler and shorter.  This caused problems when
they were not deleted manually because they often enough prevented other
temporary files with the same generated name to be created.  So the InnoDB
team used longer and more random names to keep these orphans from preventing
future operations.

Of course, the best solution is to make ALTER TABLE fully atomic so that
after a crash, these orphaned tables and related files are fully cleaned up.
This effort is non-trivial and is being worked on currently for a future
release. But for 5.7 and earlier releases, the user must manually delete
these intermediate tables and associated files (if the table was
file-per-table) after a crash.

The way to do this is to use DROP TABLE.  But before you can do that, you
need to have an FRM file with the table name so that the server can recognize
it as a table.  These intermediate tables are created by InnoDB during
in-place ALTER TABLE.  They do not have an FRM associated with them.  So make
a copy of the FRM file of the table you were trying to ALTER and give it the
name of the intermediate table.  You can see that name by querying
information_schema.innodb_sys_tables.  It will be a name like
'#sql-ib97-1735019704'.

Once you have created '#sql-ib97-1735019704.frm' in the database directory
under the datadir, you can drop it with DROP TABLE.  You do not need to
restart the server.  But you do need to use the prefix `#mysql50#` before the
table name and you need to use backticks:

DROP TABLE `#mysql50##sql-ib97-1735019704`;

The `#mysql50#` prefix tells the MySQL server not to convert `#` to `@0023`.

Posted by developer:
 
This bug will be accomplished in 5.8 by making all DDL crash resistant (Bug#80183)

Marking as duplicate of Bug#80183