Upgrading Temporal Columns From MySQL 5.5 to MySQL 5.6 Format
Before upgrading from MySQL 5.5 to 5.6, I read the relevant page in the reference manual and found this section about the storage format change for
TIMESTAMP data types to support microseconds:
Incompatible change: For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from storage required for tables created in 5.6.4 and later. This is due to a change in 5.6.4 that permits these temporal types to have a fractional part. After upgrading from MySQL 5.5 to MySQL 5.6.4 or later, it is recommended that you also upgrade from MySQL 5.5 to MySQL 5.6 TIME, DATETIME, and TIMESTAMP types…
The problem is that the manual doesn’t tell you how to “upgrade from MySQL 5.5 to MySQL 5.6 TIME, DATETIME, and TIMESTAMP types”. I figured simply rebuilding the relevant tables would probably do the trick, and I found some confirmation in a blog post from the MySQL server team. Quoting that post:
The ALTER TABLE requests ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE operation upon detecting old temporal data types upgrades them to the new format. Also a ‘NOTE’ is reported to indicate the user that an upgrade of the old temporal columns to the new format has been performed as well.
So I figured I would use
ALTER TABLE ... FORCE to rebuild my tables. But which tables need to be rebuilt? I could simply rebuild every table that has
TIMESTAMP columns, but I’d rather be able to tell which storage format the table is using so don’t end up rebuilding it if I don’t need to. For InnoDB tables I can identify the columns using the old temporal types by checking the
MTYPE value in the
INFORMATION_SCHEMA.INNODB_SYS_COLUMNS table, since that value will be
6 (INT) for the old storage format and
3 (FIXBINARY) for the new storage format. Since almost all of my tables are InnoDB that approach works well for me. For other storage engines I’ll just rebuild all tables with
Here’s a query to list all of the relevant columns suspected of using the old format:
1 2 3 4 5 6 7 8 9 10
And here’s a quick way to dump the
ALTER TABLE commands to a script and then execute that script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
If I re-run the above it will not rebuild the InnoDB tables that were already rebuilt, but it will rebuild the MyISAM tables that were already rebuilt because I don’t know how to tell if they are using the old format or not.
Since I enabled warnings I get this friendly note every time I convert a table:
Note (Code 1880): TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
As an added bonus all of my
DATETIME columns shrink from 8 bytes to 5 bytes (assuming I’m not using fractional seconds)!