Laravel 8 migration error – SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long;

Ask New Question
  • Laravel 8 migration error – SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long;

    I have just started learning Laravel 8 and got the following error after executing the command “php artisan migrate”. It is just a new project and no settings were changed. How do I fix this?

    SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table users add unique users_email_unique(email))

    Using the PHP 7.3 and MySql 8 on Windows wampserver.

    Reply
3 Replies Reply
  • Author
    Replies
  • |  

    This error can be due to the old mysql version. Here is the fix for this.

    https://laravel.com/docs/master/migrations#index-lengths-mysql-mariadb

    Reply
    |  

    But I am using the latest mysql 8 on my local wamp server with the latest version. So I want to know the exact reason for this. And of there is some other method to fix it?

    Reply
    |  

    Actually it is not the issue of version. It is the length of the string. index key took 4 bytes per character. So maximum length for a key should be 250. (250 x 4 = 1000). But if length is not defined then it take the default length of 255 which crosses the maximum limit of 1000 bytes. 191 is the safest value to define.

    So here to resolve this issue. Either define the length in the migration file. Or set a default value in the AppServiceProvider.php

    Go to app/providers directory and open the file AppServiceProvider.php

    Add the following line after the namespace declaration

    use Illuminate\Support\Facades\Schema;

    and then in boot method add the following line

    Schema::defaultStringLength(191);

    So basically solution provided by @Sachin is a working fix for this.

    I wonder, why Laravel is not setting it in its default installation? Because new users hesitate to change something in core files. Refer here for more information https://github.com/laravel/framework/issues/24711

    Reply Suggested Answer
Viewing 3 replies - 1 through 3 (of 3 total)
Reply To: Laravel 8 migration error – SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long;
Your information: