Migrations - 1071 Specified key was too long - Proper Solution

Question

I have just started using Laravel (8) and a bit confused on how to properly fix this error.

I have seen multiple suggested solutions here but do not believe the accepted solution to be the better solution.

I was attempting to perform php artisan migrate:fresh on a default Laravel project. I was able to solve this by using the solution that involves specifying a length to certain fields

    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name', 50);
        $table->string('email', 100)->unique();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password', 24);
        $table->rememberToken();
        $table->timestamps();
    });

The accepted solution is to modify Schema::defaultStringLength();.

Is this just a personal preference?

Edit: I noticed an additional detail in the documentation.

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:

I just so happened to be using version 5.7.28

setting the defaultStringLength just adjusts it from the default of 255 to what ever you set, you could explicitly set the fields length yourself as you are if you wish — lagbox, Dec 01 '20 at 21:21
Does this answer your question? [Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/42244541/laravel-migration-error-syntax-error-or-access-violation-1071-specified-key-wa) — miken32, Dec 01 '20 at 21:36
If you "do not believe the accepted solution to be the better solution" then there are 40 others to choose from. What kind of answer are you expecting to get here? — miken32, Dec 01 '20 at 21:37

score 0 · Answer 1 · answered Dec 01 '20 at 21:32

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:

I just so happened to be using version 5.7.28

score 0 · Answer 2 · answered Dec 01 '20 at 21:35

0

As the error specifies, you can set the default string length in a provider, this could be AppServiceProvider.php.

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

answered Dec 01 '20 at 21:35

mrhn

11,846
4
16
37

score 0 · Answer 3 · answered Dec 01 '20 at 22:06

For further reading take a look at innodb limits specifically the 767 bytes limit for each row of an index.

So there's a few things to take away:

The problem applies to tables stored using the InnoDB storage engine (the default in most cases)
It applies to MySQL older than 5.7.7 but also to versions after 5.7.7 that use different parameters other than the defaults.
There is no inherent default length in the database itself. The default length is specifically a Laravel feature.
The default length of 255 characters means that you can have at most 2 bytes per character to be able to create an index on a given field, so utf8 is ok but utf8mb4 is not.

Schema::defaultStringLength() is the suggested solution if you don't have the option to fine-tune your fields lengths. In an ideal world your field lengths would be already fine-tuned based on the expected data that would be inserted in them. This should allow you to catch input errors sooner.

So what I suggest to do is use Schema::defaultStringLength(191); if you can't change your innodb settings but still fine-tune your field lengths based on your real expected needs.

Migrations - 1071 Specified key was too long - Proper Solution

3 Answers3