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:
None 
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
Description:
As documentation states:

"ALTER TABLE ... TABLESPACE operations always cause a full table rebuild, even if the TABLESPACE attribute has not changed from its previous value"
 
http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

So it will always rebuild table. 
There should be an error if we indicate ALGORITHM=INPLACE:

mysql> alter table t1 ALGORITHM=INPLACE, tablespace ts1;
Query OK, 0 rows affected (1 min 50.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 ALGORITHM=INPLACE, tablespace innodb_file_per_table;
Query OK, 0 rows affected (1 min 52.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 ALGORITHM=INPLACE, tablespace innodb_system;
Query OK, 0 rows affected (2 min 16.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to repeat:
Try to alter tablespace using ALGORITHM=INPLACE you will see no error/warning.

Suggested fix:
Rising an error:
For eg:
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change tablespace INPLACE.
[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?