MySQL 8.0: Removing support for old temporal datatypes

MySQL 5.6 introduced  the fractional second precision for temporal data types – TIME, DATETIME, and TIMESTAMP,  with up to microseconds (6 digits) precision. Along with providing fractional second precision, the new implementation for temporals is disk efficient.

What is an “Old Temporal”?

Old temporals refer to the temporal type columns in tables created in MySQL versions 5.5 and below.  Any new table created with temporal types after MySQL 5.6.4 (DMR) will automatically get fractional second precision.  Any ALTER command which rebuilds the table will automatically convert old temporals to temporals with fractional second precision. But old temporals can survive for users coming from MySQL 5.5, doing in-place upgrade and using option –avoid-temporal-upgrade.  Queries to check if your database has any old temporal type left:

This blog post describes how to check for old temporal types in detail.

Well, what is changing? Why start checking for old temporals NOW() (temporal pun!). Old temporals have been deprecated since mysql-5.6, but as of MySQL 8.0 support will be removed.  This means that in-place upgrade from mysql-5.7 to mysql-8.0 will not be possible if your database has old temporal types present.

How to Fix Old Temporals

You have identified all tables with old temporals. What to do next?  Any ALTER command which rebuilds the table or REPAIR TABLE command will convert old temporal to temporal with fractional second precision. For most users, executing mysql_upgrade after upgrading to MySQL 5.7 (in-place upgrade) would have fixed the old temporal types (Refer DOC and BLOG on how to do in-place upgrade to mysql-5.7).  But if you have managed to save old temporals in MySQL 5.7 using –avoid-temporal-upgrade,  here are some methods to upgrade old temporals in mysql-5.7.

Using ALTER  command

Rebuild the table using ALTER TABLE <table_name> FORCE  command.

The blog post covers this topic in detail. Any concurrent operation on the table being altered is prohibited.

Using Tools

ALTER command is practical to convert old temporals to temporals with fractional second precision when size of the table being altered is small.  For huge tables, alter command can take considerable amount of time. Converting a table of one terabyte size can take several days with alter command. To avoid blocking of table for an extended period, it is preferable to use a tool to build a new table in the background while allowing normal traffic on the old table. There will be a small blocking period while the switch of tables happens (convert while being online). Tools like Gh-ost can be used for doing an online schema change.

The following command converts old temporals to temporals with fractional second precision on a server started with –log-bin option (–log-bin option is a requirement for Gh-ost).

Before doing any operation on the table, it is recommended to read relevant documentation, have a backup of the production server and try out first on a non-production server.

Conclusion

It is important to point out that this deprecation does not affect users who have upgraded via a logical backup (such as mysqldump), but an in-place upgrade from a database using MySQL 5.5 and older.  By removing support for old temporals, we are able to reduce some of the complexities from our code-base and spend more time working on developing new features.

Thank you for using MySQL! Leave any feedback, comment and suggestion here.

About Abhishek Ranjan

Abhishek Ranjan has been working as Software Developer in MySQL Runtime team since 2012. He is currently employed by Oracle, based in India.

6 thoughts on “MySQL 8.0: Removing support for old temporal datatypes

  1. One thing to keep in mind is mysql_upgrade script on 5.7 will detect and rebuild any table still using the old format unless you use –upgrade-system-tables option

  2. This is a good post describing the change from MySQL 5.5 to 5.6 in the temporal types on-disk format. Something like this seems like such an easy thing to do yet the auto-rebuild of tables that it triggered proved to be very troublesome. Better functionality is always good but sudden changes are not so good, so getting the right balance is something which takes time. The in-place binary upgrades of MySQL was quite painful with older versions of MySQL and thankfully it is getting much smoother now. That is really appreciated.

    So now it is time to move away from the on-disk old temporal format for those such as myself who needed more time prior to adapting to the change.

    One thing that surprises me in the description of tooling that can be used to make the change is the mention of pt-online-schema-change. This is a battle-tested and trusted tool that has been around for some time and pretty much works fine. Gh-ost came out of the need to resolve some issue with pt-online-schema-change under high write workloads and the tool seems to resolve many of those problems magnificantly. There’s a great blog post https://www.percona.com/blog/2017/07/12/gh-ost-benchmark-against-pt-online-schema-change-performance/ by Peter on his experiences with both tools together with a counter-response by Shlomi. I have also seen issues with pt-online-schema-change especially with older versi
    ons of MySQL (5.6) under high load but I have also seen some issues some months ago with gh-ost too. They may well be resolved now and the issues I saw may have been specific to my environment.

    The point here is that pt-online-schema-change works well under most circumstances. It’s another tool to consider apart from gh-ost. Prior to doing any changes mentioned in this blog you really do need to check very thoroughly the process of rebuilding your tables to ensure the data gets transformed correctly. Once you are happy with the tool you’ve chosen then go ahead and get ready for the move to 8.0.

    One final comment which isn’t mentioned in the blog post is: What happens if you don’t do the required table migration and do an in-place binary upgrade to 8.0? What will 8.0 say to you if try to startup mysqld (and/or run mysql_upgrade) with old old temporal columns still sitting on disk? I would hope the error message and “recovery procedure” will be clear as many people really do just “swap binaries and run mysql_upgrade”. For the few that don’t double check on required procedures it would be good that 8.0 does not crash but gives an understandable error message. I’ve already seen with a similar issue that the InnoDB redo log filees get rebuilt for 8.0 prior to doing other checks which means that going back did mean wiping them out, so clarification here would be good and a safe backout strategy would be convenient if that is possible.

    Either way the “heads up” for those of us affected by this change is appreciated: 8.0 doesn’t seem so far away now so we do need to get prepared.

    1. Hi Simon,

      Thank you for the comprehensive and pragmatic feedback.
      As you mentioned, pt-online-schema-change can be used for altering a table’s structure without blocking reads or writes.

      If binary in-place upgrade is done to mysql-8.0 with old temporals data still present in the data directory, server will refuse to start on the data directory.
      User will get the error message : Table upgrade required. Please do “REPAIR TABLE 55_temporal” or dump/reload to fix it!
      Basically, user needs to rebuild the table using ALTER or REPAIR command or on-line tools in mysql-5.7 before proceeding to upgrade.

      1. Hi,

        If we had old temporal datatypes in MySQL 5.7 and we did an in-place upgrade to MySQL 8, it won’t start as you just wrote but is it possible to run mysql_upgrade which will ALTER the necessary tables?

        Do we really have to remove MySQL 8 and install back MySQL 5.7 to run an alter table?

        Thanks.

        1. Hi,

          Thank you for your query.

          The mysql_upgrade included in 5.7 will upgrade all tables to remove old temporals. This means that the use-case where old temporals still remain is limited – binary upgrades from 5.5 and below specifically avoiding temporal upgrades, or upgrades that have avoided running mysql_upgrade.

          For the specific case that the user has avoided the upgrade steps, they will have to take a step back to 5.7 before going to 8.0.

Leave a Reply

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

Please enter * Time limit is exhausted. Please reload CAPTCHA.