MySQL Bugs: #72997: "fast" ALTER TABLE CHANGE on enum column triggers full table rebuild.

Bug #72997	"fast" ALTER TABLE CHANGE on enum column triggers full table rebuild.
Submitted:	13 Jun 2014 9:57	Modified:	27 Feb 2015 16:15
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.17,5.7.4	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, enum, no-op change

Description:
Certain changes to tables via ALTER TABLE should not require the rebuild of the table and this is true of ENUM changes. However, on a recent change on a replication chain that's being upgraded to 5.6 from 5.5 so had a few different versions of MySQL running we noticed that this failed to work as expected.

The change on the 5.5.23 master and on 5.6.15 slaves went through fine.
On servers running 5.6.17 and 5.7.4 (yes this is a dev version but this is to show the issue seems to be related to new code) we see the table gets rebuilt.

We would expect the ALTER TABLE to be a no-op change (just a definition change) and this change in behaviour on a minor version upgrade is not expected.

Versions that worked: 5.5.16, 5.5.23, 5.6.15
Versions that did not work: 5.6.17, 5.7.4

How to repeat:
The following change was made on a 5.5.23 master.

root@my-5.5.23 [mydb]> alter table T1 change type type enum('Enum1','Enum2','Enum3','Enum4','Enum5','Enum6','Enum7','Enum8','Enum9','Enum10') not null default 'Enum1';

The table definition was:

*************************** 1. row ***************************
       Table: T1
Create Table: CREATE TABLE `T1` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `t_id` int(10) unsigned NOT NULL DEFAULT '0',
  `h_id` int(10) unsigned DEFAULT NULL,
  `last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `v` varchar(255) DEFAULT NULL,
  `info` text CHARACTER SET utf8 NOT NULL,
  `type` enum('Enum1','Enum2','Enum3','Enum4','Enum5','Enum6','Enum7','Enum8') NOT NULL DEFAULT 'Enum1',
  PRIMARY KEY (`t_id`,`id`),
  KEY `h_id` (`h_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
