MySQL Bugs: #72594: InnoDB in-place ALTER failures block future ALTERs

Bug #72594	InnoDB in-place ALTER failures block future ALTERs
Submitted:	9 May 2014 16:59	Modified:	16 Jun 2014 5:22
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	alter, in-place, innodb

Description:
When an InnoDB in-place ALTER fails, it leaves behind both a poorly named temporary file (e.g. #sql-ib20 where 20 represents the table ID of the table being altered) and metadata about that temporary table in its data dictionary. While it is possible for the DBA to clean up the leftover temporary file, it is not readily possible for the data dictionary to be cleaned up. This makes future in-place ALTERs of the same table impossible because the temporary table is named only with the table ID.

A workaround exists to use ALGORITHM=COPY to ALTER the table, thus causing a new table to be created and the data copied, and that new table will get a new table ID thus not making this a permanent problem.

How to repeat:
Crash an in-place ALTER after the temporary table has been created but before the in-place operation has been committed. I am providing a patch to add an appropriate DBUG_EXECUTE_IF/DBUG_SUICIDE to do so, but of course it would happen naturally in production environments.

See attached patch file to add an appropriate crash point, and test case.

Suggested fix:
This fix should be in at least two parts:

1. Clean up any leftover temporary tables.

2. Name temporary tables with something slightly more unique, such as perhaps "#sql-ib<transaction_id>" -- or use the same name that MySQL uses for the .frm file.

Patch to add appropriate crash point.

Attachment: mysql_bug72594_crashpoint.txt (text/plain), 498 bytes.

Test case exercising the problem.

Attachment: mysql_bug72594_test.txt (text/plain), 798 bytes.

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > CREATE DATABASE XY;
Query OK, 1 row affected (0.05 sec)

mysql 5.6 > USE XY
Database changed
mysql 5.6 > SET GLOBAL innodb_file_format='Barracuda';
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > SET GLOBAL innodb_file_per_table=1;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 >
mysql 5.6 > CREATE TABLE `t` (
    ->   `a` INT NOT NULL,
    ->   PRIMARY KEY (`a`)
    -> ) ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected (0.36 sec)

mysql 5.6 > SET DEBUG= 'd,crash_innodb_before_commit_inplace_alter';
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > ALTER TABLE `t` ADD COLUMN `b` INT NOT NULL;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 5.6 > exit
Bye

C:\dbs>net start mysqld56
The MySQLD56 service is starting......
The MySQLD56 service was started successfully.

C:\dbs>56

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE XY
Database changed
mysql 5.6 > SELECT name FROM information_schema.innodb_sys_tables WHERE name LIKE 'test/#sql%';
Empty set (0.44 sec)

mysql 5.6 > ALTER TABLE `t` ADD COLUMN `b` INT NOT NULL;
ERROR 1050 (42S01): Table 'xy/#sql-ib4547' already exists
mysql 5.6 > DROP TABLE `t`;
Query OK, 0 rows affected (0.17 sec)

Fixed as of the upcoming 5.6.20, 5.7.5 release, and here's the changelog entry:

 A failed in-place "ALTER TABLE" operation would leave behind non-unique
temporary file names in the data dictionary preventing future "ALTER
TABLE" operations on the same table due to temporary file name conflicts.
To avoid this problem, temporary file names are made unique by appending a
static global number that is initialized to a random distributed 32-bit
number using "ut_time()" and "ut_crc32()". The number is then incremented
atomically for each assigned temporary file name. Previously, temporary
files were named using the format "#sql-ibtid", where "tid" is the table
ID. Temporary files are now named using the format "#sql-ibtid-inc", where
"tid" is the table ID and "inc" is the incremented number. 

Thank you for the bug report.

Correction: The bug is fixed as of MySQL 5.6.21 and MySQL 5.7.5.

$ bzr log -n0 -r 5979
------------------------------------------------------------
revno: 5979
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Mon 2014-06-09 10:04:33 +0530
message:
  Bug #18734396	INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS
  
  This patch is adding more uniqueness to the temporary file name. It
  creates a temporary tablename like
  "#sql-ibtid-inc" where 
           tid = the table ID
           inc = static global number that is initialized to a random
  distributed 32-bit number using ut_time() and ut_crc32().It is then 
  incremented atomically for each temporary file name assigned.

$ bzr log -n0 -r 5980
------------------------------------------------------------
revno: 5980
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Mon 2014-06-09 12:41:22 +0530
message:
  Bug #18734396   INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS
  	- Reverting the patch due to test case faiure.

$ bzr log -n0 -r 5993
------------------------------------------------------------
revno: 5993
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Fri 2014-06-13 12:25:56 +0530
message:
  Bug #18734396	INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS
  
  Analysis:
  	When an InnoDB in-place ALTER fails, it leaves behind both a
  temporary filename like "#sql-ibtid" where tid represents the table ID of
  the table being altered) in its data dictionary. This makes future in-place
  ALTERs of the same table impossible because the temporary table is named
  only with the table ID.
  
  Solution:
  	This patch is adding more uniqueness to the temporary file name. It
  creates a temporary tablename like
  "#sql-ibtid-inc" where 
           tid = the table ID
           inc = static global number that is initialized to a random
  distributed 32-bit number using ut_time() and ut_crc32().It is then 
  incremented atomically for each temporary file name assigned.
  
  	rb5580 Approved by Kevin

$ bzr log -n0 -r 6006
------------------------------------------------------------
revno: 6006
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: t-5.6
timestamp: Thu 2014-06-19 20:41:09 +0530
message:
  Bug#18734396	INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS
  	Reverting the patch due to failure in trunk.

revno: 6109
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2014-08-06 15:34:47 +0530
message:
  Bug #18734396 INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS
  
  Analysis:
        When an InnoDB in-place ALTER fails, it leaves behind both a
  temporary filename like "#sql-ibtid" where tid represents the table ID of
  the table being altered) in its data dictionary. This makes future in-place
  ALTERs of the same table impossible because the temporary table is named
  only with the table ID.
  
  Solution:
        This patch is adding more uniqueness to the temporary file name. It
  creates a temporary tablename like
  "#sql-ibtid-inc" where 
           tid = the table ID
           inc = static global number that is initialized to a random
  distributed 32-bit number using ut_time() and ut_crc32().It is then 
  incremented atomically for each temporary file name assigned.
  
        rb5580 Approved by Kevin, Marko