1071 Specified key was too long – Laravel 5.4

September 21, 2019

by Yunus Shaikh

HOW TO FIX – 1071 SPECIFIED KEY WAS TOO LONG ERROR

[IlluminateDatabaseQueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table users add unique users_email_unique(email)) [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes


To fix this issue

Go to app/Providers/AppServiceProvider.php file and add following code into boot method


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


    use Illuminate\Support\Facades\Schema;

Laravel 5.4 made a change to the default database character set, it’s now utf8mb4 which includes support for storing emojis. This only affects new applications and as long as you are running MySQL v5.7.7 and higher you do not need to do anything

Be careful about this solution. If you index email fields, for example, stored emails can only have a max length of 191 chars. This is less than the official RFC states.

Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.

For someone who doesn't want to change AppServiceProvider.php. (In my opinion, it's bad idea to change AppServiceProvider.php just for migration) You can add back the data length to the migration file under database/migrations/ as below: create_users_table.php


                $table->string('name',64);
                $table->string('email',128)->unique();
                create_password_resets_table.php
                
                $table->string('email',128)->index();
                
                

Featured Posts