Created
October 19, 2016 15:30
-
-
Save jage/65898716db854547f783834beb36eeea to your computer and use it in GitHub Desktop.
InnoDB tables to run `ALTER TABLE ... FORCE` on after a MySQL 5.5 -> 5.6 upgrade
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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