1

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 '19 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 '19 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 '19 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 '19 at 12:47
  • 1
    @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. – Sérgio Lopes Feb 9 at 13:23
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
  1. 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);
    
  2. shutdown mysqld-debug using mysqladmin shutdown -uroot -p

  3. Start mysql service.

    service mysqld start
    

Hope these steps help you.

  • 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. – Rabbit Oct 30 '19 at 23:03

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.