SELECT table_schema, table_name, column_name, column_type 
FROM information_schema.columns 
WHERE column_type LIKE '%5.5 binary format%';

-- Rebuild each detected table
ALTER TABLE database_name.table_name FORCE;

-- Find records that exist in InnoDB but not in the TABLES table
SELECT t.NAME as table_name
FROM information_schema.innodb_sys_tables t
LEFT JOIN information_schema.tables it 
  ON CONCAT(it.TABLE_SCHEMA, '/', it.TABLE_NAME) = t.NAME
WHERE it.TABLE_NAME IS NULL
  AND t.NAME NOT LIKE 'SYS_%'
  AND t.NAME NOT LIKE 'mysql/%';

-- Find affected tables
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE data_type IN ('point', 'geometry', 'polygon', 'linestring', 
                    'multipoint', 'multilinestring', 'multipolygon', 
                    'geometrycollection');

-- Rebuild table
ALTER TABLE database_name.table_name FORCE;

-- Manually perform the check
CHECK TABLE database_name.table_name FOR UPGRADE;

-- Fix based on the specific error message returned
-- Common fixes:
REPAIR TABLE database_name.table_name;
-- or
ALTER TABLE database_name.table_name FORCE;

-- 1. View stored procedures/functions definitions
SHOW CREATE PROCEDURE database_name.procedure_name;
SHOW CREATE FUNCTION database_name.function_name;

-- 2. Add backticks to conflicting identifiers
-- BEFORE MODIFICATION
CREATE PROCEDURE test()
BEGIN
  SELECT rank FROM users;  -- rank is a reserved word in MySQL 8.0
END;

-- AFTER MODIFICATION
CREATE PROCEDURE test()
BEGIN
  SELECT `rank` FROM users;
END;

-- 3. Recreate the stored procedure
DROP PROCEDURE IF EXISTS database_name.procedure_name;
CREATE PROCEDURE database_name.procedure_name ...

-- 1. Find objects using reserved keywords
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name IN ('RANK', 'ROWS', 'GROUPS', 'FUNCTION', 'SYSTEM', ...);

-- 2. Modify the SQL in user applications by adding backticks to these identifiers
-- BEFORE MODIFICATION
SELECT rank, rows FROM my_table;

-- AFTER MODIFICATION
SELECT `rank`, `rows` FROM my_table;

-- 3. Or rename the objects (if feasible)
ALTER TABLE my_table CHANGE rank ranking INT;

catalogs, character_sets, collations, column_type_elements, columns, 
dd_properties, events, foreign_key_column_usage, foreign_keys, 
index_column_usage, index_partitions, index_stats, indexes, 
parameter_type_elements, parameters, routines, schemata, 
st_spatial_reference_systems, table_partition_values, table_partitions, 
table_stats, tables, tablespace_files, tablespaces, triggers, 
view_routine_usage, view_table_usage, component, default_roles, 
global_grants, innodb_ddl_log, innodb_dynamic_metadata, 
password_history, role_edges

-- 1. Find conflicting tables
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'mysql' 
  AND table_name IN ('catalogs', 'character_sets', 'collations', ...);

-- 2. Rename conflicting tables
RENAME TABLE mysql.conflicting_table_name TO mysql.conflicting_table_name_backup;

-- or move to other databases
RENAME TABLE mysql.conflicting_table_name TO other_db.table_name;

-- 1. Find constraint names exceeding the length limit
SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_NAME, 
       LENGTH(i.CONSTRAINT_NAME) as name_length
FROM information_schema.TABLE_CONSTRAINTS i
WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND LENGTH(i.CONSTRAINT_NAME) > 64;

-- 2. Recreate the foreign key constraint
-- Delete the old constraint first
ALTER TABLE database_name.table_name DROP FOREIGN KEY old_constraint_name;

-- Then create a new constraint (using a shorter name)
ALTER TABLE database_name.table_name 
ADD CONSTRAINT new_constraint_name FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column);

-- 1. Identify objects using the MAXDB mode
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE
FROM information_schema.ROUTINES
WHERE SQL_MODE LIKE '%MAXDB%';

-- 2. Recreate the stored procedure, removing the MAXDB mode
-- Export stored procedure definition
SHOW CREATE PROCEDURE database_name.procedure_name;

-- Modify SQL_MODE, remove MAXDB
-- Recreate the stored procedure