/*!50100 PARTITION BY RANGE ( t_id)
(PARTITION pNULL VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p000 VALUES LESS THAN (1) ENGINE = InnoDB,
 PARTITION p001 VALUES LESS THAN (5000000) ENGINE = InnoDB,
 PARTITION p002 VALUES LESS THAN (10000000) ENGINE = InnoDB,
 PARTITION p003 VALUES LESS THAN (15000000) ENGINE = InnoDB,
 PARTITION p004 VALUES LESS THAN (20000000) ENGINE = InnoDB,
 PARTITION p005 VALUES LESS THAN (25000000) ENGINE = InnoDB,
 PARTITION p006 VALUES LESS THAN (30000000) ENGINE = InnoDB,
...
 PARTITION p039 VALUES LESS THAN (195000000) ENGINE = InnoDB,
 PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

Suggested fix:
Ensure that the specific ALTER TABLE does not require a table rebuild.
The table in question was 200 GB so there's a huge difference in doing a "faster" ALTER TABLE and having to use tools like pt-online-schema-change to rebuild the whole table (which of course should not be necessary in this case).

So looks related to:  https://dev.mysql.com/doc/refman/5.6/en/alter-table.html

and the comment:

"As of MySQL 5.6.16, ALTER TABLE upgrades old temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY if necessary."

If this is the cause then the conditions under which the ALGORITHM=COPY are required are in my opinion wrong.

Setting as verified.  The fix for bug #17246318 leads to the table being rebuilt if it contains any old-style datetime columns.

This happens even if the ALTER being done is capable of using the inplace algorithm.  It leads to unexpectedly using copy algorithm if you don't explicitly specify the inplace algorithm (and let it fail).

While it can be seen as a good idea to bring tables up to current version format, it is a bad idea if you do not know when it will actually happen.

I filed http://bugs.mysql.com/bug.php?id=73008 for that.

You reference: Bug 17246318 : ALTER TABLE SHOULD NOT ALLOW CREATION OF TABLES WITH BOTH 5.5 AND 5.6 TEMPORALS so it's good to have the full description.

As a bug title that seems to make sense but you have put yourself in this corner by making the 5.5 timestamp to 5.6 timestamp(0) change "invisible", and the above bug report solves the wrong problem.

By fixing #17246318 you have made a behavioural change in MySQL 5.6 which has potentially rather dramatic consequences and leave the DBAs to "fix" this by _having_ to rebuild the table prior to making changes which should be fast, and ensuring this is done on all servers in a replication chain. (which may be affected by "simple", "known to be safe" no-op ENUM definition changes).

So yes, check ... for upgrade should tell you there's a problem but that's too late. The servers _are_ running 5.6 and have been upgraded (at some previous point in time), so for minor version upgrades it should not really be necessary to have to check the server for each minor version upgrade because of this.

Consequently #17246318 should not "force" an upgrade of the table. If you want to have this behaviour, make it configurable by a new configuration parameter which by default is OFF, or if you insist set it to ON by default, but then I'll go and configure all my servers to have this setting OFF anyway to avoid problems such as this.

Thanks for filing http://bugs.mysql.com/bug.php?id=73008. I've been arguing for some time that mysqlcheck , and mysql_upgrade should be more explicit about things they do (like rebuilding a table or not) and the check ... for upgrade is more of the same. Having that extra visibility _is_ important and it's been missing.

So the new 5.6 "precision" timestamp and datetime formats are good, but their implementation and the consequences of this have had some rather unfortunate side effects.

That said, and given MySQL 5.6 can have 5.5 and 5.6 timestamp/datetime formats I think you need to live with this, accept it, and work with it in a way which is transparent to the DBA who unless he looks very carefully can't see the difference and therefore should expect the server to behave identically with one format or the other.

Simon, we made the change to ALTER TABLE after we saw customers having problems with data recovery in cases here one table had both temporal types. The potential for data loss was sufficient for Support to want the change. It's also sufficient for us to want it to be enabled by default because people tend not to know in advance that they are going to need data recovery work. We're trying protect those who are much less capable than you and who make up the majority of the users of MySQL - most of those don't even have a full time DBA.

Please do switch the setting to disabled to avoid the aggravation on your systems.

James Day, MySQL Senior Principal Support Engineer, Oracle

Noted in 5.6.24, 5.7.6 changelogs.

ALTER TABLE did not take advantage of fast alterations that might
otherwise apply to the operation to be performed, if the table
contained temporal columns found to be in pre-5.6.4 format (TIME,
DATETIME, and TIMESTAMP columns without support for fractional
seconds precision). Instead, it upgraded the table by rebuilding it.
Two new system variables enable control over upgrading such columns
and provide information about them:

* avoid_temporal_upgrade controls whether ALTER TABLE implicitly
  upgrades temporal columns found to be in pre-5.6.4 format. This
  variable is disabled by default. Enabling it causes ALTER TABLE not
  to rebuild temporal columns and thereby be able to take advantage of
  possible fast alterations.

* show_old_temporals controls whether SHOW CREATE TABLE output includes
  comments to flag temporal columns found to be in pre-5.6.4 format.
  Output for the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS
  table is affected similarly. This variable is disabled by default.

Both variables are deprecated and will be removed in a future MySQL
release.

commit cad840e6e043854c6903d72b21c621033f739b8a
Author: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>
Date:   Fri Jan 16 15:30:58 2015 +0530

    BUG#18985760: "FAST" ALTER TABLE CHANGE ON ENUM COLUMN TRIGGERS
                   FULL TABLE REBUILD
    
    Analysis:
    --------
    Certain ALTER TABLE operations on tables with temporal columns
    in pre-5.6.4 binary format were forced to use 'slow' COPY
    algorithm, even though in theory such operations could have
    been executed using 'fast' INPLACE algorithm. Thus for large
    tables such ALTER TABLEs took a lot of time while they were
    expected to be 'fast'.
    
    Starting from version 5.6.16, ALTER TABLE statements with
    ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE clauses upgrades
    the temporal columns in the old binary format to the new format
    introduced in version 5.6.4. Such upgrade requires table
    rebuild using COPY algorithm and hence it is not a 'fast'
    operation, as it can be expected in some cases
    (for example, when adding elements to the end of enum type).
    
    Fix:
    ---
    1) A new global dynamic variable 'avoid_temporal_upgrade'
       and corresponding start-up option is introduced.
       Values accepted: Boolean values.
       Default value: 0 or FALSE.
       Note: The variable/option is deprecated and will be removed
       in the future release.
    
       When the variable is set to 1, ALTER TABLE will not try to upgrade
       the old temporal columns (unless temporal column definition
       is changed). Thus the ALTER TABLE will behave in the same way
       as it did in MySQL-5.6.15.
    
       When the variable is set to 0, ALTER TABLE will try to upgrade
       old temporal columns to new format for ALTER TABLE requests
       ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE operation i.e the
       behavior introduced from MySQL-5.6.16.
    
    2) Also a new per-session dynamic system variable
      'show_old_temporals' and a corresponding start-up option is
       introduced.
       Values accepted: Boolean values.
       Default value: 0 or FALSE.
       Note: The variable/option is deprecated and will be removed
       When this variable set to 1 for the session, SHOW CREATE
       TABLE behavior for that session is changed to mark temporal
       columns using old binary format in the following way:
    
       CREATE TABLE `ts` (
       `t` timestamp /* 5.5 binary format */ NULL DEFAULT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       Also a similar comment is added to the COLUMN_TYPE field of
       I_S.COLUMNS table.
    
       When this variable is set to 0, both SHOW CREATE TABLE and
       I_S.COLUMNS won't mark the old temporals and continue with
       the existing behavior.

http://mechanics.flite.com/blog/2015/07/15/the-downside-of-the-mysql-5-dot-6-implicit-temp...