You're importing a database dump, running a Laravel migration, or activating a cpanel" class="auto-link">WordPress plugin, and it dies halfway with ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes. Half your tables exist, the rest don't, and the site is broken. The good news: this is a sizing problem, not data corruption. Once you understand the byte math, it takes about ten minutes to fix.

Symptom: What You'll Actually See

The error shows up in a few different places depending on what triggered it:

  • phpMyAdmin import stops partway with #1071 - Specified key was too long; max key length is 767 bytes.
  • Laravel throws SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long during php artisan migrate, usually on the users or password_resets table.
  • WordPress plugins activate "successfully" but their tables are missing, and you'll find the 1071 error in wp-content/debug.log.
  • On newer servers you might see max key length is 3072 bytes instead. Same family of problem, different limit.
  • If it says max key length is 1000 bytes, the table is using MyISAM, not InnoDB.

Cause: Indexes Have a Byte Budget, and utf8mb4 Eats It Fast

MySQL limits how many bytes a single index can cover, not characters. When you index a VARCHAR(255) column, MySQL reserves space for the worst case: every character using the maximum bytes its charset allows.

With utf8mb4 (the modern default, needed for emoji and many Indian-language characters), each character can take up to 4 bytes. So:

VARCHAR(255) x 4 bytes = 1020 bytes   -> over the 767 limit
VARCHAR(191) x 4 bytes = 764 bytes    -> fits

That's exactly why you see 191 all over WordPress and Laravel code. It's the biggest utf8mb4 column that fits in 767 bytes.

So where does the 767 limit come from? It applies to InnoDB tables using the older COMPACT or REDUNDANT row formats. Tables using DYNAMIC or COMPRESSED row formats get 3072 bytes instead. MySQL 5.7.7+ and MariaDB 10.2.2+ default to DYNAMIC, which is why this mostly bites people on:

  • Older servers still running MySQL 5.6 or MariaDB 10.1 and earlier
  • Dumps that hard-code ROW_FORMAT=COMPACT in their CREATE TABLE statements
  • Servers where someone set innodb_default_row_format=compact in my.cnf
CharsetMax bytes per charLargest indexable VARCHAR (767 limit)Largest indexable VARCHAR (3072 limit)
latin117673072
utf8 / utf8mb332551024
utf8mb44191768

Fix: Step by Step

Step 1: Check your MySQL version and default row format

On a VPS, SSH in and run:

mysql -e "SELECT @@version, @@innodb_default_row_format;"

On cPanel shared hosting, open phpMyAdmin, click the SQL tab and run the same SELECT. If @@innodb_default_row_format throws "unknown system variable", you're on MySQL 5.6 or older, which is the root of the problem.

Then check how the existing tables are stored:

SELECT TABLE_NAME, ENGINE, ROW_FORMAT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database';

Step 2 (cleanest fix): Upgrade MySQL/MariaDB

If you control the server, upgrading is the real fix. On a cPanel VPS, go to WHM » SQL Services » MySQL/MariaDB Upgrade. Take a full backup first, and check that your apps support the new version. We've covered the details in our guide to upgrading MySQL/MariaDB in WHM. After the upgrade, new tables default to DYNAMIC and the 3072-byte limit, and most 1071 errors just go away.

Step 3: Convert existing tables to DYNAMIC

If the server is new enough but the tables were created with an old row format, convert them:

ALTER TABLE wp_example_plugin_table ROW_FORMAT=DYNAMIC;

On MySQL 5.6 this only works if these are set in /etc/my.cnf under [mysqld], followed by a MySQL restart:

innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1

These three options were removed in MySQL 8.0 and MariaDB 10.3+ because the behaviour is now on by default. Don't add them there, or MySQL may refuse to start.

Step 4: Fix the dump before importing

If you're importing a .sql file and it keeps failing, check whether it forces an old row format:

grep -c "ROW_FORMAT=COMPACT" backup.sql

If it does, swap it out, or add DYNAMIC where no row format is set:

