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?

Leaden

Why are you using 320 chars for e-mail? This might be your problem. – Graphology

That was indeed the problem, no idea why. But yes, you are right, I don't know why I specified the char length for each field. Have removed these limits – Leaden

It's funny how no one suggested using fixed-length field that contains hash of the email and voila - problem solved forever, for any framework and for any relational database. Because that's how we guarantee uniqueness - using a fixed-number representation of variable-length input, given the fact that number range is sufficiently large (and for sha1 / sha256 it is). – Homophone

laravel-news.com/laravel-5-4-key-too-long-error may get help – Bellied

Possible duplicate of #1071 - Specified key was too long; max key length is 767 bytes – Mast

removing ->unique() worked for me ! – Selfcontrol

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

Graphology

Maximum possible email length is 254 so probably worth keeping this in mind, so I would probably validate the uniqueness using validator in that case. – Bunyip

I think scored aproach its safer, as it does really implements the charset in the database instead of doing tricks, you will probably find problems with emoticons. – Hild

For Laravel 5.4, use \Illuminate\Database\Schema\Builder::defaultStringLength(191); for correct function reference path – Puparium

After making the configuration in the AppServiceProvider.php, this problem is still happening. I am just confused. Why? I restarted server, database and everything but still. Please help. – Mirandamire

You have to set the length of the indexed column according to the limit of 767 bytes. Be aware that VARCHAR may have 1, 2 or 4 bytes for each length unit. Example: utf8_mb4 (4 bytes) -> 767 / 4 = 191. Otherwise utf8_general_ci for VARCHAR(X) with X < 85 ( 1 byte ) = O(85) , or utf8_general_ci for VARCHAR(X) with X >= 86 ( 2 bytes ) -> 767 / 2 = 383. Consider also other columns lenght in multiple column indexes. – Ragouzis

You may also want to edit directly the length of the specific column in the migration files, over specifying a default length for all the string columns, as not all columns will need this constraint as they aren't in any index. $table->string('column_name',191); – Ragouzis

This fix is still working in 2019 for laravel version 5.8.* (and mysql 5.6.* in my case) .Adding the default String length inside the boot() method fixes the 'key too long' error. – Blancheblanchette

I have seen 191 characters in the official news letter too. laravel-news.com/laravel-5-4-key-too-long-error, but how did the 191 number calculated ? – Fishing

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.

Cuneo

This might wok well enough with MySQL 5.5 (did not try to reconfigure). The 5.7 (probably also 5.6) worked without the need for any reconfiguration. The 5.7 was a default Community Server distribution with vanilla configuration. – Chiou

I changed the engine in my database.php as you mentionned but it is still creating tables with row=compact which is causing a problem. I didn't fully understand, are you saying that it isn't enough to make this change in database.php and that it is also required to make the changes in the my.cnf file ? – Grassquit

It is enough to make changes just in database.php config file and it will impact local Laravel project. Be sure to delete database before making changes and create it with new settings. You need to change my.cnfconfig file only for global server side changes (currently all new installations use utf8mb4). – Cuneo

Or - add another field, calculate the hash of the email, make the field unique, solve the problem forever, avoid fiddling with database initialization variables. – Homophone

If anybody is using Doctrine 2, you can set ROW_FORMAT by passing options={"row_format"="DYNAMIC"} to your @Table annotation. – Peccant

Thanks, you are king, this worked, but was important to set the engine in laravel, now we have 😀'engine' => 'InnoDB ROW_FORMAT=DYNAMIC', – Hild

Just adding a bit default-storage-engine=InnoDB in my.ini along with this answer saved my day. – Colchicine

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC', line worked for me. – Bialy

This fixed it with MySQL 8. Thank you! – Erine

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

Oxytetracycline

I did this on the migration itself. – Etka

This is (presumably) because each character takes up exactly 4 bytes, and the maximum key length is measured in bytes, not characters. So the key length will by 191 * 4 = 764 bytes, just a smidgen under the maximum 767 bytes the database will support. Solutions need explanations IMO if they are to contribute to the shared knowledge here, and not just provide "procedures". But a handy fix nonetheless. – Ignite

this solution always work. But Why even laravel 10 this issue still exist? – Sacco

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.

Subchloride

Answer 5

This worked for me:

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

Hartsfield

This worked for me. I use Server version: 10.1.22-MariaDB - Source distribution – Brazzaville

Answer 6

For laravel 5.4, simply edit file

App\Providers\AppServiceProvider.php

use Illuminate\Support\Facades\Schema;

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

Freehold

This also worked on laravel 8.1 – Huang

Answer 7

Remove mb4 from charset and collation from config/database.php, then it will execute successfully.

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

Clamshell

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

    ......

Epidiascope

If you installed new version in Laravel. Please remove 'mb4' from utf8mb4 & utf8mb4_unicode_ci & config/database.php – Epidiascope

Answer 9

i had same problem and i am using a wamp

Solution : Open file : config/database.php

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

Thanks

Cooperage

None of the previous answers worked for me, but this one worked like a charm! And it makes perfect sense, I believe in previous versions of Laravel DB engine was set to InnoDB by default so we didn't encounter these errors previously. – Pudendum

yes needed to do a couple of the other answers and this as well. – Laudable

Answer 10

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

Assimilation

Answer 11

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

Impellent

Answer 12

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.

Sedulity

Answer 13

for laravel 5.7 write these code in appserviceprovider.php

  use Illuminate\Support\Facades\Schema;

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

Secunda

Answer 14

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

Barnhill

Does not help my case. – Sprocket

Answer 15

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

Goddess

thanks, works for me. mamp mysql with laravel 5.6.23 – Sabrasabre

Answer 16

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.

Headcloth

Answer 17

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

Resemblance

Answer 18

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

Iceberg

This is the only answer that explains the 191 magic number. – Phrenetic

Answer 19

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

Intrust

You can also just prefix any Facade with \ – Alumroot

Answer 20

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

Ostracize

Answer 21

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)

Soracco

Answer 22

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)

Sort

Answer 23

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

Gristede

Answer 24

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

Thuthucydides

Answer 25

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

Rotherham

Answer 26

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

Plasmagel

Answer 27

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

Talapoin

Answer 28

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.

Imperfection

Answer 29

Specify a smaller length for your e-mail:

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

100 WORKS

Stripling

Answer 30

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

Chilon

Answer 31

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

Psyche

Answer 32

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

Cytoplasm

Answer 33

Go to the app\Providers\AppServiceProvider.php , add this code in public function

boot():

{
    //
    Schema::defaultStringLength(191);

};`

and use this :use Illuminate\Support\Facades\Schema;

Mundell

Answer 34

include this line at top

use Illuminate\Support\Facades\Schema;

then inside boot function add this

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

Demulcent

Could you please format your code by highlighting it and hitting Ctrl+K – Benjamin

Answer 35

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

Kerri

You would have to this for ANY model that uses the String datatype, so this is not optimal. The other answers deal with it once for ALL migrations. The "real/best" solution depends on your environment and needs. If you MUST use an older MySQL (or Maria) but want to keep the 250 string length and do not need emojis, then switch the DB charset. Otherwise shorten the max string length in the provider. – Brickbat