Bug #77830 | "ALGORITHM=INPLACE is not supported" no error with tablespace alter | ||
---|---|---|---|
Submitted: | 24 Jul 2015 13:38 | Modified: | 4 Feb 2016 14:55 |
Reporter: | Shahriyar Rzayev | Email Updates: | |
Status: | Won't fix | Impact on me: | |
Category: | MySQL Server: DDL | Severity: | S1 (Critical) |
Version: | 5.7.7-rc | OS: | CentOS (7) |
Assigned to: | CPU Architecture: | Any |
[24 Jul 2015 13:38]
Shahriyar Rzayev
[3 Sep 2015 5:27]
Roel Van de Paar
What I think Shahriyar is trying to say here is that inplace != (cannot be) table rebuild. i.e. they are mutually exclusive. Makes sense to me. http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
[3 Sep 2015 6:27]
Shahriyar Rzayev
Dear @Roel, Thank you for simple clarification :)
[29 Jan 2016 16:39]
MySQL Verification Team
Hi Shahriyar, Thank you for your bug report. I agree with you and Roel on this point. There should be definitely an error with ALGORITHM = INPLACE. Verified as reported.
[4 Feb 2016 14:51]
Kevin Lewis
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 * from information_schema.innodb_sys_tables where name like '%#sql%'; +----------+----------------------------+------+--------+-------+------------- +------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------------+------+--------+-------+------------- +------------+---------------+------------+ | 289 | test/#sql-ib288-3493953630 | 161 | 6 | 24 | Barracuda | Dynamic | 0 | General | +----------+----------------------------+------+--------+-------+------------- +------------+---------------+------------+ 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.
[4 Feb 2016 14:55]
Kevin Lewis
Sorry, the previous post was intended for Bug#80130. The following comment was posted internally but needs to be posted here. [3 Feb 2016 14:48] Kevin Lewis (KLEWIS2) There are a number of ALTER TABLE statements that can be done via ALGORITHM=COPY and ALGORITHM=INPLACE. he default is INPLACE if not specified. For example; mysql> alter table t1 TABLESPACE=innodb_file_per_table; Query OK, 0 rows affected (5.57 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 TABLESPACE=innodb_file_per_table, ALGORITHM=inplace; Query OK, 0 rows affected (5.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 TABLESPACE=innodb_file_per_table, ALGORITHM=copy; Query OK, 131072 rows affected (54.23 sec) Records: 131072 Duplicates: 0 Warnings: 0 You can see that the default algorithm has the same time as ALGORITHM=inplace and that the ALGORITHM=copy is a lot slower since it is copying each record through the SQL layer. But it is important to note that even though ALGORITHM=inplace, there is an new table created by InnoDB and all records are copied from the old table to the new one, just like ALGORITHM=copy, except not using the SQL layer to do it. The same thing happens with alter table t1 ROW_FORMAT=Dynamic; A new file is created and all records are copied to it. The only difference is whether InnoDB does it (faster) or the SQL layer does it (slower). I am closing this bug request since this ability of InnoDB to internally copy a table as part of ALGORITHM=inplace has been around for a long time. One may argue that if the resulting table is the same schema as the the starting schema then it should not do anything. That is what happens when you do; ALTER TABLE t1; But when you do ALTER TABLE t1 ENGINE=InnoDB; The file is intentionally rebuilt, just like OPTIMIZE TABLE t1; So there is a precedent in doing the ALTER TABLE even if nothing structurally is changing. This is an alternative way to rebuild a table which optimizes it and consolidates space.
[4 Feb 2016 14:58]
Kevin Lewis
Wrong number! The comment at [4 Feb 14:51] Kevin Lewis was intended for Bug#80183.
[5 Feb 2016 7:58]
Roel Van de Paar
Kevin, thanks, so - not fully following all details - could the manual not be improved here?