-- 1. Find objects using obsolete modes
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, SQL_MODE
FROM information_schema.ROUTINES
WHERE SQL_MODE REGEXP 'DB2|MSSQL|MYSQL323|MYSQL40|NO_AUTO_CREATE_USER|ORACLE|POSTGRESQL';

-- 2. Recreate the object, removing obsolete sql_mode flags

-- 1. Find views that use removed functions
SELECT table_schema, table_name, view_definition
FROM information_schema.views
WHERE view_definition REGEXP 'ENCODE|DECODE|ENCRYPT|PASSWORD|\\\\bAREA\\\\b|\\\\bASBINARY\\\\b'
 and table_schema not in ('information_schema','mysql','performance_schema','sys');

-- 2. Modify the view definition to use alternative functions
CREATE OR REPLACE VIEW view_name AS
SELECT ST_AREA(geom_column) AS area  -- Replace AREA()
FROM table_name;

-- 3. Similarly modify stored procedures, triggers, etc.

-- BEFORE MODIFICATION (Incorrect)
SELECT category, COUNT(*) 
FROM products 
GROUP BY category DESC;

-- AFTER MODIFICATION (Correct)
SELECT category, COUNT(*) 
FROM products 
GROUP BY category 
ORDER BY category DESC;

-- 1. Find table names containing 'FTS'
SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE table_name LIKE BINARY '%FTS%';

-- 2. Temporarily rename before upgrade (by changing any letter to lowercase)
RENAME TABLE myFTStable TO myFtStable;

-- 3. Can revert to original name after upgrade completion
RENAME TABLE myFtStable TO myFTStable;

-- 1. Use mysqlcheck to repair
mysqlcheck --check-upgrade --all-databases
mysqlcheck --fix-db-names --fix-table-names --all-databases

-- 2. Or use SQL commands
ALTER DATABASE `#mysql50#old_name` UPGRADE DATA DIRECTORY NAME;

-- 1. Find orphaned stored procedures
SELECT ROUTINE_SCHEMA, ROUTINE_NAME, 'is orphaned' 
FROM information_schema.routines 
WHERE NOT EXISTS (
    SELECT SCHEMA_NAME 
    FROM information_schema.schemata 
    WHERE ROUTINE_SCHEMA = SCHEMA_NAME
);

-- 2. Delete orphaned stored procedures
DROP PROCEDURE IF EXISTS non-existent_schema.procedure_name;
DROP FUNCTION IF EXISTS non-existent_schema.function_name;

-- Modify the object names to end with $
-- For example: change $example to $example$ or example
RENAME TABLE `$mytable` TO `$mytable$`;
-- or
RENAME TABLE `$mytable` TO `mytable`;

-- 1. Find indexes that are too large
SELECT table_schema, table_name, index_name, 
       GROUP_CONCAT(column_name) as columns
FROM information_schema.statistics
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY table_schema, table_name, index_name
HAVING SUM(sub_part) > 767 OR (SUM(sub_part) IS NULL AND COUNT(*) > 1);

-- 2. Drop indexes that are too large
ALTER TABLE table_name DROP INDEX index_name;

-- 3. Consider using prefix indexes or changing the row format
ALTER TABLE table_name ROW_FORMAT=DYNAMIC;
ALTER TABLE table_name ADD INDEX index_name (column_name(191));

-- Modify the stored procedure to use the full database.table syntax
-- BEFORE MODIFICATION
SELECT * FROM .mytable;

-- AFTER MODIFICATION
SELECT * FROM mydb.mytable;

-- 1. Find invalid foreign keys
SELECT 
    fk.TABLE_SCHEMA, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
    fk.REFERENCED_TABLE_SCHEMA, fk.REFERENCED_TABLE_NAME,
    t1.ENGINE as source_engine, t2.ENGINE as target_engine
FROM information_schema.KEY_COLUMN_USAGE fk
JOIN information_schema.TABLES t1 
    ON fk.TABLE_SCHEMA = t1.TABLE_SCHEMA AND fk.TABLE_NAME = t1.TABLE_NAME
JOIN information_schema.TABLES t2 
    ON fk.REFERENCED_TABLE_SCHEMA = t2.TABLE_SCHEMA 
    AND fk.REFERENCED_TABLE_NAME = t2.TABLE_NAME
WHERE fk.REFERENCED_TABLE_NAME IS NOT NULL
    AND t1.ENGINE != t2.ENGINE;

-- 2. Delete invalid foreign keys
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

-- 3. or recreate the foreign key after unifying the storage engine
ALTER TABLE table_name ENGINE=InnoDB;