EmergencyEMERGENCY? Get 24/7 Help Now!

Upgrading to MySQL 5.7, focusing on temporal types

 | April 27, 2016 |  Posted In: MySQL, Percona Server for MySQL

PREVIOUS POST
NEXT POST

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.

Conclusion

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.

 

PREVIOUS POST
NEXT POST
Tibor Korocz

Tibi joined Percona in 2015 as a Consultant. Before joining Percona, among many other things, he worked at the world’s largest car hire booking service as a Senior Database Engineer. He enjoys trying and working with the latest technologies and applications which can help or work with MySQL together. In his spare time he likes to spend time with his friends, travel around the world and play ultimate frisbee.

2 Comments

  • 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….

    • Hi Mike,

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

      https://bugs.mysql.com/bug.php?id=79060

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

      Thanks,
      Tibi

Leave a Reply