Upgrading to MySQL 5.7, focusing on temporal types

temporal typesIn this post, we’ll discuss how MySQL 5.7 handles the old temporal types during an upgrade.

MySQL changed the temporal types in MySQL 5.6.4, and it introduced a new feature: microseconds resolution in the TIME, TIMESTAMP and DATETIME types. Now these parameters can be set down to microsecond granularity. Obviously, this means format changes, but why is this important?

Are they converted automatically to the new format?

If we had tables in MySQL 5.5 that used TIME, TIMESTAMP or DATETIME are these fields are going to be converted to the new format when upgrading to 5.6? The answer is “NO.” Even if we run mysql_upgrade, it does not warn us about the old format. If we check the MySQL error log, we cannot find anything regarding this. But the newly created tables are going to use the new format so that we will have two different types of temporal fields.

How can we find these tables?

The following query gives us a summary on the different table formats:

Or we can use show_old_temporals, which will highlight the old formats during a show create table.

MySQL can handle both types, but with the old format you cannot use microseconds, and the default DATETIME takes more space on disk.

Can I upgrade to MySQL 5.7?

Of course you can! But when mysql_upgrade is running it is going to convert the old fields into the new format by default. This basically means an alter table on every single table, which will contain one of the three types.

Depending on the number of tables, or the size of the tables, this could take hours – so you may need to do some planning.

Can we avoid this at upgrade?

We can run alter tables or use pt-online-schema-schange (to avoid locking) before an upgrade, but even without these preparations we can still avoid incompatibility issues.

My colleague Daniel Guzman Burgos pointed out that  mysql_upgrade has an option called upgrade-system-tables. This will only upgrade the system tables, and nothing else.

Can we still write these fields?

The following query returns the schema and the table names that still use the old formats.

As we can see, we’re using 5.7 and table “test.t” still has the old format.

The schema:

Let’s try to insert a new row:

It was inserted without a problem, and we can’t see any related info/warnings in the error log.

Does the Replication work?

In many scenarios, when you are upgrading a replicaset, the slaves are upgraded first. But will the replication work? The short answer is “yes.” I configured row-based replication between MySQL 5.6 and 5.7. The 5.6 was the master, and it had all the temporal types in the old format. On 5.7, I had new and old formats.

I replicated from old format to old format, and from old format to new format, and both are working.


Before upgrading to MySQL 5.7, tables should be altered to use the new format. If it isn’t done, however, the upgrade is still possible without altering all the tables – the drawbacks are you cannot use microseconds, and it takes more space on disk. If you had to upgrade to 5.7, however, you could change the format later using alter table or pt-online-schema-schange.


Share this post

Comments (4)

  • Mike Sirs

    after upgrade from 5.6 to 5.7 i’ve got replication error,

    Cannot get geometry object from data you send to the GEOMETRY field’ on query. Default database: ‘my_database’. Query: ‘INSERT INTO table (file_id, polygon, a_latitude, b_latitude, c_longitude, d_longitude) VALUES ( 93937, GeomFromText(“Polygon((36.91 -121.705,36.908….

    April 28, 2016 at 2:39 pm
    • Tibor Korocz

      Hi Mike,

      Your question is not relevant to this blog post but maybe you have a similar issue like in this Bug report:


      If you don’t use the latest 5.7 try that one.


      April 29, 2016 at 7:18 am
  • Skye Shaw

    MySQL 5.6.24 added the show_old_temporals variable [1]. It’s dynamic and works with show create table.

    MySQL’s docs also say [2] that tables with pre 5.6 date format columns will not be able to take full advantage of the online DDL improvements introduced in 5.6 (i.e., use algorithm=inplace with alter table).

    [1]: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_show_old_temporals
    [2]: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

    January 12, 2018 at 11:20 am
  • Arthur M

    Really useful blog post, thanks! Small type in your query: The first occurrence of t.schema_name should be t.table_schema.

    February 28, 2019 at 1:26 pm

Comments are closed.