Fix MySQL utf8mb4 Index Length Error: Root Causes and Minimal Checks

Quick Answer

• Conclusion: The Specified key was too long; max key length is 767 bytes error occurs because utf8mb4 uses up to 4 bytes per character, so a VARCHAR(255) index column exceeds the 767-byte InnoDB limit. The minimal fix is to reduce indexed VARCHAR columns to 191 characters or use a prefix index.
• First checks: Verify your current character set with SHOW VARIABLES LIKE 'character_set%', check the table's index definitions with SHOW INDEX FROM your_table, and confirm your MySQL version (5.7+ supports innodb_large_prefix).
• Minimal fix: Run ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; and change oversized indexed columns to VARCHAR(191) or add a prefix index like INDEX (column_name(191)).
• Environment boundary: This error applies to MySQL 5.5–5.6 with default settings; MySQL 5.7+ with innodb_large_prefix=ON and innodb_file_format=Barracuda supports up to 3072-byte indexes, but you must still verify your configuration.

What Problem It Solves

MySQL's legacy utf8 character set stores a maximum of 3 bytes per character. This covers most Latin, Greek, Cyrillic, and CJK characters, but it cannot store 4-byte characters such as emoji, some rare Chinese characters, and certain mathematical symbols. When you try to insert such data, MySQL raises:

Incorrect string value: '\xF0\x9F\x8E\xB6...' for column 'tweet_text' at row 1

The solution is to switch to utf8mb4, which is a superset of utf8 and supports the full Unicode range (up to 4 bytes per character). However, this creates a second problem: because each character can now occupy 4 bytes, index key lengths that were safe under utf8 suddenly exceed MySQL's limits.

This article focuses on diagnosing and resolving the index length error that appears during or after migrating to utf8mb4.

When This Error or Setup Appears

The index length error typically appears in these situations:

1. Migrating an existing database from utf8 to utf8mb4 — you run ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 and the operation fails.
2. Creating a new table with utf8mb4 and an index on a VARCHAR(255) column.
3. Adding an index to an existing utf8mb4 table where the combined indexed column lengths exceed the limit.
4. Restoring a dump that was created with utf8mb4 settings into a server with conservative index limits.

The error message looks like:

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

This happens because InnoDB's default index key limit is 767 bytes. With utf8mb4, a VARCHAR(255) column needs 255 × 4 = 1020 bytes, which exceeds the limit.

Minimal Working Configuration

Here is a minimal my.cnf configuration that enables utf8mb4 as the default character set:

INI
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

For MySQL 5.7+ with large index support, add:

INI
[mysqld]
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON

After editing the configuration, restart MySQL:

BASH
sudo systemctl restart mysql

Then verify the settings:

SQL
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

Important: These settings only affect newly created databases and tables. Existing tables must be converted explicitly.

Parameters and Environment Variables

The following configuration directives control character set behavior and index limits:

Version compatibility notes:

• In MySQL 5.5 and 5.6, default-character-set under [mysqld] is deprecated and may cause startup failure. Use character-set-server instead.
• In MySQL 5.7+, innodb_large_prefix is enabled by default, but only applies to tables using the DYNAMIC or COMPRESSED row format.
• In MySQL 8.0, innodb_large_prefix is removed; the 3072-byte limit is always active, but the default row format is DYNAMIC.

Root Cause Analysis

The error has two layers:

Layer 1: The 767-byte index limit

InnoDB historically limited index key length to 767 bytes. This is a hard limit in the storage engine. When you define an index on a VARCHAR(255) column:

• With utf8 (3 bytes max per char): 255 × 3 = 765 bytes — fits under 767.
• With utf8mb4 (4 bytes max per char): 255 × 4 = 1020 bytes — exceeds 767.

The fix is either to reduce the column length or to enable larger index prefixes.

Layer 2: Configuration changes don't affect existing tables

When you change character-set-server in my.cnf, MySQL applies the new setting only to:

• Newly created databases
• Newly created tables
• New connections (for the client-side character set)

