How to drop RDS MySQL table with error 1709?

The error you're encountering, "Error Code: 1709. Index column size too large. The maximum column size is 767 bytes", is due to a limitation in MySQL for the maximum index length in InnoDB tables when using the COMPACT row format. The utf8mb4 character set uses a maximum of 4 bytes per character, so if you have a VARCHAR(255) column (which could be up to 1020 bytes), it exceeds the 767-byte limit.

Here's how you can drop the table:

1. Change the Global innodb_large_prefix setting:

First, you need to change the innodb_large_prefix setting to ON. This can be done by modifying the parameter group associated with your RDS instance.

2. Change the innodb_file_format:

Next, you have to set innodb_file_format to Barracuda. This can also be done in the parameter group.

3. Change the innodb_file_per_table:

Set innodb_file_per_table to 1. This can also be done in the parameter group.

4. Restart the RDS instance:

After applying these changes, you need to reboot your RDS instance for the changes to take effect.

5. Drop the table:

Now, you should be able to drop the table using the regular DROP TABLE command:

DROP TABLE your_table_name;

Remember to replace your_table_name with the actual name of your table.

After dropping the table, you can revert the changes to the parameter group if you wish.

This error often occurs when trying to use utf8mb4 character set without setting up the appropriate parameters. If you plan to use utf8mb4 in the future, you should consider keeping these settings to avoid similar problems.