MySQL Error seems unfixable: Index column size too large

Question

I'm getting: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

There are many questions about this here, I tried many of them. My context:

MySQL 8.0 on Amazon RDS
One specific table has a varchar(255) on a utf8mb4_unicode_ci charset
That table is set to ROW_FORMAT Compact

I already know the problem. That column uses 4*255 bytes but a COMPACT table can hold only 767.

What I already tried (neither one works):

alter table to change the column to varchar(100)
remove the specific index
drop the entire column
change ROW_FORMAT to DYNAMIC or COMPRESSED

I also tried to restore my oldest RDS Snapshot (7 days), and it doesn't work. This is very odd since the application was working yesterday and the days before.

Any help?

Edit:

SHOW FULL COLUMNS gives me this for the offending column:

     Field: reasonForNetPromoteScore
      Type: varchar(255)
 Collation: utf8mb4_unicode_ci
      Null: YES
       Key: MUL
   Default: NULL
     Extra: 
Privileges: select,insert,update,references
   Comment:

and SHOW INDEX gives me these:

*************************** 6. row ***************************
        Table: Registration
   Non_unique: 1
     Key_name: registration_reason_net_promote_score
 Seq_in_index: 1
  Column_name: reasonForNetPromoteScore
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 7. row ***************************
        Table: Registration
   Non_unique: 1
     Key_name: registration_reason_net_promote_score_nps
 Seq_in_index: 1
  Column_name: reasonForNetPromoteScore
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

You can make a copy table, alter the column to varchar(191), and copy the data from your original table into the new table. Then rename the tables so the new one has the name of the old table. — Bill Karwin
I can't make a copy of the table. Mysqldump doesn't work, a simple select doesn't work. I can't even do a show create table on the offending table. — Sérgio Lopes
This is a little bit odd. This error wouldn't block other operations on your table, this is an index specific error. Since you tried to restore a snapshot and the error persists my suspect is that an update on MySQL database may have caused this issue (yes it happens). One thing that you can try is to restore your snapshot in an older version of mysql (at least a minor version smaller). Another option is to contact aws support since it is an RDS database. Another sugestion is to add this question on dba.stackexchange.com — Jorge Campos

Rick James Rick James · Accepted Answer · 2019-05-14T03:13:37

Perhaps you need to upgrade to 8.0.12: From the Changelog:

----- 2018-07-27 8.0.12 General Availability & 2018-07-27 5.7.23 General Availability -- -- -----

For attempts to increase the length of a VARCHAR column of an InnoDB table using ALTER TABLE with the INPLACE algorithm, the attempt failed if the column was indexed.

If an index size exceeded the InnoDB limit of 767 bytes for COMPACT or REDUNDANT row format, CREATE TABLE and ALTER TABLE did not report an error (in strict SQL mode) or a warning (in nonstrict mode). (Bug #26848813)

MySQL Error seems unfixable: Index column size too large

1 Answers