5

I've unsuccessfully been through the AWS forum and Stack Overflow trying to find a solution to the following error: Index column size too large. The maximum column size is 767 bytes

I am running a WordPress website with 1.5M records in the postmeta table. I recently added an index to the postmeta table, and all was testing ok. However I had an incident with my server today (botnet scan dwindled my RDS credits), and restarted both my Lightsail instance and my RDS MySQL instance. After the restart I noticed that the site wasn't working properly and upon further investigation found the postmeta table was returning the error Index column size too large. The maximum column size is 767 bytes.

I'm running on MySQL 8.0.20

The table is:

 Engine = InnoDB Charset = utf8mb4 Collation = utf8mb4_0900_ai_ci Row Format = Compact

Many existing "solutions" talk about recreating the table, however I need the data that's currently in the table. Unfortunately this issue is present in my oldest AWS RDS Snapshot, so back ups don't appear to be an option.

Every time I try run an ALTER or SELECT statement, I get the same error, Index column size too large. The maximum column size is 767 bytes. I've tried:

  • Changing the ROWFORMAT=DYNAMIC
  • Converting the charset and records to utf8
  • Changing the meta_value column from 255 to 191
  • Removing the custom index
  • Dumping the table

I can see that the default ROWFORMAT is now "DYNAMIC", however this table is still "COMPACT" from when it was running on MySQL 5.7

I've also tried updating the AWS RDS MySQL from 8.0.20 to 8.0.23, however the update fails cause it reports the table is corrupt in PrePatchCompatibility.log.
Ref: https://dba.stackexchange.com/questions/234822/mysql-error-seems-unfixable-index-column-size-too-large#answer-283266

There are some other suggestions about modifying the environment and file system, and running "innodb_force_recovery".
https://dba.stackexchange.com/questions/116730/corrupted-innodb-table-mysqlcheck-and-mysqldump-crash-server
However being an RDS instance, I don't have access to this lower level of the instance.

I suspect this issue is the column length and utf8mb4, however my main priority is getting the data from the currently in the table.
I also understand that changing the ROWFORMAT to DYNAMIC should fix this issue - however getting the same error.

Ref: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

I have also tried the "RDS Export to S3" option with no luck.

Please help, I'm lost as to what else to try.

Improve this question
1
  • MySQL says it is fixed in 8.0.22 and I'm currently on 8.0.20, but when i try to update in AWS RDS it fails the pre-check reporting the table is corrupt. bugs.mysql.com/bug.php?id=99791 Commented Apr 22, 2021 at 15:38

2 Answers 2

Reset to default
8

I had, and solved, the same problem. Here's the situation.

In legacy MySQL table formats, the maximum size of an index on a VARCHAR or blob column is 767 bytes (not characters). These wp_somethingmeta WordPress tables have key columns (like meta_key) with the VARCHAR(255) datatype. When utf8mb4 is the character set each character can take up to four of those 767 bytes. that means indexes have to be defined as prefix. meta_key(191).

What makes a MySQL table into a legacy table?

  1. MyISAM access method, or
  2. An old version (5.5, early 5.6) of MySQL which only supports the older InnoDB Antelope ondisk table format and not the newer Barracuda file format, or
  3. InnoDB and the ROW_FORMAT is COMPACT (or REDUNDANT).

So, to get away from prefix indexes on the varchar(255) columns, the table needs to be InnoDB and use the DYNAMIC (or COMPRESSED) ROW_FORMAT.

There's no need to rebuild a legacy table from scratch. You can convert it by saying

ALTER TABLE whatever ENGINE=InnoDB, ROW_FORMAT=DYNAMIC;

Then you stop having the prefix-key (191) issue.

Back up your database before you do this kind of thing. You knew that.

And, upgrade to a recent version of MySQL or MariaDB. Seriously. MySQL 5.6 long-term support ended on 1-February-2021, and the newer versions are better. (GoDaddy! I'm looking at you.)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I was able to overcome this same issue by added the ROW_FORMAT=DYNAMIC for each CREATE TABLE statement when importing my Wordpress database .sql file to a new database.
This solved our issue whilst upgrading RDS in AWS.
0

WordPress' wp_postmeta table normally has an index on its meta_key column, which is varchar(255). That's too long.

First, drop the index that is too large.

SHOW CREATE TABLE wp_postmeta; -- to verify the name of the index ALTER TABLE wp_postmeta DROP KEY meta_key;

I'm assuming the name of the index will be meta_key, which is the default name for an index on that column. But double-check the index name to be sure.

Then, add the index back, but make it a prefix index such that it's not larger than 767 bytes.

Since you're using utf8mb4, which allows multibyte characters up to 4 bytes per character, you can define the index with a prefix length of floor(767/4), or 191.

ALTER TABLE wp_postmeta ADD KEY (meta_key(191));

That index length will be permitted by the COMPACT row format, and it should be more than long enough to make the index just as useful as it was before. There's virtually no chance that you have a lot of meta key values that have the same leading characters and differ only after the 191th character.

Another alternative is to create a new table:

CREATE TABLE wp_postmeta_new ( meta_id BIGINT UNSIGNED, post_id BIGINT UNSIGNED, meta_key VARCHAR(255), meta_value LONGTEXT, KEY (post_id), KEY (meta_key) ) ROW_FORMAT=DYNAMIC;

Double-check that it created this table with DYNAMIC rowformat.

Copy all the old data into it:

INSERT INTO wp_postmeta_new SELECT * from wp_postmeta;

Then swap the tables:

RENAME TABLE wp_postmeta TO wp_postmeta_old, wp_postmeta_new TO wp_postmeta;

I'm assuming there are no new posts being created while you're doing this operation. You'd have to ensure no one is adding content to this WP instance so you don't miss any data in the process.

Improve this answer

5 Comments

Thanks for the response Bill. Unfortunately running SHOW CREATE TABLE wp_postmeta;, ALTER TABLE wp_postmeta DROP KEY meta_key;, ALTER TABLE wp_postmeta DROP INDEX meta_key; and ALTER TABLE wp_postmeta DROP INDEX bk1; all result in the same error: SQL Error [1709] [HY000]: Index column size too large. The maximum column size is 767 bytes..
Do you have another index in this table that is also too large?
There are 4 indexes. Each gives the same error when run. ALTER TABLE wp_postmeta DROP INDEX `PRIMARY`;, ALTER TABLE wp_postmeta DROP INDEX bk1;, ALTER TABLE wp_postmeta DROP INDEX meta_key;, ALTER TABLE wp_postmeta DROP INDEX post_id;. I have also tried creating the new table and inserting the values per your suggestion, however also getting the same error SQL Error [1709] [HY000]: Index column size too large. The maximum column size is 767 bytes., which happens whenever I try to SELECT from the wp_postmeta. Great troubleshooting suggestions though, thanks.
You will need to drop all four indexes in one ALTER TABLE statement. Check the syntax or examples of ALTER TABLE in the manual. You can do multiple changes in one statement, separated by commas.
Same error running: ALTER TABLE wp_postmeta DROP INDEX `PRIMARY`, DROP INDEX bk1, DROP INDEX meta_key, DROP INDEX post_id;. SQL Error [1709] [HY000]: Index column size too large. The maximum column size is 767 bytes.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.