The Downside of the MySQL 5.6 Implicit Temporal Column Format Upgrade
I’ve written two separate posts about the MySQL 5.6 temporal column format change, but I haven’t yet addressed the problem with the way this upgrade was implemented. Essentially, the problem is that the implicit format upgrade causes a write-blocking full table rebuild the first time you run
ALTER TABLE after upgrading, even if the
ALTER TABLE command is an online DDL operation that normally doesn’t block writes.
This behavior is well intentioned, but harmful. The intent is for the server to tack on a seemingly innocuous column format upgrade to my existing
ALTER TABLE command, but when it changes my DDL from non-blocking to blocking it undermines online DDL and becomes problematic.
Read on for detailed examples and workarounds.
For example, say I have the following 10 million row table in a MySQL 5.5 database:
1 2 3 4 5 6 7 8 9 10 11
In MySQL 5.5 there is no online DDL, so if I add a column or an index to that table MySQL will not allow insert/update/delete queries on the table until my
ALTER TABLE command completes.
For example running this DDL in MySQL 5.5 will block all insert/update/delete queries:
If I upgrade my database to MySQL 5.6 the
joined column will be in the old temporal format. If it’s a version of MySQL 5.6 before 5.6.16 then I can add columns and indexes using online DDL without blocking writes, but if I am using 5.6.16 or newer then by default the
ALTER TABLE will trigger a write-blocking table rebuild in order to upgrade the format of the datetime column.
Here’s an example of what that looks like:
1 2 3 4 5 6 7 8
How can I assure that my DDL operations don’t trigger an implicit temporal column format upgrade?
There are a couple of ways to do it.
Workaround #1: pt-online-schema-change
Percona Toolkit includes a tool called pt-online-schema-change that allows you to alter tables without blocking writes. As described in the documentation:
pt-online-schema-change works by creating an empty copy of the table to alter, modifying it as desired, and then copying rows from the original table into the new table. When the copy is complete, it moves away the original table and replaces it with the new one. By default, it also drops the original table.
Using pt-online-schema-change to alter a table will not block reads or writes, and since it creates a new table to replace your existing table any datetime/timestamp/time columns will be in the new temporal format so I don’t need to worry about the implicit format change occurring the next time I alter the table.
Here’s an example of how I can add the previously discussed column using pt-online-schema-change:
1 2 3 4
Another way to avoid the implicit format change is to generate a list of tables with datetime/timestamp/time columns in the old format and execute a simple no-op alter on all of those tables using pt-osc to explicitly upgrade the format of those columns.
I can use one of the queries from my two previous posts to generate a list of tables that need to be upgraded. I could even modify one of those queries to generate a shell script, and then execute that shell script to upgrade the columns. There are lots of ways to run pt-osc so this exact query might not work for you, but here’s an example of the SQL to shell script method in action:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
Workaround #2: avoid_temporal_upgrade
If I upgrade from MySQL 5.5 to MySQL 5.6.24 or higher then I can enable a global, dynamic server variable called
avoid_temporal_upgrade in order to disable the implicit temporal column format upgrades. With
avoid_temporal_upgrade enabled I can postpone the temporal column format upgrade, but at some point in the future MySQL will stop supporting the old format so eventually I will still need to upgrade the format of any datetime/timestamp/time columns in the old format.
Here’s an example of using
avoid_temporal_upgrade to add the previously discussed column in MySQL 5.6.25 without blocking writes on my table:
1 2 3 4 5 6 7 8 9 10
I should point out that
avoid_temporal_upgrade disables the temporal column format upgrade for all DDL, not just online DDL. Even if I do something that blocks writes anyway (like change the data type of a column, drop a primary key, or add a fulltext index) the temporal column format upgrade will still not happen. As far as I can tell it’s impossible to upgrade the temporal column format while
avoid_temporal_upgrade is enabled. Thus if I do want to upgrade the temporal column format for a specific table, I either need to temporarily disable
avoid_temporal_upgrade, or use pt-online-schema-change as previously discussed to rebuild the table in a non-blocking fashion.