Laravel migration: unique key is too long, even if specified

Question

I am trying to migrate a users table in Laravel. When I run my migration I get 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_uniq(email))

my migration is as follows:

Schema::create('users', function(Blueprint $table)
{
    $table->increments('id');
    $table->string('name', 32);
    $table->string('username', 32);
    $table->string('email', 320);
    $table->string('password', 64);
    $table->string('role', 32);
    $table->string('confirmation_code');
    $table->boolean('confirmed')->default(true);
    $table->timestamps();

    $table->unique('email', 'users_email_uniq');
});

After some googling I came across this bug report where Taylor says you can specify the index key as the 2nd parameter of unique(), which I have done. It still gives the error. What is going on here?

Answer 1

Specify a smaller length for your e-mail:

$table->string('email', 250);

Which is the default, actually:

$table->string('email');

And you should be good.

For Laravel 5.4 you can find a solution in this Laravel 5.4: Specified key was too long error, Laravel News post:

As outlined in the Migrations guide to fix this all you have to do is edit your AppServiceProvider.php file and inside the boot method set a default string length:

use Illuminate\Database\Schema\Builder;


public function boot()
{
    Builder::defaultStringLength(191);
}

Answer 2

Update 1

As of Laravel 5.4 those changes are no more needed.

Laravel 5.4 uses the utf8mb4 character set by default, which includes support for storing "emojis" in the database. If you are upgrading your application from Laravel 5.3, you are not required to switch to this character set.

Update 2

Current production MariaDB versions DO NOT support this setting by default globally. It is implemented in MariaDB 10.2.2+ by default.

Solution

And if you intentionally want to use the correct future-default (starting from Laravel 5.4) UTF8 multi-byte utf8mb4 support for 😀 then start to fix 😂 your database configuration.

In Laravel config/database.php define:

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

DYNAMIC allows to store long key indexes.

Server settings (by default included in MySQL 5.7.7+ / MariaDB 10.2.2+):

[mysqld]
# default character set and collation
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4

# utf8mb4 long key index
innodb_large_prefix = 1
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = 1

For clients:

[mysql]
default-character-set=utf8mb4

And then STOP your MySQL/MariaDB server. After that START. Hot RESTART may not work.

sudo systemctl stop mysqld
sudo systemctl start mysqld

Now you have Laravel 5.x with UTF8 support.

Answer 3

If you're on or updated to Laravel 5.4 This worked for me;

Just 1 change. in AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

public function boot()
{
  Schema::defaultStringLength(191);
}

As mentioned in the migrate guide https://laravel.com/docs/master/migrations#creating-indexes

Answer 4

If anyone else stumbles upon this answer like I did but for a different reason, you may check your Laravel DB charset/collation.

I was installing an application (Snipe-IT) and had configured the Laravel database config to use the following:

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_general_ci',

Removing mb4 from both strings fixed the issue, although I believe Antonio's answer is the real solution to the problem.

Answer 5

This worked for me:

 $table->charset = 'utf8';
 $table->collation = 'utf8_unicode_ci';

Answer 6

For laravel 5.6
This solution solve my problem
go to config/database.php
Find the code below

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Change this two field

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci'

With This

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci'

Answer 7

Remove mb4 from charset and collation from config/database.php, then it will execute successfully.
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

Answer 8

I have faced the same issue, and fixed it by adding the below two lines in my app/database.php

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

My file looks like below :

<?php

return [

    /*
    |--------------------------------------------------------------------------
    | Default Database Connection Name
    |--------------------------------------------------------------------------
    |
    | Here you may specify which of the database connections below you wish
    | to use as your default connection for all database work. Of course
    | you may use many connections at once using the Database library.
    |
    */

    'default' => env('DB_CONNECTION', 'mysql'),

    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',

    ......

Answer 9

For laravel 5.4, simply edit file

App\Providers\AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);
}

Answer 10

In file config/database.php where :

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

Change this line to this :

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

Answer 11

File: config/database.php
change the following
FROM ->
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

TO ->
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

Answer 12

i had same problem and i am using a wamp

Solution : Open file : config/database.php

'engine' => null, => 'engine' => 'InnoDB',

Thanks

Answer 13

If someone having this problem even after doing, above mentioned changes. For an example, in my case I did below changes,

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()
   {
      //
   }

   public function boot()
   {
     Schema::defaultStringLength(191);
   }
}

But it wouldn't work right away for two reasons. One is if you are using lumen instead of laravel, you may have to uncomment this line in your app.php file first.

$app->register(App\Providers\AppServiceProvider::class);

And then you have to create the migration script again with the artisan command,

php artisan make:migration <your_table_name>

Since now only the changes you made to ServiceProvider will work.

Answer 14

For Laravel >= 5.6 users

Open AppServiceProvider.php file

Use the following class

use Illuminate\Support\Facades\Schema;

Then inside boot method add the following line

public function boot()
{
    Schema::defaultStringLength(191);
}

Answer 15

Going to your config/database.php and change the charset and collation from utf8mb4 to utf8

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

My problem solved using this method, good luck dude!

