Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Question

Migration error on Laravel 5.4 with php artisan make:auth

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter tabl e users add unique users_email_unique(email))

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Answer 1

According to the official Laravel 7.x documentation, you can solve this quite easily.

Update your /app/Providers/AppServiceProvider.php to contain:

use Illuminate\Support\Facades\Schema;

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191);
}

Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.

Answer 2

I don't know why the above solution and the official solution which is adding

Schema::defaultStringLength(191);

in AppServiceProvider didn't work for me. What worked for was editing the database.php file in config folder. Just edit

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

to

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

and it should work, although you will be unable to store extended multibyte characters like emoji.

This is an ugly hack and don't do if you want to store string in non english language, emoji

I did it with Laravel 5.7. Hope it helps.

Don't forget to stop and launch again the server.

Answer 3

I'm just adding this answer here as it's the quickest solution for me. Just set the default database engine to 'InnoDB' on

/config/database.php

'mysql' => [
    ...,
    ...,
    'engine' => 'InnoDB',
 ]

then run php artisan config:cache to clear and refresh the configuration cache

EDIT: Answers found here might explain what's behind the scenes of this one

Answer 4

Laravel 7.X (also works in 8X): Simple Solution.

Option-1:

php artisan db:wipe 

Update these values(Below) of mysql array in /config/database.php

'charset' => 'utf8', 'collation' => 'utf8_general_ci',

And then

php artisan migrate

It's Done! Migration Tables will be created successfully.

---

Option-2:

Use php artisan db:wipe or delete/drop all the tables of your database manually.

Update your AppServiceProvider.php [ Located in app/Providers/AppServiceProvider.php ]

use Illuminate\Support\Facades\Schema;
/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191); 
}

And then

php artisan migrate

It's Done!

Pitfall: I would like to mention of @shock_gone_wild 's comment

Be careful about this solution (Option-2). If you index email fields for example, stored emails can only have a max length of 191 chars. This is less than the official RFC states.

---

Optionally I Tried out these possible ways (like below) but doesn't work.

php artisan config:cache php artisan migrate:fresh

php artisan migrate:reset

Answer 5

This issue is caused in Laravel 5.4 by the database version.

According to the docs (in the Index Lengths & MySQL / MariaDB section):

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.

In other words, in <ROOT>/app/Providers/AppServiceProvider.php:

// Import Schema
use Illuminate\Support\Facades\Schema;
// ...

class AppServiceProvider extends ServiceProvider
{

public function boot()
{
    // Add the following line
    Schema::defaultStringLength(191);
}

// ...

}

But as the comment on the other answer says:

Be careful about this solution. If you index email fields for example, stored emails can only have a max length of 191 chars. This is less than the official RFC states.

So the documentation also proposes another solution:

Alternatively, you may enable the innodb_large_prefix option for your database. Refer to your database's documentation for instructions on how to properly enable this option.

Answer 6

For someone who don't want to change AppServiceProvider.php. (In my opinion, it's bad idea to change AppServiceProvider.php just for migration)

You can add back the data length to the migration file under database/migrations/ as below:

create_users_table.php

$table->string('name',64);
$table->string('email',128)->unique();

create_password_resets_table.php

$table->string('email',128)->index();

Answer 7

I have solved this issue and edited my config->database.php file to like my database ('charset'=>'utf8') and the ('collation'=>'utf8_general_ci'), so my problem is solved the code as follow:

'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' => 'utf8',
        'collation' => 'utf8_general_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

Answer 8

I am adding two sollution that work for me.

1st sollution is:

1. Open database.php file insde config dir/folder.

2. Edit 'engine' => null, to 'engine' => 'InnoDB',

   This worked for me.

2nd sollution is:

1. Open database.php file insde config dir/folder.
   2.Edit
   'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',
   to
   

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

Goodluck

Answer 9

1- Go to /config/database.php and find these lines

'mysql' => [
    ...,
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    ...,
    'engine' => null,
 ]

and change them to:

'mysql' => [
    ...,
    'charset' => 'utf8',
    'collation' => 'utf8_unicode_ci',
    ...,
    'engine' => 'InnoDB',
 ]

2- Run php artisan config:cache to reconfigure laravel

3- Delete the existing tables in your database and then run php artisan migrate again

Answer 10

I have found two solutions for this error

OPTION 1:

Open your user and password_reset table in database/migrations folder

And just change the length of the email:

$table->string('email',191)->unique();

---

OPTION 2:

Open your app/Providers/AppServiceProvider.php file and inside the boot() method set a default string length:

use Illuminate\Support\Facades\Schema;

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

Answer 11

The solution no one tells is that in Mysql v5.5 and later InnoDB is the default storage engine which does not have this problem but in many cases like mine there are some old mysql ini configuration files which are using old MYISAM storage engine like below.

default-storage-engine=MYISAM

which is creating all these problems and the solution is to change default-storage-engine to InnoDB in the Mysql's ini configuration file once and for all instead of doing temporary hacks.

default-storage-engine=InnoDB

And if you are on MySql v5.5 or later then InnoDB is the default engine so you do not need to set it explicitly like above, just remove the default-storage-engine=MYISAM if it exist from your ini file and you are good to go.

Answer 12

works like charm for me!

Add this to config/database.php

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

instead of

'engine' => 'null',

Answer 13

Instead of setting a limit on length I would propose the following, which has worked for me.

Inside:

config/database.php

