Reputation: 12335

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

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

Upvotes: 341

Answers (30)

Dexter Bengil

Reputation: 6615

I'm sharing this as it's the quickest solution for my case. Simply set the default database engine to 'InnoDB' in

/config/database.php

'mysql' => [
    ...,
    ...,
    'engine' => env('DB_ENGINE', 'InnoDB'),
 ]

After making this change, run php artisan config:clear to clear and refresh the configuration cache.

EDIT: For a deeper explanation of this solution, you can check out the answers provided here.

Upvotes: 305

Alireza Tahriri

Reputation: 81

According to this article, you can fix this very easily.

You have to just upadte /app/Providers/AppServiceProvider.php file like following code:

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

done!!!

Upvotes: 0

perfectionist1

Reputation: 947

Laravel 7.X | 8X | 9X: 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

Upvotes: 68

Ulrich

Reputation: 21

To resolve this problem with Laravel 8

open config\database.php and change

'charset' => 'utf8mb4',

'collation' => 'utf8mb4_unicode_ci',

to

'charset' => 'utf8',

'collation' => 'utf8_unicode_ci',
run php artisan config:cache
php artisan migrate:fresh --seed

kinds regards

Upvotes: 1

Koushik Das

Reputation: 10793

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

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

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

Upvotes: 253

Developer Sam

Reputation: 121

In laravel 9

First set the default database engine to InnoDB on

/config/database.php

'engine' => 'InnoDB',

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

php artisan db:wipe

Change these values of mysql array in /config/database.php as follows 'charset' => 'utf8', 'collation' => 'utf8_general_ci', Then

php artisan migrate That's all! Migration Tables will be created successfully.

Upvotes: 10

Mizael Clistion

Reputation: 101

in database.php

-add this line:

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

Upvotes: 7

Amranur Rahman

Reputation: 1127

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

Upvotes: 13

Kryptobarons

Reputation: 36

The cleanest solution is to go to your database and change:

default_storage_engine to InnoDB

You most likely have there MyISAM.

Upvotes: 0

Ajay

Reputation: 888

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

defaultStringLength(125)

Upvotes: 2

Alaa ElAlfi

Reputation: 277

works like charm for me!

Add this to config/database.php

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

instead of

'engine' => 'null',

Upvotes: 23

Robin Delaporte

Reputation: 585

If you have this error running "php artisan migrate". You can alter the table you want to update by writing this :

    DB::statement('ALTER TABLE table_name ROW_FORMAT = DYNAMIC;');

In your migration script. Example :

class MyMigration extends Migration {

/**
 * Run the migrations.
 */
public function up()
{
    DB::statement('ALTER TABLE table_name ROW_FORMAT = DYNAMIC;');        
    Schema::table('table_name', function ($table) {
        //....
    });
}

/**
 * Undo the migrations.
 */
public function down()
{
    //....
}
}

And then run php artisan migrate again

Upvotes: 0

Ahmad Shakib

Reputation: 485

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

Upvotes: 22

abSiddique

Reputation: 12335

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.

Upvotes: 601

Ali A. Dhillon

Reputation: 643

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.

Upvotes: 11

user3216114

Reputation: 325

Need to create database using following command.

CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

and then run following command to migrate database tables.

php artisan migrate

Upvotes: 0

Md. Noor-A-Alam Siddique

Reputation: 1077

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,

Upvotes: 9

NoBugs

Reputation: 9496

Just a few lines in the /etc/mysql/mariadb.conf.d/50-server.cnf or wherever your config is:

innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
innodb_default_row_format = 'DYNAMIC'

and sudo service mysql restart

https://stackoverflow.com/a/57465235/778234

See the docs: https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html

Upvotes: 0

Udhav Sarvaiya

Reputation: 10061

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

Upvotes: 11

Mahesh Gaikwad

Reputation: 41

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

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

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

Upvotes: 3

Goldman.Vahdettin

Reputation: 21

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.

Upvotes: 1

Scotty G

Reputation: 424

For Lumen:

Add to .env file

DB_ENGINE=InnoDB

Upvotes: 0

Edmunds22

Reputation: 791

You can set a string length of the indexed field as follows:

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

Upvotes: 0

Adam Winster

Reputation: 69

Changing my local database server type from "mariadb" to "mysql" fixed this for me without having to edit any Laravel files.

I followed this tutorial to change my db server type: https://odan.github.io/2017/08/13/xampp-replacing-mariadb-with-mysql.html

Upvotes: 0

Jacey

Reputation: 679

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.

Upvotes: 0

Levinski Polish

Reputation: 21

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

Go to app/Providers/AppServiceProvide.php and add

use Illuminate\Support\ServiceProvider;

and inside of the method boot();

Schema::defaultStringLength(191);

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

php artisan config:cache

php artisan migrate

Upvotes: 2

mohammad asghari

Reputation: 1884

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

Upvotes: 15

samuelabate

Reputation: 1631

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!

Upvotes: 3

helloroy

Reputation: 427

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

Upvotes: 25

Mohamed Allal

Reputation: 20830

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

Upvotes: 5

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

Answers (30)

Related Questions