Dev Notes

Software Development Resources by David Egan.

Laravel 5.4 and MariaDB Errors


Laravel, MariaDB, Migrations
David Egan

When running migrations on Laravel 5.4 and MariaDB you may encounter 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

Solution

Amend the AppServiceProvider - which you’ll find here: app/Providers/AppServiceProvider.php. Use Illuminate\Support\Facades\Schema to set a default string length which can be used in migrations. The ameded class might look like this:

<?php

namespace App\Providers;

use Illuminate\Support\Facades\Schema;
use Illuminate\Support\ServiceProvider;

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

    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

Migrate the Database

You may end up with a part-migration error, whereby tables have been created during a broken migration, but the migrations table is missing. Here’s an example showing the database after receiving the above-quoted error:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 82
Server version: 10.1.23-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [my_db_dev]> show tables;
+---------------------+
| Tables_in_my_db_dev |
+---------------------+
| migrations          |
| users               |
+---------------------+

2 rows in set (0.00 sec)

With the database in this state, you won’t be able to roll back migrations.

To fix, delete the tables manually at the MariaDB prompt:

# Open the MariaDB command line focused on your database:
mysql -u root -p my_db_dev

# Show tables to determine what tables you need to drop:
SHOW TABLES;

# Drop tables:
DROP TABLE users, migrations;

After manually amending the database, close the MariaDB CLI by typing \q.

At this point you should be able to run the migration successfully:

php artisan migrate
comments powered by Disqus