Laravel migration error - Исправление ошибок и поиск оптимальных решений проблем

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

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

use IlluminateSupportFacadesSchema;

/**
 * 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.

Ryan

21.5k29 gold badges172 silver badges343 bronze badges

answered Feb 15, 2017 at 9:52

abSiddiqueabSiddique

11.1k3 gold badges23 silver badges30 bronze badges

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.

user

4,9056 gold badges17 silver badges35 bronze badges

answered Sep 17, 2017 at 8:29

Koushik DasKoushik Das

8,8923 gold badges47 silver badges45 bronze badges

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

answered Oct 9, 2017 at 7:50

Dexter BengilDexter Bengil

5,6856 gold badges36 silver badges53 bronze badges

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 IlluminateSupportFacadesSchema;
/**
 * 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

answered Jan 7, 2021 at 8:47

perfectionist1perfectionist1

7272 gold badges9 silver badges14 bronze badges

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 IlluminateSupportFacadesSchema;
// ...

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.

answered Feb 20, 2017 at 19:39

Esteban HerreraEsteban Herrera

2,1882 gold badges22 silver badges31 bronze badges

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

answered Apr 21, 2017 at 16:46

helloroyhelloroy

3772 silver badges6 bronze badges

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

answered Apr 20, 2018 at 6:24

works like charm for me!

Add this to config/database.php

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

instead of

'engine' => 'null',

answered Jan 22, 2021 at 2:39

I am adding two sollution that work for me.

1st sollution is:

Open database.php file insde config dir/folder.
Edit 'engine' => null, to 'engine' => 'InnoDB',

This worked for me.

2nd sollution is:

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

to

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

Goodluck

answered Jun 28, 2018 at 13:33

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

answered Feb 8, 2019 at 8:10

mohammad asgharimohammad asghari

1,7041 gold badge16 silver badges23 bronze badges

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

And Update this code as my image:

use IlluminateSupportFacadesSchema;

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

answered Apr 16, 2022 at 5:05

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

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

answered Feb 8, 2018 at 6:37

Udhav SarvaiyaUdhav Sarvaiya

9,03812 gold badges55 silver badges62 bronze badges

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.

answered Nov 8, 2019 at 20:06

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,

GuruBob

8431 gold badge10 silver badges21 bronze badges

answered Jul 24, 2017 at 20:21

in database.php

-add this line:

‘engine’ => ‘InnoDB ROW_FORMAT=DYNAMIC’,

answered May 4, 2022 at 3:42

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

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.

answered Jan 19, 2018 at 5:43

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

use IlluminateSupportFacadesSchema;  // 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

answered Jan 17, 2018 at 20:37

Mohamed AllalMohamed Allal

16.2k4 gold badges87 silver badges91 bronze badges

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.

answered Sep 12, 2022 at 7:21

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.

answered Jun 25, 2017 at 8:03

Chintan KotadiyaChintan Kotadiya

1,3281 gold badge11 silver badges19 bronze badges

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

answered Apr 23, 2018 at 9:08

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!

answered Nov 28, 2018 at 7:59

SammieSammie

1,4911 gold badge21 silver badges17 bronze badges

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

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

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

answered Nov 16, 2018 at 18:34

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

use IlluminateSupportFacadesSchema;

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

answered Feb 8, 2018 at 10:44

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

Go to app/Providers/AppServiceProvide.php and add

use IlluminateSupportServiceProvider;

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

answered Apr 21, 2019 at 1:41

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

defaultStringLength(125)

answered May 20, 2021 at 17:45

AjayAjay

8288 silver badges17 bronze badges

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.

answered May 17, 2018 at 9:03

Kaizoku GambareKaizoku Gambare

3,0043 gold badges28 silver badges40 bronze badges

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.

answered Jul 7, 2019 at 20:59

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.

answered Apr 25, 2018 at 19:20

Brynn BatemanBrynn Bateman

7391 gold badge8 silver badges22 bronze badges

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

answered Jul 24, 2018 at 13:23

Tiago GouvêaTiago Gouvêa

14.3k4 gold badges72 silver badges79 bronze badges

Источник

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

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

use IlluminateSupportFacadesSchema;

/**
 * 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.

Ryan

21.5k29 gold badges172 silver badges343 bronze badges

answered Feb 15, 2017 at 9:52

abSiddiqueabSiddique

11.1k3 gold badges23 silver badges30 bronze badges

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.

user

4,9056 gold badges17 silver badges35 bronze badges

answered Sep 17, 2017 at 8:29

Koushik DasKoushik Das

8,8923 gold badges47 silver badges45 bronze badges

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

answered Oct 9, 2017 at 7:50

Dexter BengilDexter Bengil

5,6856 gold badges36 silver badges53 bronze badges

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 IlluminateSupportFacadesSchema;
/**
 * 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

answered Jan 7, 2021 at 8:47

perfectionist1perfectionist1

7272 gold badges9 silver badges14 bronze badges

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 IlluminateSupportFacadesSchema;
// ...

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.

answered Feb 20, 2017 at 19:39

Esteban HerreraEsteban Herrera

2,1882 gold badges22 silver badges31 bronze badges

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

answered Apr 21, 2017 at 16:46

helloroyhelloroy

3772 silver badges6 bronze badges

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

answered Apr 20, 2018 at 6:24

works like charm for me!

Add this to config/database.php

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

instead of

'engine' => 'null',

answered Jan 22, 2021 at 2:39

I am adding two sollution that work for me.

1st sollution is:

Open database.php file insde config dir/folder.
Edit 'engine' => null, to 'engine' => 'InnoDB',

This worked for me.

2nd sollution is:

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

to

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

Goodluck

answered Jun 28, 2018 at 13:33

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

answered Feb 8, 2019 at 8:10

mohammad asgharimohammad asghari

1,7041 gold badge16 silver badges23 bronze badges

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

And Update this code as my image:

use IlluminateSupportFacadesSchema;

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

answered Apr 16, 2022 at 5:05

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

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

answered Feb 8, 2018 at 6:37

Udhav SarvaiyaUdhav Sarvaiya

9,03812 gold badges55 silver badges62 bronze badges

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

answered Nov 8, 2019 at 20:06

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,

GuruBob

8431 gold badge10 silver badges21 bronze badges

answered Jul 24, 2017 at 20:21

in database.php

-add this line:

‘engine’ => ‘InnoDB ROW_FORMAT=DYNAMIC’,

answered May 4, 2022 at 3:42

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

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.

answered Jan 19, 2018 at 5:43

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

use IlluminateSupportFacadesSchema;  // 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

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

answered Jan 17, 2018 at 20:37

Mohamed AllalMohamed Allal

16.2k4 gold badges87 silver badges91 bronze badges

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.

answered Sep 12, 2022 at 7:21

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

answered Jun 25, 2017 at 8:03

Chintan KotadiyaChintan Kotadiya

1,3281 gold badge11 silver badges19 bronze badges

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

answered Apr 23, 2018 at 9:08

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!

answered Nov 28, 2018 at 7:59

SammieSammie

1,4911 gold badge21 silver badges17 bronze badges

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

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

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

answered Nov 16, 2018 at 18:34

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

use IlluminateSupportFacadesSchema;

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

answered Feb 8, 2018 at 10:44

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

Go to app/Providers/AppServiceProvide.php and add

use IlluminateSupportServiceProvider;

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

answered Apr 21, 2019 at 1:41

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

defaultStringLength(125)

answered May 20, 2021 at 17:45

AjayAjay

8288 silver badges17 bronze badges

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

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.

answered May 17, 2018 at 9:03

Kaizoku GambareKaizoku Gambare

3,0043 gold badges28 silver badges40 bronze badges

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.

answered Jul 7, 2019 at 20:59

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.

answered Apr 25, 2018 at 19:20

Brynn BatemanBrynn Bateman

7391 gold badge8 silver badges22 bronze badges

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

answered Jul 24, 2018 at 13:23

Tiago GouvêaTiago Gouvêa

14.3k4 gold badges72 silver badges79 bronze badges

Источник

$ php artisan migrate:status
+——+————————————————+——-+
| Ran? | Migration | Batch |
+——+————————————————+——-+
| N | 2014_10_12_000000_create_users_table | |
| N | 2014_10_12_100000_create_password_resets_table | |
| N | 2018_04_23_062130_create_categories_table | |
+——+————————————————+——-+

======================================
$ php artisan migrate

IlluminateDatabaseQueryException : SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘users’ already exists (SQL: create table `users` (`id` int unsigned not null auto_increment primary key, `name` varchar(255) not null, `email` varchar(255) not null, `password` varchar(255) not null, `remember_token` varchar(100) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8mb4 collate utf8mb4_unicode_ci)

at D:OSPaneldomainscatalog.locvendorlaravelframeworksrcIlluminateDatabaseConnection.php:664
660| // If an exception occurs when attempting to run a query, we’ll format the error
661| // message to include the bindings with SQL, which will make this exception a
662| // lot more helpful to the developer instead of just the database’s errors.
663| catch (Exception $e) {
> 664| throw new QueryException(
665| $query, $this->prepareBindings($bindings), $e
666| );
667| }
668|

Exception trace:

1 PDOException::(«SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘users’ already exists»)
D:OSPaneldomainscatalog.locvendorlaravelframeworksrcIlluminateDatabaseConnection.php:458

2 PDOStatement::execute()
D:OSPaneldomainscatalog.locvendorlaravelframeworksrcIlluminateDatabaseConnection.php:458

Please use the argument -v to see more details.

GaneMax@GANEMAX D:OSPaneldomainscatalog.loc

Имя файла миграции для категорий?
2018_04_23_062130_create_categories_table
Создавал файл миграции так
$ php artisan make:migration create_categories_table —create=categories

Источник

Странная ошибка, возникающая при выполнении первой миграции в Laravel 8, тянется ещё со времён Laravel 5.4. И её почему-то до сих пор не устранили. Связано ли это с тем, что программисты, работающие с Laravel, предпочитают исключительно MySQL и люто ненавидят MariaDB, или ещё по какой причине, но ошибка случается и поправить её на самом деле, не составляет труда.

Ошибка выглядит следующим образом. При выполнении команды:

php artisan migrate

Миграции начинают обрабатываться:

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table

Но тут же вылетает сообщение об ошибке:

   IlluminateDatabaseQueryException

  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_unique`(`email`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
    667▕         // If an exception occurs when attempting to run a query, we'll format the error
    668▕         // message to include the bindings with SQL, which will make this exception a
    669▕         // lot more helpful to the developer instead of just the database's errors.
    670▕         catch (Exception $e) {
  ➜ 671▕             throw new QueryException(
    672▕                 $query, $this->prepareBindings($bindings), $e
    673▕             );
    674▕         }
    675▕

      +9 vendor frames
  10  database/migrations/2014_10_12_000000_create_users_table.php:26
      IlluminateSupportFacadesFacade::__callStatic("create")

      +21 vendor frames
  32  artisan:37
      IlluminateFoundationConsoleKernel::handle(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

Решение проблемы с ошибкой `php artisan migrate` — SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes при применении миграции в Laravel

Для устранения этой ошибки нужно внести изменения в метод boot класса AppServiceProvider. Для этого открываем файл, находящийся по адресу:

/папка_проекта/app/Providers/AppServiceProvider.php

Изначально он имеет вид:

<?php

namespace AppProviders;

use IlluminateSupportServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
    }
}

В него нужно вписать 2 строчки (использование библиотеки фасадов use IlluminateSupportFacadesSchema; и модификацию метода boot: Schema::defaultStringLength(191);) таким вот образом:

use IlluminateSupportFacadesSchema;

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

Таким образом модифицированный файл с классом AppServiceProvider будет иметь вид (добавляется 6-я и 26-я строки):

<?php

namespace AppProviders;

use IlluminateSupportServiceProvider;
use IlluminateSupportFacadesSchema;
class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }

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

После сохранения этих изменений, команда php artisan migrate работает без ошибок, все миграции применяются и проект на фреймворке Laravel оживает. =)

Важно(!)
Перед тем, как накатывать повторно миграции, удалите созданные ранее таблицы из базы данных.
Иначе будет новая ошибка!

По материалам:
laravel-news.com

Заберите ссылку на статью к себе, чтобы потом легко её найти!
Выберите, то, чем пользуетесь чаще всего:

Источник

Comments

LukeTowers

added a commit
to octobercms/october
that referenced
this issue

Feb 17, 2018

Fixes: #1927. Related: laravel/framework#17508. Issue occurs when database configuration related to full support for the utf8mb4 charset is incorrect; MySQL > 5.7 & MariaDB > 10.2 doesn't have this issue because they default to the correct configuration values; this fix solves the issue for older versions of MySQL and MariaDB without requiring database server configuration changes.

The root cause of the issue with the utf8mb4 encoding is that both InnoDB and MyISAM have too low of an index key prefix limit (767 bytes and 1000 bytes respectively) to properly store 255 4-byte characters; which would take 1024 bytes. See the docs on InnoDB limitations: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

In MySQL >= 5.7 & MariaDB >= 10.2 this limit has been bumped to 3076 bytes by the changing of the default value of the `innodb_large_prefix` configuration property (introduced in MySQL 5.5) to true; which is what bumps up the limit. In order to manually set that property to true on earlier versions, `innodb_file_format` must be set to `BARRACUDA` and `row_format` must be `DYNAMIC` or `COMPRESSED`. See http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/ for more information. 

This change fixes the issue by changing the default string length to 191 (total of 764 bytes, within the older size limit) when the MySQL database config is detected to be using the utf8mb4 charset.

octoberapp

pushed a commit
to octoberrain/system
that referenced
this issue

Mar 7, 2018

Fixes: octobercms/october#1927. Related: laravel/framework#17508. Issue occurs when database configuration related to full support for the utf8mb4 charset is incorrect; MySQL > 5.7 & MariaDB > 10.2 doesn't have this issue because they default to the correct configuration values; this fix solves the issue for older versions of MySQL and MariaDB without requiring database server configuration changes.

The root cause of the issue with the utf8mb4 encoding is that both InnoDB and MyISAM have too low of an index key prefix limit (767 bytes and 1000 bytes respectively) to properly store 255 4-byte characters; which would take 1024 bytes. See the docs on InnoDB limitations: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

In MySQL >= 5.7 & MariaDB >= 10.2 this limit has been bumped to 3076 bytes by the changing of the default value of the `innodb_large_prefix` configuration property (introduced in MySQL 5.5) to true; which is what bumps up the limit. In order to manually set that property to true on earlier versions, `innodb_file_format` must be set to `BARRACUDA` and `row_format` must be `DYNAMIC` or `COMPRESSED`. See http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/ for more information. 

This change fixes the issue by changing the default string length to 191 (total of 764 bytes, within the older size limit) when the MySQL database config is detected to be using the utf8mb4 charset.

adsa95

pushed a commit
to adsa95/october-module-system
that referenced
this issue

Apr 29, 2018

Fixes: octobercms/october#1927. Related: laravel/framework#17508. Issue occurs when database configuration related to full support for the utf8mb4 charset is incorrect; MySQL > 5.7 & MariaDB > 10.2 doesn't have this issue because they default to the correct configuration values; this fix solves the issue for older versions of MySQL and MariaDB without requiring database server configuration changes.

The root cause of the issue with the utf8mb4 encoding is that both InnoDB and MyISAM have too low of an index key prefix limit (767 bytes and 1000 bytes respectively) to properly store 255 4-byte characters; which would take 1024 bytes. See the docs on InnoDB limitations: https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html

In MySQL >= 5.7 & MariaDB >= 10.2 this limit has been bumped to 3076 bytes by the changing of the default value of the `innodb_large_prefix` configuration property (introduced in MySQL 5.5) to true; which is what bumps up the limit. In order to manually set that property to true on earlier versions, `innodb_file_format` must be set to `BARRACUDA` and `row_format` must be `DYNAMIC` or `COMPRESSED`. See http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/ for more information. 

This change fixes the issue by changing the default string length to 191 (total of 764 bytes, within the older size limit) when the MySQL database config is detected to be using the utf8mb4 charset.

laravel

locked and limited conversation to collaborators

Mar 4, 2019

Источник

Я пробую изучать Laravel. Начал новый проект по туториалу https://laracasts.com/series/laravel-from-scratch-… .

При вводе комманды :

php artisan migrate

я получаю ошибку:

IlluminateDatabaseQueryException  : could not find driver (SQL: select * from information_schema.tables where table_schema = blog and table_name = migrations)

  at /home/morilon/php_proj/blog/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

  Exception trace:

  1   PDOException::("could not find driver")
      /home/morilon/php_proj/blog/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68

  2   PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=blog", "root", "", [])
      /home/morilon/php_proj/blog/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68

  Please use the argument -v to see more details.

Что значит : could not find driver (SQL: select * from information_schema.tables where table_schema = blog and table_name = migrations) ?

После установки я удалил и установил заново MySQL, просто пароль забыл.
При переустановке использовал такие комманды:

pkg —get-selections | grep mysql
sudo apt-get remove —purge [everything that came up]
sudo apt-get autoremove && sudo apt-get autoclean
sudo apt-get install mysql-server
sudo apt install php-mysql

Что то устанавливал, не знаю точно что помогло. Наверно

sudo apt-get install php-pear php7.2-curl php7.2-dev php7.2-gd php7.2-mbstring php7.2-zip php7.2-mysql php7.2-xml

Теперь показывает

IlluminateDatabaseQueryException  : SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: NO) (SQL: select * from information_schema.tables where table_schema = blog and table_name = migrations)

Источник

Laravel migrations is one of the powerful feature to work with Laravel Database. Working with migrations in laravel generates number of errors including Specified key was too Long. Facing the error after running laravel migration “Error Exception Failed to Open Stream”.
Why Failed to Open Stream?.
Failed to open stream occurs when you already run migration and then delete your table migration file from (your-project > app > database > migrations). Manually deleting migration file generates error.

[ErrorException] include([directory of file]): failed to open stream: No such file or directory

1. Failed to Open Stream – Laravel Solution:

Delete table file from migration folder in laravel without using migration:rollback generates the error. “failed to open stream: No such file or directory”. Artisan refuse to create the file, until you use dump-autoload in composer.

Open your command prompt to run composer commands.
Running the command dump-autoload can save your day.

composer dump-autoload

2. Delete Migration Entry from phpMyAdmin:

Laravel Migration “Failed to Open Stream” error could be solved via deleting the entry from phpMyAdmin. Delete the row from migration table points laravel, table is not exists. Laravel then allows migration to run smoothly.

Open your phpMyAdmin and navigate to your Database.
Browse your Migration Table.
Delete the entry which is already registered with the name of your table.

3. Run Migrate Rollback

Migrate rollback is one of the best feature to delete all entries. Rollback and Re-run your migration. Rollback helps you to create a stable state for your database.
Run php artisan migrate:rollback until your database meets your desired state.

Wrap Up:
Guideline for the beginners to use rollback call and run your migration again and again until you find a stable state. Update your table and run your migration can solve numbers of problems.
Consider reading Specified key was too Long laravel 5. When you define foreign key to a table you will face that error.

Источник

Решение проблемы с ошибкой php artisan migrate — SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes при применении миграции в Laravel

Comments

1. Failed to Open Stream – Laravel Solution:

2. Delete Migration Entry from phpMyAdmin:

3. Run Migrate Rollback

Читайте также:

Решение проблемы с ошибкой `php artisan migrate` — SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes при применении миграции в Laravel