I'm trying to migrate from MySQL 5.7 to MySQL 8.0 for my application's database. However, the precheck failed with an incompatibility error "25," stating that tables with redundant row formats cannot have an index larger than 767 bytes. The error message also mentions that only 50 indexes are displayed.
Is there a query or method to identify the problematic tables in my database? I need to find out which tables in my production environment may be affected by this issue.
You can use information_schema.tables.
This query tells you how many different row formats per database
SELECT table_schema,row_format,COUNT(1) table_count
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql','sys')
GROUP BY table_schema,row_format;
To see your tables that have ROW_FORMAT=REDUNDANT, run this query
SELECT table_schema,table_name
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql','sys')
AND row_format='REDUNDANT';
If these are the tables you want, you can convert them to ROW_FORMAT=Dynamic.
Here is the query to show you the ALTER TABLE commands to change row_format to Dynamic
SELECT
CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Dynamic;') CMDs
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql','sys')
AND row_format='REDUNDANT';
(This is historical trivia -- not really an Answer; see Rolando's Answer.)
From the changelogs:
From 5.0.3 (2005)
InnoDB: Introduced a compact record format that does not store the number of columns or the lengths of fixed-size columns. The old format can be requested by specifying ROW_FORMAT=REDUNDANT. The new format (ROW_FORMAT=COMPACT) is the default. The new format typically saves 20% of disk space and memory.
As late as 8.0.26 (2021), I still see subtle bugs in Redundant being fixed.
