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. No luck. My context:

• MySQL 8.0.13 on Amazon RDS
• One specific table has a one specific column as 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 indexes.

What I already tried (neither one works):

• alter table to change the column to varchar(100)
• alter table to change teh column charset
• remove the specific index
• drop the entire column
• change ROW_FORMAT to DYNAMIC or COMPRESSED
• dump or select data on the table

All above always return the same error. The table seems untouchable. Example:

> alter table Registration drop column reasonForNetPromoteScore;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

And:

> repair table Registration;
+---------------------------------+--------+----------+--------------------------------------------------------------------+
| Table                           | Op     | Msg_type | Msg_text                                                           |
+---------------------------------+--------+----------+--------------------------------------------------------------------+
| xxxxxxxxproduction.Registration | repair | Error    | Index column size too large. The maximum column size is 767 bytes. |
| xxxxxxxxproduction.Registration | repair | Error    | Table 'xxxxxxxxproduction.Registration' doesn't exist              |
| xxxxxxxxproduction.Registration | repair | error    | Corrupt                                                            |
+---------------------------------+--------+----------+--------------------------------------------------------------------+

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

Since I'm using RDS, I can't try to restore to a different mysql version. I can't access the physical machine to do more advanced things.

---

A little bit more technical info:

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

SHOW CREATE TABLE on an old backup gives this:

REATE TABLE `Registration` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `course_id` bigint(20) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `finishedAt` datetime DEFAULT NULL,
  `certificateStartedDate` datetime DEFAULT NULL,
  `certificateFinishDate` datetime DEFAULT NULL,
  `authenticityCode` varchar(255) DEFAULT NULL,
  `lastAccessDate` datetime DEFAULT NULL,
  `netPromoteScore` int(11) DEFAULT NULL,
  `sharedLinkedin` bit(1) NOT NULL DEFAULT b'0',
  `lastSectionSaw_id` bigint(20) DEFAULT NULL,
  `finished` bit(1) DEFAULT b'0',
  `state` enum('IN_PROGRESS','PAUSED','BOOKMARKED','FINISHED','NOT_VISIBLE') NOT NULL DEFAULT 'IN_PROGRESS',
  `reasonForNetPromoteScore` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_course_on_registration` (`user_id`,`course_id`),
  KEY `registration_user` (`user_id`),
  KEY `registration_course` (`course_id`),
  KEY `registration_reason_net_promote_score` (`reasonForNetPromoteScore`),
  KEY `registration_reason_net_promote_score_nps` (`reasonForNetPromoteScore`,`netPromoteScore`),
  CONSTRAINT `registration_course` FOREIGN KEY (`course_id`) REFERENCES `Course` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `registration_user` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=65651 DEFAULT CHARSET=latin1

(I can't run SHOW CREATE TABLE on this table anymore, gives the same error)

Answer 1

I ran into the same issue where i am not able to do any operations on the table even drop table command did not work. I am on version 8.0.17.

But below option helped me.

Edit data dictionary by starting mysql in debug mode.

1. use ps -ax|grep mysqld to find the command line that was used to launch MySQL.

   2013 ?        Ssl   /usr/sbin/mysqld
   

2. stop mysql service

3. start mysql in debug mode. Now open a new window for logging into mysql, incase if your window is taking long time to start mysql in debug mode.

   /usr/sbin/mysqld-debug
   

4. Login to mysql and execute below command (for more info in the MySQL documentation section 14.1 Data Dictionary Schema)

   mysql> SET SESSION debug='+d,skip_dd_table_access_check';
   

5. Now do select t.name,s.name,t.id,t.row_format from mysql.tables t, mysql.schemata s where t.name='Registration' and t.schema_id=s.id and s.name in ('db_1') \G

The output will show something like this:

*************************** 1. row ***************************
name: d1
name: db_1
id: 100
row_format: Compact

6. now you can change the row_format for those table using the id above.

   mysql> set sql_log_bin=0; 
   mysql> update mysql.tables set row_format="Dynamic" where id in (100);
   

7. shutdown mysqld-debug using mysqladmin shutdown -uroot -p

8. Start mysql service.

   service mysqld start
   

Hope these steps help you.

Answer 2

related bug

Need to wait for mysql 8.0.22 on RDS and try to upgrade