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.

Partioned tables

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.tables and 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 REBULD PARTITION, 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

select distinct t.table_schema,t.engine,t.table_name,c.column_name,c.column_type
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,c.column_name;

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

select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type,p.partition_name
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
  inner join information_schema.partitions p on p.table_schema = t.table_Schema and p.table_name = t.table_name
  left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_name,'#P#',p.partition_name)
  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,c.column_name;

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_tables and 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

set session show_old_temporals=ON;

select t.table_schema,t.engine,t.table_name,c.column_name,c.column_type
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
where c.column_type in ('time /* 5.5 binary format */','timestamp /* 5.5 binary format */','datetime /* 5.5 binary format */')
  and t.table_schema not in ('mysql','information_schema','performance_schema')
  and t.table_type = 'base table'
order by t.table_schema,t.table_name,c.column_name;