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

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?

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)

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

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

include this line at top

use Illuminate\Support\Facades\Schema;

then inside boot function add this

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

In file config/database.php where :

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

Change this line to this :

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

This worked for me:

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

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)

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

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

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

If you have tried every other answer and they have not worked, you can drop all tables from the database and then execute the migrate command all at once using this command:

php artisan migrate:fresh

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.

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

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

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

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

Give a smaller length for your email string like :

$table->string('email',128)->unique(); //In create user table

And

$table->string('email',128)->index(); // create password resets table

This will definitely work.

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.

Keep in mind that those articles goes with the easy and wrong solution. It's like chopping of part of your foot because your socks doesn't fit.

The correct answer (to buy a new sock) is a bit more complex since it requires you to go to the store again. In this case it requires you to configure your mysql instance, and that isn't as copy/pastable as a wrong answer, so we keep propagating the wrong answer. We want to seem copy/pastable and easy to use, right?

You forgot to specify which Laravel or mysql version you are using. The problem occurs because you are using an older mysql (or newer with wrong settings).

Set innodb_file_format=Barracuda
Set innodb_large_prefix=1
Set innodb_default_row_format=dynamic

If you do not have the innodb_default_row_format setting (introduced in 5.7.9), then you have to change your config/database.php to use 'engine' => 'innodb row_format=dynamic'.

The engine setting was introduced in Laravel 5.2.14, so I hope you have something newer than that.

https://github.com/laravel/framework/issues/22660#issuecomment-355725923

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!

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'

for laravel 5.7 write these code in appserviceprovider.php

  use Illuminate\Support\Facades\Schema;

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

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.

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',

    ......

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

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.

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

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

For laravel 5.4, simply edit file

App\Providers\AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

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

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

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

Please open user and password rest files. files are located in database/migrations folder.

add line

Schema::defaultStringLength(191); in up function in both files.

Then run

php artisan migrate

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

If you are seeing this error then definitely you need to make following changes on AppServiceProvider.php file -> which can be found inside app->providers:

changes to be made

use Illuminate\Support\Facades\Schema; //see if you have this class if not add it.

public function boot()
{
    Schema::defaultStringLength(191); //make sure you add this line
}

this should solve your problem for more you can see the Laravel news regarding this issue. https://laravel-news.com/laravel-5-4-key-too-long-error

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.

The same error pops up because of the way Laravel unique() function works.

The only thing you need to do is specify a shorter index name eg.

$table->unique(['email', 'users_email_uniq'],'my_custom_uniq');

In some cases it can help if you agree to unique only the first 191 ?hars

public function up()
{
    DB::statement('ALTER TABLE `YOUR_TABLE_NAME`    ADD UNIQUE INDEX `NAME_FOR_INDEX` (`YOUR_COLUMN_NAME` (191) );');
}

i had same problem and i am using a wamp

Solution : Open file : config/database.php

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

Thanks

Specify a smaller length for your e-mail:

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

100 WORKS