framework: SQLSTATE[42000] fix not working for Laravel 5.8

Laravel Version: 5.8.3
PHP Version: 7.3.2
Database Driver & Version: MariaDB 10.1.38

Description:

Fix for 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)) from https://laravel.com/docs/master/migrations#creating-indexes is not working for latest Laravel

Steps To Reproduce:

fresh Laravel 5.8.3 installation
App\Providers\AppServiceProvider:

<?php

namespace App\Providers;

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

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

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        // Fix for MySQL < 5.7.7 and MariaDB < 10.2.2
        // https://laravel.com/docs/master/migrations#creating-indexes
        Schema::defaultStringLength(191);
    }
}

execute php artisan make:auth
create new database for your connection (don’t change config files - only .env file)
execute php artisan migrate

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 4
Comments: 16 (3 by maintainers)

Commits related to this issue

fixed string length issue https://github.com/laravel/framework/issues/27806 — committed to slefevre/patch by slefevre 4 years ago

Most upvoted comments

i think the problem is that by default laravel uses the utf8mb4 charset which uses more space than the utf8 charset. You can either change the mysql settings to allow for bigger keys or just change the charset to utf8 and the collation to utf8_general_ci in config/database.php under the mysql section. Had this problem more than once and not just with laravel.

also have a look at @devcircus’ comment below.

+26

justicenode on Sep 1, 2020

The docs should cover everything you need:

Index Lengths & MySQL / MariaDB

Laravel uses the utf8mb4 character set by default, which includes support for storing “emojis” in the database. 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:

use Illuminate\Support\Facades\Schema;

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

Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database’s documentation for instructions on how to properly enable this option.

devcircus on Mar 8, 2019

i think the problem is that by default laravel uses the utf8mb4 charset which uses more space than the utf8 charset. You can either change the mysql settings to allow for bigger keys or just change the charset to utf8 and the collation to utf8_general_ci in config/database.php under the mysql section. Had this problem more than once and not just with laravel.

Thanks @justicenode it work for me

PhuBui on Oct 8, 2019

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

If you encounter this error its because your mysql(MariaDB) Version Framework has nothing to do with it

If You add Schema::defaultStringLength(191); to AppServiceProvider All the fileds in the migration will have the length of 191 so if you dont want ot specify the length of the colunm for each table you can add to AppserviceProvider

ManojKiranA on Mar 7, 2019

i think the problem is that by default laravel uses the utf8mb4 charset which uses more space than the utf8 charset. You can either change the mysql settings to allow for bigger keys or just change the charset to utf8 and the collation to utf8_general_ci in config/database.php under the mysql section. Had this problem more than once and not just with laravel.

work for me thanks

visotechsolutions on Jun 9, 2020