Identifying Temporal Columns in Old Format in MySQL 5.6 and Above
I’ve gotten a lot of positive feedback from my post on upgrading temporal columns from MySQL 5.5 to MySQL 5.6 format, both in the blog comments and directly from people who have used my query to identify which columns are candidates to be upgraded to the new format. Based on the feedback and some changes in more recent MySQL releases, I think it’s worth doing a follow-up post.
It was pointed out to me that the SQL query I used in my previous post does not work for partitioned tables. The reason is the join between
information_schema.innodb_sys_tables in my query. For non-partitioned tables the value in
information_schema.innodb_sys_tables.name will be
<table_schema>/<table_name>, but for partitioned tables there will be one row in
information_schema.innodb_sys_tables for each partition, with the naming pattern of
<table_schema>/<table_name>#P#<partition_name>. Thus I need to update my query to support both naming patterns.
When I started testing the MySQL 5.5 to 5.6 upgrade on partitioned tables I was curious whether a table could have some partitions using the old format and some using the new format, and whether I could convert the columns one partition at a time. Based on my testing it appears that a given table will always use the same temporal column format for all partitions, and in order to upgrade the format of those columns you need to rebuild the entire table. If I upgrade a partitioned table with datetime/timestamp/time columns from 5.5 to 5.6 those columns will still be in the old format for all partitions. If I add new partitions to that table the datetime/timestamp/time columns in the new partitions will be in the old format. Partition-level maintenance operations such as
COALESCE PARTITION, and
REORGANIZE PARTITION do not upgrade the temporal column format, so I still need to use a table-level operation such as
ALTER TABLE...FORCE or
OPTIMIZE PARTITION (which is a misnomer because it actually rebuilds the entire table).
Here’s the new query that will work for non-partitioned and partitioned tables in versions of MySQL up to 5.6.23 and 5.7.5:
1 2 3 4 5 6 7 8 9 10
It’s possible to refine the query further for partitioned tables by joining to the
information_schema.partitions table, but given the fact that you have to rebuild the entire table anyway I use the above query. In case you are curious, here is the version of the query that includes the partition names:
1 2 3 4 5 6 7 8 9 10 11
Improvements in MySQL 5.6.24+ and 5.7.6+
MySQL 5.6.24 and 5.7.6 added a new system variable called
show_old_temporals which simplifies the process of identifying temporal columns that need to be upgraded. With that variable enabled the
information_schema.columns.column_type column will have
" /* 5.5 binary format */" appended for columns in the old format. That removes the need to join to
information_schema.innodb_sys_columns, and unlike my old query it also works with storage engines other than InnoDB.
Here’s the simplified query that you should use in 5.6.24+ and 5.7.6+ to identify temporal columns in the old format:
1 2 3 4 5 6 7 8 9