Dev Notes

Various Cheat Sheets and Resources by David Egan/Carawebs.

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