How to fix “Error 1709 (HY000) at line x: Index column size too large. The maximum column size is 767 bytes”

Error 1709 (HY000) at line x: Index column size too large. The maximum column size is 767 bytes

While importing a MySQL database, you might encounter an error “Error 1709 (HY000) at line x: Index column size too large. The maximum column size is 767 bytes”.

If you are getting this error, you are on the right page. In this article, I will explain why this error occurs and how you can resolve it.

Cause of Error

The error occurs when you create tables with ROW_FORMAT=COMPACT with utf8 encoding which makes the table row too large. You will most likely get this error while restoring a database dump from MySQL server to a MariaDB server or vice versa. This happens because in MySQL server, the default row format for InnoDB tables is “COMPACT” whereas in MariaDB version 10.2.2 (or higher), the default row format is “DYNAMIC”.

Resolution of Error

To fix the above mentioned error, you need to set the default row format for InnoDB tables to “DYNAMIC”. This can be done in multiple ways.

Using phpMyAdmin

If you’re using phpMyAdmin to manage your MySQL or MariaDB server, click on Variables tab, search for “default row format” . You will see the default value as shown in the following screenshot:

To change the value, click on Edit link under Action and type either compact or dynamic as per your requirement. After making this change, restart your MySQL or MariaDB server.

Using MySQL Configuration File

If you want to change the settings using configuration file (e.g., my.cnf), just open the file in a text editor like nano and find the following line:

innodb-default-row-format=compact

Now change this line to the following:

innodb-default-row-format=dynamic

See the following screenshot for reference:

That’s it. Don’t forget to restart your MySQL or MariaDB server after making this change. You can now import your database and the error will no longer occur.