Why This Error HappensFew things are as frustrating as a database error blocking a fresh WordPress install. Usually, it happens right when you are about to finish. You will see ERROR 1071 (42000) pop up, signaling that your database hit a wall while trying to create an index. This typically occurs when activating resource-heavy plugins like WooCommerce or Yoast SEO on older server environments.
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
The Math Behind the LimitIt comes down to a simple calculation. Modern WordPress uses the utf8mb4 character set to support emojis and special symbols. In this format, each character can take up to 4 bytes of space.
When a plugin tries to create a unique index on a VARCHAR(255) column, the database does the math: 255 characters × 4 bytes = 1,020 bytes. Because older versions of the InnoDB engine (pre-MySQL 5.7.7) have a strict 767-byte limit for index keys, the database rejects the query. It is essentially trying to fit a 1,020-byte peg into a 767-byte hole.
Fix 1: Enable InnoDB Large Prefix (Best for VPS Users)If you manage your own server via SSH, you can fix this by changing how the database handles file formats. You need to move to the Barracuda format, which supports index keys up to 3,072 bytes.
1. Access Your Database```
mysql -u root -p
### 2. Update Global VariablesRun these commands to tell the engine to allow larger prefixes:
SET GLOBAL innodb_file_format = Barracuda; SET GLOBAL innodb_file_per_table = ON; SET GLOBAL innodb_large_prefix = ON;
### 3. Make the Changes PermanentDon't skip this step, or the error will return after your next reboot. Open your configuration file (usually `/etc/mysql/my.cnf`) and add these lines under the `[mysqld]` block:
[mysqld] innodb_file_format = Barracuda innodb_file_per_table = ON innodb_large_prefix = ON
Restart the service to apply the changes: `sudo systemctl restart mysql`.
## Fix 2: Adjust wp-config.php (Best for Shared Hosting)If you are on shared hosting (like Bluehost or HostGator), you probably don't have root access to change server variables. Instead, you can tell WordPress to use a 3-byte character set. This drops the index size to **255 × 3 = 765 bytes**, which barely fits under the 767-byte limit.
Open your `wp-config.php` file and find this line:
define( 'DB_CHARSET', 'utf8mb4' );
Change it to:
define( 'DB_CHARSET', 'utf8' );
**Warning:** Switching to `utf8` means you lose full emoji support. If a user posts a 😭 emoji, it might appear as a broken character or a question mark.
## Fix 3: Manually Shorten the Index LengthSometimes you only need to fix one specific table. You can manually alter the column to use 191 characters instead of 255. Since **191 × 4 = 764 bytes**, the database will accept it without complaining.
ALTER TABLE wp_options MODIFY option_name varchar(191);
Check your error log to see which specific table and column triggered the 1071 error, then swap `wp_options` and `option_name` with the names found in your logs.
## How to Verify the FixTo make sure everything is running smoothly, check your work:
- **For VPS:** Run `SHOW VARIABLES LIKE 'innodb_large_prefix';`. If it says `ON`, you are good to go.- **For Shared Hosting:** Delete the half-finished tables from your database and restart the WordPress installation. It should now finish without errors.- **Plugin Check:** Try reactivating the plugin that caused the crash. It should now generate its tables successfully.While these fixes work, the 767-byte limit is a sign of an aging server. For the best security and performance, plan an upgrade to **MySQL 8.0** or **MariaDB 10.5+** where these limits are no longer an issue.