-- Find tables which do not have their tablespace registered in INNODB_SYS_TABLESPACES
-- This happens for tables created on MySQL 5.1
--
-- This ignores partitioned tables as mysql_upgrade should have ran
-- ALTER TABLE … UPGRADE PARTITIONING on them during the 5.7 upgrade.
--
-- The join might not work for non-ascii table names.
--
-- FIX: Run OPTIMIZE TABLE on them.

SELECT
  it.table_schema,
  it.table_name
FROM
  information_schema.TABLES it
  LEFT JOIN information_schema.PARTITIONS ip
    ON (it.TABLE_SCHEMA=ip.TABLE_SCHEMA AND it.TABLE_NAME=ip.TABLE_NAME
        AND ip.PARTITION_NAME IS NOT NULL)
  LEFT JOIN information_schema.INNODB_SYS_TABLESPACES ts
    ON ts.NAME=CONCAT(it.table_schema, '/', it.table_name)
WHERE
  it.ENGINE='InnoDB'
  AND it.TABLE_TYPE='BASE TABLE'
  AND it.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
  AND ip.PARTITION_NAME IS NULL
  AND ts.SPACE IS NULL;

-- Find tables with "new" decimal columns which have a binary (63) charset
-- instead of latin1 (8)
--
-- FIX: Run OPTIMIZE TABLE on them
SELECT
  t.NAME AS table_name,
  c.NAME AS column_name
FROM
  information_schema.INNODB_SYS_COLUMNS c
  LEFT JOIN information_schema.INNODB_SYS_TABLES t USING (TABLE_ID)
WHERE
  c.MTYPE=3                    /*   3 = DATA_FIXBINARY                 */
  AND PRTYPE >> 16 = 63        /*  63 = DATA_MYSQL_BINARY_CHARSET_COLL */
  AND PRTYPE & 255             /* 255 = DATA_MYSQL_TYPE_MASK           */
  = 246                        /* 246 = MYSQL_TYPE_NEWDECIMAL          */;