sed -i 's/ROW_FORMAT=COMPACT/ROW_FORMAT=DYNAMIC/g' backup.sql
sed -i 's/ENGINE=InnoDB DEFAULT/ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT/g' backup.sql

Drop the half-imported tables (or the whole database) before you re-import, so you aren't mixing old and new structures.

Step 5 (shared hosting fix): Shorten the index, not the data

On shared cPanel hosting you can't change server settings, so fix the schema instead. You have two options:

  1. Use a prefix index. Index only the first 191 characters while the column keeps its full length:
    ALTER TABLE wp_example DROP INDEX meta_key,
      ADD INDEX meta_key (meta_key(191));
    This works for normal indexes. For a UNIQUE index, values that only differ after character 191 will collide, so think before you do it.
  2. Shrink the column. If nothing realistic will ever exceed 191 characters (emails, slugs, tokens), just make it VARCHAR(191):
    ALTER TABLE users MODIFY email VARCHAR(191) NOT NULL;

For columns that only ever hold hex hashes or ASCII tokens, you can also store them as CHARACTER SET ascii. One byte per character means a full VARCHAR(255) index fits easily.

Laravel-specific fix

Laravel creates string() columns as VARCHAR(255). On older MySQL, add this to app/Providers/AppServiceProvider.php:

use Illuminate\Support\Facades\Schema;

public function boot(): void
{
    Schema::defaultStringLength(191);
}

Then roll back the failed migration (or drop the partial tables) and run php artisan migrate again.

The 3072-byte and 1000-byte variants

If you hit 3072 bytes, it's usually a composite index: four or more utf8mb4 VARCHAR(255) columns in one key. Use prefix lengths on some of the columns, or rethink whether every column needs to be in that index.

If you hit 1000 bytes, the table is MyISAM. Convert it to InnoDB, which you should do anyway for crash safety:

ALTER TABLE old_table ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

Prevention: Don't Hit It Again on the Next Migration

  • Match versions before you migrate. Moving a dump from MySQL 8 down to an older MariaDB is the most common way to trigger 1071 (and its cousin, the utf8mb4_0900_ai_ci collation error). Check both sides first.
  • Keep indexed strings at 191 or below in your own schemas unless you're sure every server will be MySQL 5.7+ / MariaDB 10.2+.
  • Don't hard-code ROW_FORMAT=COMPACT in table definitions or plugin install scripts.
  • Test imports on staging first. A failed import on a staging copy costs nothing. A failed import on production leaves you with half a database.
  • Keep the server current. MySQL 5.6 and MariaDB 10.1 have been end-of-life for years. Upgrading fixes this error and closes security holes too.
Quick rule of thumb: if the server is old, upgrade it. If you can't touch the server, shorten the index to 191. Only change the column charset as a last resort.

Frequently Asked Questions

Will switching my database to utf8 instead of utf8mb4 fix this?

Technically yes, since utf8mb3 lets you index 255 characters. But you'll lose emoji support, and some characters will get stored as ??? or fail to insert. Use a prefix index or VARCHAR(191) instead.

Does a prefix index make queries slower?

Barely, for most sites. MySQL uses the first 191 characters to narrow the search and then checks the full value. Covering-index optimisations won't apply to that column, but on typical WordPress or Laravel workloads you won't notice.

Did the failed import damage my existing data?

No. MySQL rejects the CREATE TABLE or ALTER TABLE and stops. Tables created before the error are fine but may be incomplete. Drop the database and re-import cleanly after you fix the cause.

I'm on SkyServer shared hosting. Can you change the MySQL setting for me?

Row-format defaults are server-wide, so they can't be changed for one account. Run the version check from Step 1 first. If an old dump still fails, open a support ticket with the exact error line and the table name, and we'll help you adjust the schema.

Why does the same dump import fine on my local machine?

Your local setup (XAMPP, Docker, Laragon) probably runs a newer MySQL or MariaDB with the 3072-byte limit. Compare SELECT @@version; on both machines. The version gap almost always explains it.