How to handle temporal datatype columns when doing upgrade from 5.5 to 5.6

Question

I want to upgrade my MySQL 5.5.40 server to 5.6.28. But as per the documentation we need to alter the tables having columns which are using date or datetime or timestamp data types as storage requirement for these data types changed and to allow fractional parts for these temporal data types.

https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

ALTER TABLE tbl_name FORCE;

But how can we confirm that storage is altered. Is there any query or something to check that table columns are updated. What are the consequences if we don't alter the table and just done an in place upgrade from 5.5 to 5.6?

Answer 1

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;

http://mechanics.flite.com/blog/2014/05/01/upgrading-temporal-columns-from-mysql-5-dot-5-to-mysql-5-dot-6-format/

Answer 2

Without the upgrade, you cannot use fractions of a second.

Old data structure was 4 bytes for TIMESTAMP, 8 bytes for DATETIME.
New structure is 5 bytes for each, depending on how many decimal places of time you pick.
If you later allow for fractional seconds, the space is up to 8 bytes.