Understanding and Fixing “Index column size too large. The maximum column size is 767 bytes” Error in MySQL Replication

Understanding the issue

Let's start by first understanding how MySQL stores indexes physically. This behavior is controlled by the row format of a table and can be changed for each table. You can run the following command to determine the row format of any table

mysql> select TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT,TABLE_COLLATION from information_schema.tables where table_name = 'orch_heartbeat'\G

*************************** 1. row ***************************
   TABLE_SCHEMA: test
 	TABLE_NAME: orch_heartbeat
 	ROW_FORMAT: Dynamic
TABLE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)        

Innodb Supports 4 types of row format?

• REDUNDANT
• COMPACT
• DYNAMIC
• COMPRESSED

You can read more about these here https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

The important detail to note here is how they store the indexes. Tables that use the REDUNDANT or COMPACT row format store the first 768 bytes of variable-length column values (VARCHAR, VARBINARY, and BLOB and TEXT types) in the index record within the B-tree node, with the remainder stored on overflow pages

Dynamic row format is similar to COMPACT row format but supports index key prefixes up to 3072 bytes instead of 768 bytes in COMPACT.

COMPRESSED row format is similar to Dynamic row format with additional compressive capabilities; hence, it can store index key prefixes up to 3072 bytes

There is a bug where tables created in versions earlier than MySQL 8, which use the COMPACT or REDUNDANT row format and do not have the row format explicitly set in the table DDL, allow users to create un-prefixed indexes on fields that exceed the maximum column size of 767 bytes. https://bugs.mysql.com/bug.php?id=99791

The default row format in MySQL 5.7+ is DYNAMIC, but prior to that, it was COMPACT. So this is more likely to affect tables that were originally created on versions earlier than 5.7 or 5.7 databases where the innodb_default_row_format value has been changed to COMPACT or REDUNDANT.

Now, the bug is fixed for new indexes that you will create on an 8.0 cluster, but I think the issue is not completely solved as people have reported that it is still there. The only hint we got from Oracle is that if there are existing indexes and the charset changes so that the existing index is now bigger than 767 bytes, the issue can happen again.

In my experience of upgrading MySQL from 5.7 to 8.0, I found that the issue can happen for any index whose length is more than 767 bytes and the row format of the table is COMPACT or REDUNDANT

Solution

The issue is unfixable once you start getting the error. It can only be fixed on nodes where the index is present but MySQL is not complaining, which would be the case if you have recently created the index or recently upgraded the node.

So if you don’t have a node where MySQL is not complaining about the error, you have to build one from the backup.

After you have the node, you have two options:

1. Drop the index causing the table to be inaccessible. (DROP INDEX <INDEX_NAME> ON <TABLE_NAME>; )
2. Change the row format of the table to DYNAMIC. (ALTER TABLE <TABLE_NAME> ROW_FORMAT=DYNAMIC;)