Answer 16

I added to the migration itself

Schema::defaultStringLength(191);
Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email')->unique();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

yes, I know I need to consider it on every migration but I would rather that than have it tucked away in some completely unrelated service provider

Answer 17

for laravel 5.7 write these code in appserviceprovider.php

  use Illuminate\Support\Facades\Schema;

public function boot()
{
  Schema::defaultStringLength(191);
}

Answer 18

Change charset to from 'utf8mb4' to 'utf8' and

collation to 'utf8mb4_unicode_ci' to 'utf8_unicode_ci'

in config/database.php file

It worked for me.

Answer 19

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);
}

You can check out of

https://laravel-news.com/laravel-5-4-key-too-long-error https://laravel.com/docs/5.5/migrations#indexes

Answer 20

It's because Laravel 5.4 uses utf8mb4 which supports storing emojis.

Add this in your app\Providers\AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);
}

and you should be good to go.

Answer 21

If you're on or updated to Laravel 5.4 and latest version it works;
Just 1 change in AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

public function boot()
{
  Schema::defaultStringLength(191);
}

Answer 22

You can go to app/Providers/AppServiceProvider.php and import this

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

also in boot function add this

Schema::defaultStringLength(191);

Answer 23

I'd like to point that something that i missed ...

I'm new at Laravel, and i didn't copy the "use Illuminate....." because i really didn'at paid atention, because right above the function boot you alread have a use Statement.

Hope that helps anyone

**use Illuminate\Support\Facades\Schema;**

public function boot()
{
    Schema::defaultStringLength(191);
}

Answer 24

I had a problem, change the configuration of the 'config / database'

file 'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

keeping the same pattern in the database.

I then gave the command

php artisan migrate

Answer 25

In 24 october 2016 Taylor Otwell the Author of Laravel announced on is Twitter

"utf8mb4" will be the default MySQL character set in Laravel 5.4 for better emoji support. 🙌 Taylor Otwell Twitter Post

which before version 5.4 the character set was utf8

During this century many web app, include chat or some kind platform to allow their users to converses, and many people like to use emoji or smiley. and this are some kind of super characters that require more spaces to be store and that is only possible using utf8mb4 as the charset. That is the reason why they migrate to utf8mb4 just for space purpose.

if you look up in the Illuminate\Database\Schema\Builder class you will see that the $defaultStringLength is set to 255, and to modify that you can procede through the Schema Facade and call the defaultStringLength method and pass the new length.

to perform that change call that method within your AppServiceProvider class which is under the app\providers subdirectory like this

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        // all other code ...

        Schema::defaultStringLength(191); 
    }
    // and all other code goes here
}

I will suggest to use 191 as the value just because MySQL support 767 bytes, and because 767 / 4 which is the number of byte take by each multibyte character you will get 191.

You can learn more here The utf8mb4 Character Set (4-Byte UTF-8 Unicode Encoding) Limits on Table Column Count and Row Size

Answer 26

SOLUTION:

First change the defaultStringLength to 191, in the app\Providers\AppServiceProvider.php:

public function boot()
{
    Schema::defaultStringLength(191);
}

Then, change the charset and collation values as follows, in config\database.php:

'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',

(link to know about MariaDB charset)

Answer 27

You will not have this problem if you're using MySQL 5.7.7+ or MariaDB 10.2.2+.

To update MariaDB on your Mac using Brew first unlink the current one: brew unlink mariadb and then install a dev one using brew install mariadb --devel

After installation is done stop/start the service running: brew services stop mariadb brew services start mariadb

Current dev version is 10.2.3. After the installation is finished you won't have to worry about this anymore and you can use utf8mb4 (that is now a default in Laravel 5.4) without switching back to utf8 nor editing AppServiceProvider as proposed in the Laravel documentation: https://laravel.com/docs/master/releases#laravel-5.4 (scroll down to: Migration Default String Length)

Answer 28

Just installed MariaDB 10.2.4 RC, fired up new blank Laravel 5.4 project and default migration (varchar(255) columns) works.

No need to change DB conf and Laravael config/database.php. Thus just as @scorer noted about default behaviour for 10.2.2+.

Answer 29

All was well described in the others Anwser you can see more details in the link bellow (search with key 'Index Lengths & MySQL / MariaDB") https://laravel.com/docs/5.5/migrations

BUT WELL THAT's not what this answer is about! the thing is even with doing the above you will like get another error (that's when you like launch php artisan migrate command and because of the problem of the length, the operation like stuck in the middle. solution is bellow, and the user table is like created without the rest or not totally correctly) we need to roll back. the default roll back will not do. because the operation of migration didn't like finish. you need to delete the new created tables in the database manually.

we can do it using tinker as in bellow:

L:\todos> php artisan tinker

Psy Shell v0.8.15 (PHP 7.1.10 — cli) by Justin Hileman

>>> Schema::drop('users')

=> null

I myself had a problem with users table.

after that your good to go

php artisan migrate:rollback

php artisan migrate

Answer 30

Set database engine InnoDB:

  Schema::create('users', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });