Skip to content

Instantly share code, notes, and snippets.

@jage

jage/innodb-tables-to-alter.sql

Created October 19, 2016 15:30
Show Gist options
  • Save jage/65898716db854547f783834beb36eeea to your computer and use it in GitHub Desktop.
Save jage/65898716db854547f783834beb36eeea to your computer and use it in GitHub Desktop.
Download ZIP
InnoDB tables to run `ALTER TABLE ... FORCE` on after a MySQL 5.5 -> 5.6 upgrade
Raw
innodb-tables-to-alter.sql
# Improved version of http://mysqlserverteam.com/upgrading-old-mysql-5-5-format-temporals-to-mysql-5-6-format-2/#comment-746
SELECT t.table_schema,t.table_name,c.column_name,c.column_type, iss.num_rows
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.innodb_sys_tables ist ON ist.name = concat(t.table_schema,"/",t.table_name)
INNER JOIN information_schema.innodb_sys_columns isc ON isc.table_id = ist.table_id AND isc.name = c.column_name
INNER JOIN information_schema.innodb_sys_tablestats iss ON iss.name = concat(t.table_schema,"/",t.table_name)
WHERE t.engine = "innodb"
AND c.column_type IN ("time","timestamp","datetime")
AND isc.mtype = 6
ORDER BY t.table_schema,t.table_name,c.column_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment