0

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)

  • Why, for what purposes, do You need this index? And show full table's DDL (its CREATE TABLE) instead of particular data. – Akina Apr 15 at 13:05
  • 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. – Sérgio Lopes Apr 15 at 14:21
  • 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). – Akina Apr 16 at 4:55
  • @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 Apr 16 at 12:47
  • Does file_per_table is set? – Akina Apr 16 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.