Upgrading temporal columns from MySQL 5.5 to MySQL 5.6 forma_MySQL

Before upgrading from MySQL 5.5 to 5.6, I read therelevant pagein thereference manualand found this section about the storage format change forDATETIME,TIME,TIMESTAMPdata 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 youhowto “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 ablog postfrom 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 useALTER TABLE ... FORCEto rebuild my tables. But which tables need to be rebuilt? I could simply rebuild every table that hasDATETIME,TIME, and/orTIMESTAMPcolumns, 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 theMTYPEvalue in theINFORMATION_SCHEMA.INNODB_SYS_COLUMNStable, since that value will be6 (INT)for the old storage format and3 (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 withDATETIME,TIME, and/orTIMESTAMPcolumns.

Here’s a query to list all of the relevant columns suspected of using the old format:

<code>select t.table_schema,t.engine,t.table_name,c.column_name,c.column_typefrom information_schema.tables t inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_nameleft outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name)left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name where c.column_type in ('time','timestamp','datetime')and t.table_schema not in ('mysql','information_schema','performance_schema')and t.table_type = 'base table'and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6))order by t.table_schema,t.table_name,c.column_name;</code>

And here’s a quick way to dump theALTER TABLEcommands to a script and then execute that script:

<code>select distinct concat('set sql_log_bin = 0; alter table ',t.table_schema,'.',t.table_name,' force;') as ddlinto outfile '/tmp/rebuild_tables_to_upgrade_tempral_storage_format.sql'from information_schema.tables t inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_nameleft outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name)left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name where c.column_type in ('time','timestamp','datetime')and t.table_schema not in ('mysql','information_schema','performance_schema')and t.table_type = 'base table'and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6))order by (t.data_length + t.index_length) asc;/W /. /tmp/rebuild_tables_to_upgrade_tempral_storage_format.sql</code>

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 myDATETIMEcolumns shrink from 8 bytes to 5 bytes (assuming I’m not using fractional seconds)!