Syntax error or access violation: 1071 Specified key was too long

Ask New Question

Tagged: 

  • Syntax error or access violation: 1071 Specified key was too long

    Laravel Migration Error:

    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.

    • This topic was modified 6 months ago by PK.
    • This topic was modified 6 months ago by PK.
    • This topic was modified 6 months ago by PK.
    • This topic was modified 2 months ago by PK.
    • This topic was modified 2 months ago by PK.
    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

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

    + 0
    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

    • This reply was modified 2 months ago by PK.
    + 0
    Reply Best Answer Suggested Answer
Viewing 3 replies - 1 through 3 (of 3 total)
Reply To: Reply #50274 in Syntax error or access violation: 1071 Specified key was too long
Your information: