Rohan Shewale's Blog

Laravel 5.4 Migration Error: SQLSTATE[42000] Specified key was too long

March 07, 2017 | 1 Minute Read

Laravel 5.4 released on last week of January this year, with lots of bug fixes and security updates (not that it needed one!). From adding broadcast routes to replacing Laravel Elixir with Laravel Mix which is entirely based on Webpack (oppose to previous based on Gulp).

Last week, when one of my teammates was performing migrate from a fresh application it got struck on this error β€”

  [Illuminate\Database\QueryException]                                                      
   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

This is due to Laravel 5.4 default database character set which is now utf8mb4, for including support to store emojis. This needs you running at least MySQL v5.7.7 or MariaDB v10.2.2, for anyone on older versions will encounter the previous error when running migration.

To solve this you will need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them, as outlined in the Migrations guide. You can configure this by calling the Schema::defaultStringLength method within your AppServiceProvider: β€”

  use Illuminate\Support\Facades\Schema;

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

After which, everything will work as expected.

Though, the Laravel documentation also mentioned an alternative way – by enabling innodb_large_prefix in your database, but it’s better to upgrade you DB instead.