Flite Careers

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
mysql > desc member;
+------------+-------------------------+------+-----+---------+----------------+
| Field      | Type                    | Null | Key | Default | Extra          |
+------------+-------------------------+------+-----+---------+----------------+
| id         | bigint(20)              | NO   | PRI | NULL    | auto_increment |
| first_name | varchar(25)             | NO   |     | NULL    |                |
| last_name  | varchar(25)             | NO   |     | NULL    |                |
| username   | varchar(16)             | NO   |     | NULL    |                |
| email      | varchar(35)             | YES  |     | NULL    |                |
| joined     | datetime                | NO   |     | NULL    |                |
+------------+-------------------------+------+-----+---------+----------------+

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:

1
2
alter table member
  add column is_active tinyint not null default 1;

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
mysql > \W
Show warnings enabled.
mysql > alter table member 
    ->   add column is_active tinyint not null default 1;
Query OK, 10000000 rows affected, 1 warning (45.43 sec)
Records: 10000000  Duplicates: 0  Warnings: 1

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

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
pt-online-schema-change \
  --recursion-method=dsn=h=localhost,D=percona,P=3306,t=dsns \
  --alter "add column is_active tinyint not null default 1" D=test,t=member \
  --execute

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
mysql > select distinct concat('pt-online-schema-change ',
    -> '--recursion-method=dsn=h=localhost,D=percona,P=3306,t=dsns --alter "force" D=',
    -> t.table_schema,
    -> ',t=',
    -> t.table_name,
    -> ' --execute')
    -> into outfile '/tmp/upgrade_temporal_columns.sh'
    -> from information_schema.tables t 
    ->   inner join information_schema.columns c on c.table_schema = t.table_schema and c.table_name = t.table_name
    ->   left outer join information_schema.innodb_sys_tables ist on (ist.name = concat(t.table_schema,'/',t.table_name) or ist.name like concat(t.table_schema,'/',t.table_name,'#P#%'))
    ->   left outer join information_schema.innodb_sys_columns isc on isc.table_id = ist.table_id and isc.name = c.column_name 
    -> where c.column_type in ('time','timestamp','datetime')
    ->   and t.table_schema not in ('mysql','information_schema','performance_schema')
    ->   and t.table_type = 'base table'
    ->   and (t.engine != 'innodb' or (t.engine = 'innodb' and isc.mtype = 6))
    -> order by t.table_schema,t.table_name;
Query OK, 25 rows affected (0.10 sec)

mysql > exit
Bye
$ chmod +x /tmp/upgrade_temporal_columns.sh
$ /tmp/upgrade_temporal_columns.sh

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
mysql > \W
Show warnings enabled.
mysql > set global avoid_temporal_upgrade = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1287): '@@avoid_temporal_upgrade' is deprecated and will be removed in a future release.
mysql > alter table members 
    ->   add column is_active tinyint not null default 1;
Query OK, 0 rows affected (15.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

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.


Comments