Bug #73008 "check table .. for upgrade" fails to report old datetime format.

• Submitted: 15 Jun 2014 7:51
• Modified: 25 Feb 2015 18:55
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.6.20
• OS: Any
• CPU Architecture: Any

[15 Jun 2014 7:51] Shane Bester

Description:
"check table .. for upgrade" fails to report old datetime format.

the consequence is as follows:
o)  mysql_upgrade does not know to issue a rebuild command on these tables.
o)  'fast alter table' cannot be done on these old-style formats.

How to repeat:
In 5.5 create a table like:
create table t1(a datetime, b timestamp, c date, d time)engine=innodb;

upgrade to 5.6 and run:

 check table t1 for upgrade;

Suggested fix:
When I run: "check table t1 for upgrade;"  I expect to see something about table requiring a rebuild to convert to the new format columns.

[25 Feb 2015 18:55] Paul DuBois

Noted in 5.7.7, 5.8.0 changelogs.

CHECK TABLE ... FOR UPGRADE did not report temporal columns that use
the old datetime format (from before MySQL 5.6.4). Consequently,
mysql_upgrade did not know to rebuild tables that contain such
columns, and subsequent ALTER TABLE statements were unable to perform
fast alterations to the extent possible had the tables been repaired.

[27 Feb 2015 16:19] Paul DuBois

Revised changelog entry:

CHECK TABLE ... FOR UPGRADE did not report temporal columns that use
the old datetime format (from before MySQL 5.6.4). Consequently,
mysql_upgrade did not know to issue REPAIR TABLE statements to
rebuild tables that contain such columns, and subsequent ALTER TABLE
statements were unable to perform fast alterations to the extent
possible had the tables been repaired. Now, if the
avoid_temporal_upgrade system variable is disabled, CHECK TABLE
reports old temporal columns and REPAIR TABLE upgrades tables from
old temporal format to the new format.