replace this line for mysql:

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

with:

'engine' => null,

Answer 14

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

use Illuminate\Support\Facades\Schema;

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

Note: first you have to delete (if you have) users table, password_resets table from the database and delete users and password_resets entries from migrations table.

To run all of your outstanding migrations, execute the migrate Artisan command:

php artisan migrate

After that everything should work as normal.

Answer 15

As already specified we add to the AppServiceProvider.php in App/Providers

use Illuminate\Support\Facades\Schema;  // add this

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191); // also this line
}

you can see more details in the link bellow (search for "Index Lengths & MySQL / MariaDB") https://laravel.com/docs/5.5/migrations

BUT WELL THAT's not what I published all about! the thing is even when doing the above you will likely to get another error (that's when you run php artisan migrate command and because of the problem of the length, the operation will likely stuck in the middle. solution is below, and the user table is likely created without the rest or not totally correctly) we need to roll back. the default roll back will not work. 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 below:

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 you're good to go

php artisan migrate:rollback

php artisan migrate

Answer 16

If you want to change in AppServiceProvider then you need to define the length of email field in migration. just replace the first line of code to the second line.

create_users_table

$table->string('email')->unique();
$table->string('email', 50)->unique();

create_password_resets_table

$table->string('email')->index();
$table->string('email', 50)->index();

After successfully changes you can run the migration.
Note: first you have to delete (if you have) users table, password_resets table from the database and delete users and password_resets entries from migration table.

Answer 17

Schema::defaultStringLength(191); will define the length of all strings 191 by default which may ruin your database. You must not go this way.

Just define the length of any specific column in the database migration class. For example, I'm defining the "name", "username" and "email" in the CreateUsersTable class as below:

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

Answer 18

The recommended solution is to enable innodb_large_prefix option of MySQL so you won't be getting into subsequent problems. And here is how to do that:

Open the my.ini MySQL configuration file and add the below lines under the [mysqld] line like this.

[mysqld]
innodb_file_format = Barracuda
innodb_large_prefix = 1
innodb_file_per_table = ON

After that, save your changes and restart your MySQL service.

Rollback if you need to and then re-run your migration.

---

Just in case your problem still persists, go to your database configuration file and set

'engine' => null, to 'engine' => 'innodb row_format=dynamic'

Hope it helps!

Answer 19

I have just modified following line in users and password_resets migration file.

Old : $table->string('email')->unique();

New : $table->string('email', 128)->unique();

Answer 20

In order to avoid changing anything in your code, simply update your MySQL server to at least 5.7.7

Reference this for more info : https://laravel-news.com/laravel-5-4-key-too-long-error

Answer 21

This is common since Laravel 5.4 changed the default database charater set to utf8mb4. What you have to do, is: edit your App\Providers.php by putting this code before the class declaration

use Illuminate\Support\Facades\Schema;

Also, add this to the 'boot' function Schema::defaultStringLength(191);

Answer 22

If you don't have any data assigned already to you database do the following:

1. Go to app/Providers/AppServiceProvide.php and add

use Illuminate\Support\ServiceProvider;

and inside of the method boot();

Schema::defaultStringLength(191);

2. Now delete the records in your database, user table for ex.

3. run the following

php artisan config:cache

php artisan migrate

Answer 23

I think to force StringLenght to 191 is a really bad idea. So I investigate to understand what is going on.

I noticed that this message error :

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Started to show up after I updated my MySQL version. So I've checked the tables with PHPMyAdmin and I've noticed that all the new tables created were with the collation utf8mb4_unicode_ci instead of utf8_unicode_ci for the old ones.

In my doctrine config file, I noticed that charset was set to utf8mb4, but all my previous tables were created in utf8, so I guess this is some update magic that it start to work on utf8mb4.

Now the easy fix is to change the line charset in your ORM config file. Then to drop the tables using utf8mb4_unicode_ci if you are in dev mode or fixe the charset if you can't drop them.

For Symfony 4

change charset: utf8mb4 to charset: utf8 in config/packages/doctrine.yaml

Now my doctrine migrations are working again just fine.

Answer 24

first delete all tables of the database in the localhost

Change Laravel default database (utf8mb4) properties in file config/database.php to:

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

after then Changing my local database properties utf8_unicode_ci. php artisan migrate it is ok.

Answer 25

Try with default string length 125 (for MySQL 8.0).

defaultStringLength(125)

Answer 26

Open this file here: /app/Providers/AppServiceProvider.php

And Update this code as my image:

use Illuminate\Support\Facades\Schema;

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

Answer 27

in database.php

-add this line:

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

Answer 28

For anyone else who might run into this, my issue was that I was making a column of type string and trying to make it ->unsigned() when I meant for it to be an integer.

Answer 29

The approached that work here was pass a second param with the key name (a short one):

$table->string('my_field_name')->unique(null,'key_name');

Answer 30

I was getting this error even though I already had (actually because I already had) Schema::defaultStringLength(191); in my AppServiceProvider.php file.

The reason is because I was trying to set a string value in one of my migrations to a value higher than 191:

Schema::create('order_items', function (Blueprint $table) {
    $table->primary(['order_id', 'product_id', 'attributes']);
    $table->unsignedBigInteger('order_id');
    $table->unsignedBigInteger('product_id');
    $table->string('attributes', 1000); // This line right here
    $table->timestamps();
});

Removing the 1000 or setting it to 191 solved my issue.