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’,
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:

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.

About Nisha Gopalakrishnan

Nisha Gopalakrishnan has been working with MySQL database since 2012. She is working out of the Oracle office in Bangalore as a Senior Software Engineer. Her industry background has been system software development in database internals, file system and security.

9 thoughts on “Upgrading old MySQL-5.5 format temporals to MySQL-5.6 format.

  1. It is good that you now indicate the conversion happens and that seems to imply that you check this to catch tables that may have the older format.

    I made a bug report that changes such as this are reported to in mysql_upgrade so that it can be seen that this type of change has happened. Current behaviour just shows an OK which may mean the table is fine or may mean that it has been “updated”. If you start to show the information you indicate above, then doing the same in mysql_upgrade would also be useful.

  2. To address Hamed’s question, if you are using InnoDB you should be able to identify the columns using the old temporal types by checking the mtype value in the innodb_sys_columns table, since that value will be 6 (int) for the old storage format and 3 (fixbinary) for the new storage format.

    Here’s a query I’ve used for this purpose:

    select t.table_schema,t.table_name,c.column_name,c.column_type
    from information_schema.tables t
    inner join information_schema.columns c on c.table_Schema = t.table_schema and c.table_name = t.table_name
    inner join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,’/’,t.table_name)
    inner join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name
    where t.engine = ‘innodb’
    and c.column_type in (‘time’,’timestamp’,’datetime’)
    and isc.mtype = 6
    order by t.table_schema,t.table_name,c.column_name;

  3. if i’m doing a non-binary upgrade (that is, dumping in the old version and load that script in 5.6), do I still have to do this ALTER TABLE trick?

    1. Hello Sebastian,

      If you are using a tool like ‘mysqldump’ which produces a set of SQL statements
      in order to recreate the schema objects on 5.6, then the tables having temporal columns would be in the new format. This is because we don’t support creation of tables with columns of old temporal format in 5.6. So the ALTER TABLE statement for the sole purpose of upgrading the temporal columns in old format can be avoided.


Leave a Reply