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: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
Version: | 5.7.10 | OS: | CentOS (7) |
Assigned to: | CPU Architecture: | Any |
[28 Jan 2016 7:02]
Shahriyar Rzayev
[28 Jan 2016 8:07]
Shahriyar Rzayev
Changed Severity to S1
[28 Jan 2016 9:16]
MySQL Verification Team
Hello Shahriyar, Thank you for the report. Verified as described with 5.7.10. Thanks, Umesh
[4 Feb 2016 15:03]
Kevin Lewis
[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`.
[4 Feb 2016 15:16]
Kevin Lewis
Posted by developer: This bug will be accomplished in 5.8 by making all DDL crash resistant (Bug#80183)
[4 Feb 2016 15:21]
Kevin Lewis
Marking as duplicate of Bug#80183