Laravel migration error: Specified key too long

Most of the newbies who just started with Laravel, for those running MariaDB or older versions of MySQL may hit this error when trying to run migrations.

php artisan migrate
[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; 
max key length is 767 bytes


Let’s check what’s the root cause of this error.

When we are using UTF8 character set in MySQL, each character holds 3 bytes. Default length of a String (varchar) field is 255 characters. So 255 X 3 < 767. So we are good.

But if you check database config you will see that latest Laravel releases uses utf8mb4 charset.

'charset' => 'utf8mb4',

Which means we are using 4 bytes for every character. This supports storing emojis as well.
So now you know what’s the issue : 255 X 4 = 1020 > 767.

There are three ways to fix this issue.

1) Update your MySQL database server to 5.7.7 or a higher version. This will automatically resolve the issue.

2) Add following two lines of code to app/Providers/AppServiceProvider.php file.
Use statement at the top and set schema default string length at boot method.
191 X 4 = 764 < 767, So we are good.

use Illuminate\Support\Facades\Schema;

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

3) Last method is to set length at migration file. You need to set this value for all the varchar (String) fields which marked as INDEX or UNIQUE. I will show an example below.

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

$table->string('email', 191)->index();

Leave a Comment