Error Reported During Table Creation for an RDS for MySQL 5.6 DB Instance

Scenario

An error is reported when a table creation statement is executed on an RDS for MySQL 5.6 DB instance.

Index column size too large. The maximum column size is 767 bytes.

Possible Causes

When you create tables for an RDS for MySQL 5.6 instance, use indexes based on the following rules:

• For a single-field index, the field length cannot exceed 767 bytes.
• For a composite index, the length of each field cannot exceed 767 bytes, and the total length of all fields cannot exceed 3,072 bytes.
• Use the 4-byte character set utf8mb4 for table creation.

If the maximum length of an index is 767 bytes, the length allowed for a varchar field is 191.75 bytes (767/4=191.75). If the length of the varchar field in the table creation statement exceeds this value, an error is reported.

Solution

1. Ensure that the value of innodb_large_prefix is ON.
2. Create a table by referring to the rules in Possible Causes.