MySQL Error seems unfixable: Index column size too large

34
April 16, 2019, at 04:00 AM

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. My context:

  • MySQL 8.0 on Amazon RDS
  • One specific table has a 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.

What I already tried (neither one works):

  • alter table to change the column to varchar(100)
  • remove the specific index
  • drop the entire column
  • change ROW_FORMAT to DYNAMIC or COMPRESSED

I also tried to restore my oldest RDS Snapshot (7 days), and it doesn't work. This is very odd since the application was working yesterday and the days before.

Any help?

Edit:

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
READ ALSO
How do I pass my url params to async functions in javascript files separate from the route with Node.js?

How do I pass my url params to async functions in javascript files separate from the route with Node.js?

I'm just learning to code and I'm building an application for my businessI'm having an issue with passing url parameters on my show route

33
MySql combine records from same table

MySql combine records from same table

I'm attempting to preform a sql command to update rows, and combine rows with matching data

19
How to query between date and specific time range

How to query between date and specific time range

I need help on a query between date and specific time rangeFor example, a user entering the building from 1 Jan 2019 until 1 April 2019 and only from 7 PM until 10 PM for that date

29
How to invert or filter out the two employees that work on projects in other departments?

How to invert or filter out the two employees that work on projects in other departments?

So right now I am so close but right now the result table that is return only shows the employees that I don't want to see and I want to show the employees that only work on projects within their department

20