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.



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?

2 Answers 2

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;


  • i felt this link is very useful for upgrading replication environments. dzone.com/articles/…
    – kasi
    Commented Dec 23, 2015 at 12:10

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.