Laravel 5.4: Specified key was too long error

Question

I have a problem using laravel migrate command. It shows :

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

But my mysql version is 5.7.14 and my charset is :

mysql> show variables like "%char%";
+--------------------------+-------------------------------------------------+
| Variable_name            | Value                                           |
+--------------------------+-------------------------------------------------+
| character_set_client     | utf8mb4                                         |
| character_set_connection | utf8mb4                                         |
| character_set_database   | utf8mb4                                         |
| character_set_filesystem | binary                                          |
| character_set_results    | utf8mb4                                         |
| character_set_server     | utf8mb4                                         |
| character_set_system     | utf8                                            |
| character_sets_dir       | D:\wamp64\bin\mysql\mysql5.7.14\share\charsets\ |
+--------------------------+-------------------------------------------------+

So there should not be any problem as doc said. Someone can help me?

Answer 1

The 1000 bytes mentioned in the error message points to your storage engine being MyISAM instead of InnoDB. The MySql changes related to the key length in 5.7.7 were specific to the InnoDB engine.

You either need to move your table to the InnoDB storage engine, or if you have to use MyISAM, you'll need to use the Schema::defaultStringLength(191); statement mentioned in the documentation.

// AppServiceProvider

use Illuminate\Support\Facades\Schema;

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

Answer 2

You will not get any error for latest version of MySQL 5.7.7 or above, but for below version in any laravel version you need to add a line into your file AppServiceProvider.php placed at app/Providers/AppServiceProvider.php

You need to update the like below:

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

Please do not forget to add the line

use Illuminate\Support\Facades\Schema;

at the top of the file.

With the reference of the blog below: https://laravel-news.com/laravel-5-4-key-too-long-error

Answer 3

The approached that work here was pass a second param with the key name (a short one):

$table->string('email')->unique(null,'email');

Answer 4

Setting Schema::defaultStringLength(191) won't always work i.e. if you use Telescope. Main cause of this error is mysql engine. Laravel is configured for InnoDB by default and your database is probably configured to use MyISAM by default. To fix it properly it's better to edit config\database.php and in mysql configuration change

'engine' => null

to

'engine' => 'InnoDB'

Ref: https://stackoverflow.com/a/37996097/6389945

Answer 5

Index Lengths & MySQL / MariaDB

Laravel uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are running a version of MySQL older than the 5.7.7 release or MariaDB older than the 10.2.2 release, you may need to manually configure the default string length generated by migrations in order for MySQL to create indexes for them. You may configure this by calling the Schema::defaultStringLength method within your AppServiceProvider (app\Providers\AppServiceProvider.php) :

use Illuminate\Support\Facades\Schema;

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