MySQL :: 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:

mysql> SET show_old_temporals = ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): '@@show_old_temporals' is deprecated and will be removed in a future release.
mysql> SELECT table_schema, table_name,column_name,column_type FROM information_schema.columns WHERE column_type LIKE 'timestamp /* 5.5 binary format */'\G
*************************** 1. row ***************************
table_schema: db
  table_name: 55_temporal
 column_name: f_timestamp
 column_type: timestamp /* 5.5 binary format */
1 row in set (0.09 sec)

mysql> SET show_old_temporals = ON;

Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): '@@show_old_temporals' is deprecated and will be removed in a future release.

mysql> SELECT table_schema, table_name,column_name,column_type FROM information_schema.columns WHERE column_type LIKE 'timestamp /* 5.5 binary format */'\G

*************************** 1. row ***************************

table_schema: db

table_name: 55_temporal

column_name: f_timestamp

column_type: timestamp /* 5.5 binary format */

1 row in set (0.09 sec)

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.

mysql> ALTER TABLE db.55_temporal FORCE;
Query OK, 5 rows affected, 1 warning (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 1

Note (Code 1880): TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

mysql> ALTER TABLE db.55_temporal FORCE;

Query OK, 5 rows affected, 1 warning (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 1

Note (Code 1880): TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

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).

gh-ost --user=root --database=db --table=55_temporal --alter="FORCE" --allow-on-master --execute

1	gh-ost --user=root --database=db --table=55_temporal --alter="FORCE" --allow-on-master --execute

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.