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.

7 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. Hi,

    Is there a way to identify all tables with such anomaly so that they can be upgraded in a batch operation?

    Thanks
    Hamed

    1. Hello Hamed,

      Currently we cannot identify all the tables in a database having the old temporal types.

      Regards,
      Nisha.

  3. 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;

Leave a Reply

Your email address will not be published. Required fields are marked *


three − 2 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">