DEV Community

Chathura Rathnayaka
Chathura Rathnayaka

Posted on Originally published at backendrecipes.com

Fixing SQLSTATE[42000]: 1071 Specified Key Was Too Long (1000 & 767 Bytes) in Laravel

#laravel #sql #php #mysql

You configure your database credentials, run php artisan migrate to build your tables, and the migration crashes halfway through the users or jobs table with this terminal error:

Illuminate\Database\QueryException

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes 
(Connection: mysql, SQL: alter table `users` add unique `users_email_unique`(`email`))
Enter fullscreen mode Exit fullscreen mode

Depending on your MySQL or MariaDB version, the error message will state a max key length of 1000 bytes, 767 bytes, or 3072 bytes.

The Math Behind the Crash

By default, Laravel uses the utf8mb4 character set so your database can store full Unicode characters and emojis. Unlike older utf8 (which uses 3 bytes per character), utf8mb4 reserves up to 4 bytes per character.

When a migration creates a standard string column ($table->string('email')->unique()), Laravel sets it to VARCHAR(255). When MySQL tries to build a UNIQUE or INDEX key on that column, it calculates the maximum possible byte size:

255 characters × 4 bytes (utf8mb4) = 1,020 bytes
Enter fullscreen mode Exit fullscreen mode

Because 1,020 bytes exceeds the 1,000-byte (or 767-byte) index limit of your storage engine, MySQL aborts the migration immediately.

Quick Diagnostic Reference

Look at the exact byte limit reported in your terminal error to pick the right fix:

Error Byte Limit Real Root Cause Recommended Fix
max key length is 1000 bytes Database defaulted to the legacy MyISAM engine instead of InnoDB Fix 1
max key length is 767 bytes Older MySQL (< 5.7.7) or MariaDB (< 10.2.2) without innodb_large_prefix Fix 2
Shared hosting / Locked DB config Cannot change server engine settings; need global 191-char cap Fix 2
max key length is 3072 bytes Composite index combining multiple VARCHAR(255) columns exceeds limit Fix 3
Error on re-running migrate Failed DDL left a half-built table (1050 Table already exists) Fix 4

1. The 1000 bytes Trap: Switch from MyISAM to InnoDB

Most forum threads tell you to immediately shrink your string columns to 191 characters in AppServiceProvider. However, if your terminal specifically says max key length is 1000 bytes, shrinking your columns masks a much bigger problem: your database is creating tables using the MyISAM storage engine instead of InnoDB.

MyISAM has a hard 1,000-byte index limit, does not support database transactions (DB::beginTransaction()), and silently ignores foreign key constraints ($table->foreignId()). This commonly happens on WAMP stacks, cPanel shared servers, or custom MariaDB installations where the server's default engine is still set to MyISAM.

How to force InnoDB in Laravel:

  1. Open config/database.php and find the mysql connection array (or mariadb in Laravel 11 and 12).
  2. Change 'engine' => null to 'engine' => 'InnoDB': 
// config/database.php

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DB_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    // ...
    'charset' => env('DB_CHARSET', 'utf8mb4'),
    'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => 'InnoDB', // <-- Explicitly force InnoDB here
],
Enter fullscreen mode Exit fullscreen mode

(Note: If you are using Laravel 11 or 12 and your .env has DB_CONNECTION=mariadb, make sure you update the 'mariadb' block inside config/database.php as well).

  1. Clear your cached configuration and re-run a fresh migration: 
php artisan config:clear
php artisan migrate:fresh
Enter fullscreen mode Exit fullscreen mode

Because modern InnoDB supports up to 3,072 bytes per index, switching the engine to InnoDB allows full VARCHAR(255) utf8mb4 indexes (1,020 bytes) to pass without shrinking your column lengths.

2. The Global defaultStringLength(191) Fix (Laravel 11 & 12)

If your error says max key length is 767 bytes, or if your hosting provider forces legacy index limits that you cannot override, you need to cap Laravel's default string length so that indexed columns stay under 767 bytes:

191 characters × 4 bytes (utf8mb4) = 764 bytes (Safely under 767 & 1000 bytes)
Enter fullscreen mode Exit fullscreen mode

Open app/Providers/AppServiceProvider.php, import the Schema facade at the top, and call Schema::defaultStringLength(191) inside the boot() method:

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     */
    public function register(): void
    {
        //
    }

    /**
     * Bootstrap any application services.
     */
    public function boot(): void
    {
        Schema::defaultStringLength(191);
    }
}
Enter fullscreen mode Exit fullscreen mode

