SOLVED: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Solved: Fixing MySQL/MariaDB ERROR 1071 – Specified Key Was Too Long (Max Key Length 767 Bytes)

Welcome back to Darren’s Tech Tutorials!

If you’ve recently tried to migrate a database—maybe you’re setting up a new server or importing a complex system like Moodle—only to be met with a frustrating wall of error text, you’re definitely not alone. The dreaded ERROR 1071 (42000) is a common stumbling block in the world of MySQL and MariaDB.

The full error reads:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

This problem usually pops up when your database is trying to create a table with an index (key) that exceeds the standard default size limit (767 bytes). But don’t worry—this is a super quick fix that requires running just three simple commands.

Let’s dive in and get that database imported successfully!

Understanding ERROR 1071

Why does this error happen? Historically, many database engines (specifically InnoDB, which powers both MySQL and MariaDB) used default settings that limited the length of index prefixes to 767 bytes.

However, modern applications and specific table structures (especially those using UTF8mb4 character sets) often require longer key lengths. We need to tell your database instance to use a more modern storage format that supports these larger keys.

The solution is to configure InnoDB to use the Barracuda file format and enable large_prefix support.

Before we start, grab your free resource: Don’t forget, we have a complete Linux cheat sheet available to help you navigate the command line with ease! [Access the free Linux cheat sheet here: http://eepurl.com/dkRNM9]

The Step-by-Step Fix

Follow these steps to quickly resolve the key length error and resume your database import.

Step 1: Log into Your Database Installation

First, you need to log in to your MariaDB or MySQL instance as a user with administrative rights (usually root).

Open your terminal and run the following command. You will be prompted for your database password.

mysql -u root -p

Step 2: Configure InnoDB Settings

Once you are successfully logged into the MariaDB or MySQL command line, we are going to run three specific SET GLOBAL commands. These commands update the operational settings of the InnoDB storage engine to allow for larger prefixes.

Note: For easy copy-pasting, execute the following three commands in sequence:

Command 1: Set File Format to Barracuda

This command enables the advanced Barracuda file format, which is required for supporting larger indexes.

SET GLOBAL innodb_file_format=Barracuda;

Command 2: Enable File Per Table

This ensures that the new file format settings are applied on a per-table basis, offering better performance and stability.

SET GLOBAL innodb_file_per_table=on;

Command 3: Enable Large Prefix Support

This is the critical command that explicitly enables indexes to exceed the old 767-byte limit.

SET GLOBAL innodb_large_prefix=on;

You should receive an Query OK, 0 rows affected message after each command.

Step 3: Exit and Rerun the Import

Now that you have updated the global InnoDB parameters, you need to exit the database command line session.

exit

With the new settings in place, you can now run your original database import command (the one that previously failed with ERROR 1071).

In our example, we were importing a moodle_back.sql file:

mysql -u root -p < moodle_back.sql

Type in your password when prompted.

You should see that the import runs completely, without returning the ERROR 1071!

Step 4: Verification (Optional)

To confirm the successful import, you can log back into your database and check the structure:

mysql -u root -p

Then run:

USE moodle;
SHOW TABLES;

If you see all your expected tables listed, congratulations! The index length error has been resolved, and your database is successfully migrated.

Wrapping Up

There you have it—a fast, straightforward fix for one of the most common migration errors in MySQL and MariaDB. This error can look scary, but by simply adjusting the InnoDB global variables, we bypass the old key length limits and get back to work quickly.

Did this tutorial save your day? Let us know in the comments below! If you found this guide helpful, smash that like button and subscribe to Darren’s Tech Tutorials for more clear, actionable guides to mastering technology.

Happy coding!