MySQL Bugs: #80183: ALTER TABLESPACE is not crash resistant

Bug #80183	ALTER TABLESPACE is not crash resistant
Submitted:	28 Jan 2016 8:03	Modified:	28 Jan 2016 9:30
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Verified	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:
Well situation for me is quite critical.
Please consider following situation:

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

How to repeat:
See description

Suggested fix:
Make it crash resistant :)

Hello Shahriyar,

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

Thanks,
Umesh

Posting comments from the internal bug system to the external. They were intended to be public but did not get posted.

[1 Feb 2016 21:49] Kevin Lewis (KLEWIS2)

This is not as critical as it was described because no data is lost.
However, the tablespace is left containing an orphaned temporary
table takes up space in the general tablespace and prevents that
tablespace from being dropped.
And the orphaned temporary table cannot easily be dropped.

But I will describe a workaround below that can be used to drop the
temporary table in that general tablespace which will recover those
pages used by it and will allow the general tablespace to be dropped.

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

In my next comment to this bug I will discuss what the bad behavior actually
is,
other situations that it affects, and what should be done about it.

[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.

So the problem of ALTER TABLE being not very crash resistant has been around
a long time. It is being fixed properly in a future release.

So what can be done for mysql-5.7?
1) Document the process of dropping orphaned temp tables, how to spot them,
etc.
2) Allow DROP TABLESPACE when its only contents are temp tables. This would
be done in InnoDB and would not affect the related FRM file. It would also
require some locking to prevent dropping a tablespace when an active temp
table is in the tablespace.

[3 Feb 2016 12:07] Kevin Lewis (KLEWIS2)

After some discussion internally, I am closing this bug as something we will
not fix in 5.7.

A future release will have fully crash resistant ALTER TABLE. It requires
atomic DDL which can be accomplished once the FRM files are replaced by a
transactional data dictionary.

The previous suggestion I made to 'Allow DROP TABLESPACE when its only
contents are temp tables' is not a good idea since InnoDB does not know
if the temp table is active or not.

This bug is being applied to 5.8 so that we make sure that DDL is atomic and recovers cleanly.

What are the implications for MEB?

Bug 80181 was made duplicate of this one
Bug 80263 was made duplicate of this one

What are the implications for MEB?

MEB ignores the temporary tables at the time of backup. Unless I am missing something I can’t think of implications for MEB.