Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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

[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

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.
                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.
– shock_gone_wild
                Feb 16, 2017 at 12:30
                This solution suggest by Laravel github.com/laravel/framework/issues/17508 laracasts.com/discuss/channels/laravel/…
– abSiddique
                Feb 17, 2017 at 7:47
                It is working and is a valid solution, but I just wanted to point out, that there are possible pitfalls using this approach.
– shock_gone_wild
                Feb 17, 2017 at 9:48
                This is an ugly hack which hobbles your code to accommodate a bad/outdated DB configuration, and it's being presented as if it was an actual solution.
– Andrew Koster
                Oct 1, 2020 at 4:08
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.
                Using this character set will only allow you to save Standard ASCII, and not multibyte special characters like those from Arabic, Hebrew, most European scripts, and of course emoji. see also stackoverflow.com/a/15128103/4233593
– Jeff Puckett
                Sep 27, 2017 at 19:01
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
                A quick update: I wrote "InnoDB ROW_FORMAT=DYNAMIC" insted of just "InnoDB", as mentioned in other comments below, and it worked. However I don't know what it means and how it affect the Laravel application. If anyone know, I would really appreciate the heads up.
– Bill M.
                Dec 31, 2021 at 22:29
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
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.
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();
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,
works like charm for me!
Add this to config/database.php
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
instead of
'engine' => 'null',
                Setting 'engine' => 'InnoDB' only didn' t work for me but adding ROW_FORMAT=DYNAMIC like this worked.
– Umair Malhi
                Aug 8, 2022 at 18:17
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',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
Goodluck

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);
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);
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.
                Thanks! I had to use this suggestion in conjunction with the string length, charset, and collation changes in order to get this to work with laravel 6 and mysql 5.6. Hopefully this helps others in the future.
– Casper Wilkes
                Mar 6, 2020 at 23:43
                @CasperWilkes you don't have to do any of that string length, charset stuff. Check your Mysql system variable like this show global variables like 'innodb_large_prefix'; it should be ON. If it's OFF then you can check this answer on how turn it on. And here is more information about innodb_large_prefix on dev.mysql.com.
– Ali A. Dhillon
                Mar 17, 2020 at 19:10
                Thanks man, I didn't want to make any change in laravel configurations so your solution solved the problem with laravel 9.9
– Hooman
                Apr 20, 2022 at 3:09
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,
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',
php artisan migrate
That's all! Migration Tables will be created successfully.
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.
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
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.
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();
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!
I have just modified following line in users and password_resets migration file.
Old : $table->string('email')->unique();
New : $table->string('email', 128)->unique();
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
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);
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
                It worked but need to add  use Illuminate\Support\Facades\Schema; use Illuminate\Support\ServiceProvider; is already there. Hope you correct it
– Jimish Gamit
                May 14, 2019 at 10:44
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.
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.