Every migration that calls $table->string('column_name') without an explicit length parameter will now generate a VARCHAR(191) column instead of VARCHAR(255).

3. Fixing the 3072 bytes Limit on Composite Indexes (MySQL 8+)

Even on a modern MySQL 8.0+ or MariaDB 10.6+ server running InnoDB, you can still trigger 1071 Specified key was too long; max key length is 3072 bytes when creating a composite index (an index spanning multiple columns).

For example, consider apolymorphic or multi-tenant lookup table that indexes four VARCHAR(255) columns together:

// ❌ BROKEN: 4 columns × 255 chars × 4 bytes = 4,080 bytes (Exceeds 3,072-byte limit)
Schema::create('audit_logs', function (Blueprint $table) {
    $table->id();
    $table->string('tenant_id');
    $table->string('event_type');
    $table->string('auditable_type');
    $table->string('ip_address');

    $table->index(['tenant_id', 'event_type', 'auditable_type', 'ip_address'], 'audit_lookup_idx');
});
Enter fullscreen mode Exit fullscreen mode

The Fix: Scope Column Lengths to Realistic Sizes

A UUID only needs 36 characters, an IPv6 address needs at most 45 characters, and a model class name or slug rarely exceeds 100 characters. Pass explicit lengths to the columns involved in the composite index:

// ✅ WORKING: (36 + 64 + 120 + 45) × 4 bytes = 1,060 bytes (Well under 3,072 bytes)
Schema::create('audit_logs', function (Blueprint $table) {
    $table->id();
    $table->string('tenant_id', 36);
    $table->string('event_type', 64);
    $table->string('auditable_type', 120);
    $table->string('ip_address', 45);

    $table->index(['tenant_id', 'event_type', 'auditable_type', 'ip_address'], 'audit_lookup_idx');
});
Enter fullscreen mode Exit fullscreen mode

Not only does this eliminate the 1071 migration crash, it also keeps your B-Tree index smaller in RAM, speeding up WHERE queries.

4. Cleaning Up the Follow-Up Error (1050 Table already exists)

After you apply Fix 1 or Fix 2 and run php artisan migrate again, you will almost certainly hit a second error right away:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'users' already exists
Enter fullscreen mode Exit fullscreen mode

Why this happens:

In MySQL, CREATE TABLE and ALTER TABLE are Data Definition Language (DDL) statements that cause an implicit commit. When Laravel creates the users table, step 1 (CREATE TABLE users) succeeds, and step 2 (ALTER TABLE users ADD UNIQUE (email)) crashes with the 1071 key length error. Because the migration crashed mid-flight, Laravel never recorded the migration in the migrations table, leaving an orphaned users table behind.

How to clear it:

  • In Local / Staging Environments: Wipe all half-built tables and re-run all migrations cleanly from scratch: 
php artisan migrate:fresh
Enter fullscreen mode Exit fullscreen mode

(Note: If php artisan migrate:fresh fails to connect to your database socket after restarting your local database service, see our guide on Fixing SQLSTATE[HY000] [2002] Connection Refused in Laravel).

  • In Production Environments (Where you cannot wipe existing data): Never run migrate:fresh in production. Instead, log into MySQL (mysql -u root -p), drop only the specific empty table that failed mid-creation, and re-run php artisan migrate: 
USE your_database_name;
DROP TABLE IF EXISTS failed_table_name;
EXIT;
Enter fullscreen mode Exit fullscreen mode 
php artisan migrate
Enter fullscreen mode Exit fullscreen mode

(Tip: When running production migrations over SSH, always run Artisan under your web server user rather than root so Monolog doesn't lock your log files. Check our guide on Fixing storage/logs/laravel.log Permission Denied for the safe command syntax).

Verify Your Table Engine and Collation

Once your migrations finish with green DONE badges, you can confirm that your tables are running on InnoDB with utf8mb4 by running this quick check inside php artisan tinker or your MySQL client:

SHOW TABLE STATUS WHERE Name = 'users'\G
Enter fullscreen mode Exit fullscreen mode

Look for these two lines in the output:

Engine: InnoDB
Collation: utf8mb4_unicode_ci
Enter fullscreen mode Exit fullscreen mode

When Engine reports InnoDB and your migrations complete without warnings, your database schema is ready for production traffic.

Top comments (0)