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: