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)
SHOW CREATE TABLE
also doesn't work (same error). But I edited the question with my last working backup DDL.