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)

• Akina: Why, for what purposes, do You need this index? And show full table's DDL (its CREATE TABLE) instead of particular data.
• Sérgio Lopes: I don't need this index anymore. But I can't remove it (same error). I don't have the exact DDL because SHOW CREATE TABLE also doesn't work (same error). But I edited the question with my last working backup DDL.
• Akina: The only way to fix this problem I see is to make backup of this table, edit it by removing this index, then drop table and restore it from edited backup. Or something similar (for example, backup structure + create edited empty table + copy data + replace table, etc.). If there are some tables referenced to this table they would be backed up and re-created too (if not, integrity check do not allow).
• Sérgio Lopes: @Akina The problem is both a simple select and mysqldump fail with the same error. I can't backup the data from this table.
• Sérgio Lopes: @JonVaughan Nothing happy to report, unfortunately. AWS support was completely useless. Even as paying support, they didn't respond for days. They didn't have any new insight to this problem, not even answered why the database was working the day before and I couldn't restore their own snapshot. AWS failed miserably on this one. In the end, I restored an old manual backup I had, lost a few days of data and lost confidence in RDS. Since then, I have a daily backup job that exports the entire database and reimport it on a different machine. Can't trust RDS snapshot feature.

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.

• Rabbit: After step 3, open a new window for logging into mysql, incase if your window is taking long time to start mysql in debug mode.

Answer 2

related bug

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

• Peter Murray: I have two tables in a Confluence installation that are in such a stuck state. I haven't been tracking AWS' release schedule on MySQL versions...version 8.0.21 is the latest I see available. (So close!) Any idea on when AWS might release the upgrade to RDS?
• Dzmitry Bahdanovich: Unfortunately, It's impossible for us to upgrade the RDS mysql where one of the table is corrupted in such a way