How to fix Laravel 5.4: Specified key was too long error

in Laravel/Tutorials & Samples

As mentioned here, Laravel 5.4 made a change to the default database character set, and it’s now utf8mb4 instead of plain utf8. This switch to utf8mb4 works fine for new applications that are running MySQL v5.7.7 and higher.

Howver, anybody running MariaDB or older versions of MySQL might see the following error when trying to run migrations:

[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

Although this tutorial and the migrations update guide say to edit your AppServiceProvider.php file and inside the boot method set a default string length like so:

use Illuminate\Support\Facades\Schema;

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

I found that this solution did not fully fix the problem and it was something of a hack. The proper fix is to actually edit your database.php file inside the config foler. Inside the file, I simply set the MySQL character set back to UTF8 and this fixes the problem at the core. So here is a sample of my database.php file:

    'connections' => [

        ...

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
        ...
     ]

This should resolve the “Specified key was too long error” problem cleanly for MariaDB and older versions of MySQL.

Tags:

Mifty Yusuf is a Montreal-based software developer who enjoys playing with new web technologies as well as comic books and illustrations. He beleives that, no matter what the question is, the answer is always Batman!

Leave a Reply

Your email address will not be published.

*

Latest from Laravel

Go to Top