MySQL Error seems unfixable: Index column size too large

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 ver

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.

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

2013 ?        Ssl   /usr/sbin/mysqld

-
stop mysql service

-
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

-
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';

-
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

-
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);

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

-
Start mysql service.

service mysqld start

Hope these steps help you.

StackExchange Database Administrators Q#234822, answer score: 2