How to fix file format in tables after upgrade of mysql 5.6 -> mysql 8.0.23 in AWS RDS?

Question

I upgraded mysql 5.6 -> mysql 8.0.23 in AWS RDS. There is a warning:

22. Creating indexes larger than 767 bytes on tables with redundant row format might cause the tables to be inaccessible. You have one or more tables with redundant row format. Creating an index larger than 767 bytes might cause the table to be inaccessible (only 50 tables are shown). More Information: https://bugs.mysql.com/bug.php?id=99791

[table_schema, table_name] <schema, table_name> - Consider restoring the DB instance from a snapshot creating before upgrading, and change the row_format of the tables to dynamic.

So in the database, there are tables that have row format equals COMPACT, DYNAMIC or FIXED. As I understand I have to change to dynamic from compact.

What is proper way to change all tables format to DYNAMIC? If I ALTER TABLE table_name ROW_FORMAT=DYNAMIC; the table will have BARACUDA in mysql 8? Or there is another way to fixed this warning?

Answer 1

One way to fix the warning is to make sure there are no indexes on columns that may be longer than 767 bytes.

For example: VARCHAR(255) is a common choice for a string column, but if you use the utf8mb4 character set, this can be up to 255 * 4 or 1020 bytes. You would need to shorten this column to VARCHAR(191) so the max length of the string is 191 * 4 or 764 bytes.

Or simply drop the index on any column that is VARCHAR(255).

Also consider compound indexes. If the total bytes of the indexed columns is over 767 bytes, then it will cause the warning. E.g. a compound index on two columns each VARCHAR(100) would be too large.

You can also change the ROW_FORMAT=DYNAMIC as you said. This implicitly makes the table stored in Barracuda format. The row formats REDUNDANT and COMPACT belong to Antelope, whereas row formats DYNAMIC and COMPRESSED belong to Barracuda. By changing the row format to DYNAMIC, the table becomes Barracuda by definition.

Also, the size limit is larger on MySQL 8.0, it's 3072 bytes, which means VARCHAR(767) could in theory have an index even if it uses utf8mb4 character set. But at that point, I wonder if such a long string really needs to be indexed. I mean, are you going to run a query to search for a 700-character string?