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: | |
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 |
[9 May 2014 16:59]
Jeremy Cole
[9 May 2014 17:01]
Jeremy Cole
Patch to add appropriate crash point.
Attachment: mysql_bug72594_crashpoint.txt (text/plain), 498 bytes.
[9 May 2014 17:01]
Jeremy Cole
Test case exercising the problem.
Attachment: mysql_bug72594_test.txt (text/plain), 798 bytes.
[9 May 2014 18:01]
MySQL Verification Team
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)
[13 Jun 2014 19:51]
Daniel Price
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.
[6 Aug 2014 13:59]
Daniel Price
Correction: The bug is fixed as of MySQL 5.6.21 and MySQL 5.7.5.
[7 Aug 2014 5:43]
Laurynas Biveinis
$ 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.
[7 Aug 2014 5:43]
Laurynas Biveinis
$ 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.
[7 Aug 2014 5:52]
Laurynas Biveinis
$ 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
[7 Aug 2014 6:44]
Laurynas Biveinis
$ 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.
[25 Sep 2014 12:11]
Laurynas Biveinis
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