MySQL :: Upgrading old MySQL-5.5 format temporals to MySQL-5.6 format.

The temporal datatypes ‘TIME/DATETIME/TIMESTAMP’ supports fractional values starting from MySQL 5.6.4 version. Hence the storage requirement and encoding differs in comparison to pre- MySQL 5.6.4 temporal datatypes.

As mentioned in the section ‘Upgrading from MySQL 5.5 to 5.6’,
http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
Once you have imported the pre-MySQL 5.6.4 tables, ‘ALTER TABLE’ allows creation of tables containing temporal columns of both mysql-5.5 and mysql-5.6 format.

As noted in the documentation, the disadvantages of having such tables are:

Recreating tables having both 5.5 and 5.6 temporals becomes tedious process when the metadata file(.frm) for the table is unavailable. This would involve recreating the table having the old temporal columns using 5.5 server instance and then adding the 5.6 temporal columns using 5.6 server instance.

Recreating tables having 5.5 temporals and 5.6 INNODB FULLTEXT indexes has to follow the same process.

The old temporal formats are not space efficient.

There is a likelihood that the old temporal format would not be supported in the future releases.

Currently operations are supported on the old temporal columns. The old temporal columns are upgraded to the new format only when they are altered(like renaming the old temporal column) else they are retained in the old format.

In order to overcome the above mentioned shortcomings, versions starting from MySQL 5.6.16 upgrades the old temporal types to the new temporal format while performing certain ALTER TABLE operations. 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.

For example, consider the ALTER TABLE, FORCE operation which reconstructs table:

ALTER TABLE t1 FORCE;
Warnings:
Note 1880 TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

Note that such conversions cannot be done using INPLACE algorithm. If attempted for a table with mysql-5.5 temporals, the operation remains unsuccessful and an error is flagged.