Flite Culture

Upgrading Temporal Columns From MySQL 5.5 to MySQL 5.6 Format

Before upgrading from MySQL 5.5 to 5.6, I read the relevant page in the reference manual and found this section about the storage format change for DATETIME, TIME, TIMESTAMP data types to support microseconds:

Incompatible change: For TIME, DATETIME, and TIMESTAMP columns, the storage required for tables created before MySQL 5.6.4 differs from storage required for tables created in 5.6.4 and later. This is due to a change in 5.6.4 that permits these temporal types to have a fractional part. After upgrading from MySQL 5.5 to MySQL 5.6.4 or later, it is recommended that you also upgrade from MySQL 5.5 to MySQL 5.6 TIME, DATETIME, and TIMESTAMP types…

The problem is that the manual doesn’t tell you how to “upgrade from MySQL 5.5 to MySQL 5.6 TIME, DATETIME, and TIMESTAMP types”. I figured simply rebuilding the relevant tables would probably do the trick, and I found some confirmation in a blog post from the MySQL server team. Quoting that post:

The ALTER TABLE requests ADD/CHANGE/MODIFY COLUMN, ADD INDEX or FORCE operation upon detecting old temporal data types upgrades them to the new format. Also a ‘NOTE’ is reported to indicate the user that an upgrade of the old temporal columns to the new format has been performed as well.

So I figured I would use ALTER TABLE ... FORCE to rebuild my tables. But which tables need to be rebuilt? I could simply rebuild every table that has DATETIME, TIME, and/or TIMESTAMP columns, but I’d rather be able to tell which storage format the table is using so don’t end up rebuilding it if I don’t need to. For InnoDB tables I can identify the columns using the old temporal types by checking the MTYPE value in the INFORMATION_SCHEMA.INNODB_SYS_COLUMNS table, since that value will be 6 (INT) for the old storage format and 3 (FIXBINARY) for the new storage format. Since almost all of my tables are InnoDB that approach works well for me. For other storage engines I’ll just rebuild all tables with DATETIME, TIME, and/or TIMESTAMP columns.

Here’s a query to list all of the relevant columns suspected of using the old format:

1
2
3
4
5
6
7
8
9
10
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
  left outer join information_schema.innodb_sys_tables ist on ist.name = concat(t.table_schema,'/',t.table_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;

And here’s a quick way to dump the ALTER TABLE commands to a script and then execute that script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select distinct concat('set sql_log_bin = 0; alter table ',t.table_schema,'.',t.table_name,' force;') as ddl
into outfile '/tmp/rebuild_tables_to_upgrade_tempral_storage_format.sql'
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)
  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.data_length + t.index_length) asc;

\W 
\. /tmp/rebuild_tables_to_upgrade_tempral_storage_format.sql

If I re-run the above it will not rebuild the InnoDB tables that were already rebuilt, but it will rebuild the MyISAM tables that were already rebuilt because I don’t know how to tell if they are using the old format or not.

Since I enabled warnings I get this friendly note every time I convert a table:

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

As an added bonus all of my DATETIME columns shrink from 8 bytes to 5 bytes (assuming I’m not using fractional seconds)!

Comments

Comments