Existing tables retain their original character set. You must explicitly convert them:

SQL
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This command rebuilds the table and converts all CHAR, VARCHAR, and TEXT columns. However, it does not automatically adjust index lengths. If an index was defined on a VARCHAR(255) column, the conversion will fail with the index length error.

Common Errors and Fixes

Error 1: Incorrect string value: '\xF0\x9F\x8E\xB6...'

Cause: The column, table, or connection is still using utf8, which cannot store 4-byte characters.

Fix: Ensure all three layers use utf8mb4:

SQL
-- Convert the table
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Ensure the connection uses utf8mb4
SET NAMES utf8mb4;

Also verify the database default:

SQL
ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Error 2: Specified key was too long; max key length is 767 bytes

Cause: An indexed VARCHAR column exceeds the byte limit under utf8mb4.

Fix options (choose one):

Option A — Reduce column length to 191:

SQL
ALTER TABLE your_table MODIFY column_name VARCHAR(191) NOT NULL;

Option B — Use a prefix index:

SQL
ALTER TABLE your_table ADD INDEX idx_name (column_name(191));

Option C — Enable large index prefixes (MySQL 5.7+):

INI
[mysqld]
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON

Then convert the table to use the DYNAMIC row format:

SQL
ALTER TABLE your_table ROW_FORMAT=DYNAMIC;

Error 3: MySQL fails to start after adding utf8 settings

Cause: Using default-character-set under [mysqld] in MySQL 5.5/5.6.

Fix: Remove that directive and use character-set-server instead:

INI
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Error 4: Character set mismatch between client and server

Cause: The client connection uses a different character set than the server.

Fix: Specify the character set in the connection string or use SET NAMES:

BASH
mysql --default-character-set=utf8mb4 -u username -p database_name

Or in a connection string (e.g., for PHP PDO):

PHP
$pdo = new PDO(
    'mysql:host=localhost;dbname=your_db;charset=utf8mb4',
    $user,
    $pass
);

Production Notes and Security Checks

When deploying utf8mb4 in production, keep these points in mind:

1. Index length planning: Before converting, audit all indexes. Use this query to find indexed columns that may exceed the limit:

SQL
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.STATISTICS
JOIN INFORMATION_SCHEMA.COLUMNS 
    USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
WHERE TABLE_SCHEMA = 'your_database'
    AND CHARACTER_MAXIMUM_LENGTH > 191;

2. Client tools may override settings: MySQL Workbench, phpMyAdmin, and other GUI tools often set their own connection character set. Always verify with SHOW VARIABLES LIKE 'character_set_connection'; after connecting.

3. Backup before migration: Run a full backup before executing ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4. The conversion rebuilds the table and can take significant time on large datasets.

4. Security practices:

   • Restrict file permissions on my.cnf to 640 or tighter: sudo chmod 640 /etc/mysql/my.cnf
   • Use a database user with minimal privileges for application connections
   • Never store credentials in the configuration file; use environment variables or a secrets manager

5. Test in staging first: The conversion can fail midway on large tables. Test the exact ALTER TABLE statements on a staging copy before running them in production.

FAQ

Q: Why can't MySQL's utf8 character set store emoji?

A: MySQL's utf8 (also called utf8mb3) supports only up to 3 bytes per character. Emoji and many other symbols require 4 bytes in UTF-8 encoding. The utf8mb4 character set is the only MySQL character set that supports the full Unicode range, including 4-byte characters.

Q: How do I avoid index length errors when converting a table from utf8 to utf8mb4?

A: Before running the conversion, reduce indexed VARCHAR columns to 191 characters (since 191 × 4 = 764 bytes, under the 767-byte limit), or use prefix indexes. Alternatively, on MySQL 5.7+, enable innodb_large_prefix and set the table row format to DYNAMIC to allow indexes up to 3072 bytes.

Q: After changing my.cnf, why can't existing tables still store emoji?

A: Configuration file changes only affect newly created databases and tables. Existing tables keep their original character set. You must explicitly convert them with ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;.