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

[Saibamen]

• 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
• AppProvidersAppServiceProvider:

<?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

[ManojKiranA]

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

[staudenmeir]

What does your migration look like?

[Saibamen]

@ManojKiranA: Ekhm... Please read my ticket again. I already added Schema::defaultStringLength(191) into AppServiceProvider

@staudenmeir: This same as @ManojKiranA - read my ticket again ;) I didn't change anything except my user, password and database name for MySQL in .env

users:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

password_resets:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreatePasswordResetsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('password_resets', function (Blueprint $table) {
            $table->string('email')->index();
            $table->string('token');
            $table->timestamp('created_at')->nullable();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('password_resets');
    }
}

[justicenode]

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.

[Saibamen]

So documentation need to be updated

[devcircus]

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.

[adelsedighi]

Route::Post does not work!
I have to make it get then give a hidden input as method= post
the hidden input value does not go into mysql db!!!
the route and view, action all checked! but mysql returns an error which state the ... field does not have a valid value?? but the value is set in the view form!

[alhavizd]

change bigIncrements to increments (ID).

[legend6]

just update your mariadb to the latest version 10.4.7, it will fix it ;)
also for $table->json()

happy coding !!

[0884508882inno]

Am facing the same problem but to me it is failing to add other tables in the database apart from the user and password table

[PhuBui]

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

[wanamra]

I use MySQL 8.0.18, create database using collation utf8mb4_general_ci, make sure the MySQL engine is InnoDB, successfully run php artisan migrate

[visotechsolutions]

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

[jrdutra]

The error persists:

IlluminateDatabaseQueryException

SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'users' already exists (SQL: create table users (id bigint unsigned not null auto_increment primary key, name varchar(191) not null, email varchar(191) not null, email_verified_at timestamp null, password varchar(191) not null, remember_token varchar(100) null, created_at timestamp null, updated_at timestamp null) default character set utf8mb4 collate 'utf8mb4_unicode_ci')

at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
667| // If an exception occurs when attempting to run a query, we'll format the error
668| // message to include the bindings with SQL, which will make this exception a
669| // lot more helpful to the developer instead of just the database's errors.
670| catch (Exception $e) {

671| throw new QueryException(
672| $query, $this->prepareBindings($bindings), $e
673| );
674| }
675|

  +9 vendor frames

10 database/migrations/2014_10_12_000000_create_users_table.php:24
IlluminateSupportFacadesFacade::__callStatic("create")

  +22 vendor frames

33 artisan:37
IlluminateFoundationConsoleKernel::handle(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

[jrdutra]

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

It Worked for me...Thanks!!!