rohitjangir's avatar

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`))

Hello friends, when I try to install Laravel 12 project, every time i get this issue. what is the permanent solution for this.

rohitjangir@DESKTOP-BP29VVL MINGW64 /c/wamp64/www/test-project $ php artisan migrate:fresh

Dropping all tables ................................................................................................................. 75.40ms DONE

INFO Preparing database.

Creating migration table ............................................................................................................ 21.85ms DONE

INFO Running migrations.

0001_01_01_000000_create_users_table ................................................................................................ 33.44ms FAIL

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))

at vendor\laravel\framework\src\Illuminate\Database\Connection.php:822 818▕ $this->getName(), $query, $this->prepareBindings($bindings), $e 819▕ ); 820▕ } 821▕ ➜ 822▕ throw new QueryException( 823▕ $this->getName(), $query, $this->prepareBindings($bindings), $e 824▕ ); 825▕ } 826▕ }

1 vendor\laravel\framework\src\Illuminate\Database\Connection.php:568 PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes")

2 vendor\laravel\framework\src\Illuminate\Database\Connection.php:568 PDOStatement::execute()

rohitjangir@DESKTOP-BP29VVL MINGW64 /c/wamp64/www/test-project

0 likes
9 replies
jlrdw's avatar

Check your encoding, is it utf8mb4. I am set at utf8mb4_0900_ai_ci.

1 like
JussiMannisto's avatar

@jlrdw utf8mb4 is an encoding type, utf8mb4_0900_ai_ci is a collation type. These are different settings.

1 like
JussiMannisto's avatar

This has to do with your database configuration, specifically the row formats used by the InnoDB storage engine. Run these two commands in your database:

show variables like 'innodb_default_row_format';
show variables like 'innodb_file_per_table';

If the values are not dynamic and 1 or ON, that's the problem.

Historically, the maximum index key length for InnoDB was 768 or 1000 bytes. The utf8mb4 data type takes up to 4 bytes per character. If the indexed column is 255 characters long, which is the default for string columns in Laravel, the 1000 byte limit is exceeded.

Newer versions of MySQL / MariaDB use the dynamic row format by default, which allows longer keys.

You can fix this issue by setting the innodb_default_row_format to dynamic. Also ensure that innodb_file_per_table is set to 1 so that the modern row formats work. That's also the default nowadays, and it just means each table has its own file on the disk. Place or replace these values under the [mysqld] tag in the database server config file, then restart the database service:

[mysqld]
innodb_file_per_table = 1
innodb_default_row_format = dynamic

An alternative is to just use shorter strings on indexed columns, but it's better to fix the config.

1 like
Snapey's avatar

This is an old problem, which makes me think that your database version is old.

Anyway, the simplest answer is to look in the migration and change the email field to be max length of something like 80 characters which is still way more than you need for an email address.

It will say something like $table->string('email')->unique(), change it to $table->string('email',80)->unique()

2 likes
jayandholariya's avatar

@snapey Can this be used as a solution?

File: /app/Providers/AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191);
}
jayandholariya's avatar

@rohitjangir You can refer this:

https://stackoverflow.com/questions/42244541/laravel-migration-error-syntax-error-or-access-violation-1071-specified-key-wa
ajinaniyan's avatar

This issue happens because MySQL limits indexed string lengths, especially when using utf8mb4 (each character = 4 bytes). Your email column with a unique index exceeds that limit.

You can fix it permanently by opening App\Providers\AppServiceProvider.php and adding this inside the boot() method:

use Illuminate\Support\Facades\Schema;

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

Then run your migration again it’ll work fine.

Alternatively, if you’re on an older MySQL version, consider upgrading to MySQL 5.7+ and enabling innodb_large_prefix for better compatibility.

Please or to participate in this conversation.