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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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- gt primary   order id    product id    attributes          table- gt unsignedBigInteger  order id         table- gt unsignedBigInteger  product id         table- gt string  attributes   1000      This line right here      table- gt timestamps          Removing the 1000 or setting it to 191 solved my issue

User · Answer

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- gt string  name  64    table- gt string  email  128 - gt unique      create password resets table php   table- gt string  email  128 - gt index

User · Answer

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

User · Answer

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

User · Answer

This issue is caused in Laravel 5 4 by the database version   According to the docs  in the Index Lengths  amp  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  lt ROOT gt  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

User · Answer

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

User · Answer

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    gt  null  to  engine    gt   innodb row format dynamic   Hope it helps

User · Answer

For Lumen   Add to  env file  DB ENGINE InnoDB

User · Answer

I have just modified following line in users and password resets migration file   Old    table- gt string  email  - gt unique     New    table- gt string  email   128 - gt unique

User · Answer

1- Go to  config database php and find these lines   mysql    gt                  charset    gt   utf8mb4        collation    gt   utf8mb4 unicode ci                 engine    gt  null       and change them to    mysql    gt                  charset    gt   utf8        collation    gt   utf8 unicode ci                 engine    gt   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

User · Answer

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

User · Answer

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

User · Answer

You can set a string length of the indexed field as follows      table- gt string  email   200 - gt unique

User · Answer

If you have this error running  quot php artisan migrate quot   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

User · Answer

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    gt   utf8    collation    gt   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

User · Answer

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

User · Answer

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- gt string  email  - gt unique     table- gt string  email   50 - gt unique      create password resets table   table- gt string  email  - gt index     table- gt string  email   50 - gt 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

User · Answer

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

User · Answer

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

User · Answer

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    gt   utf8mb4    collation    gt   utf8mb4 unicode ci     to    charset    gt   utf8    collation    gt   utf8 unicode ci     and it should work  although you will be unable to store extended multibyte characters like emoji   I did it with Laravel 5 7  Hope it helps

User · Answer

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    gt   InnoDB ROW FORMAT DYNAMIC     with    engine    gt  null

User · Answer

I am adding two sollution that work for me   1st sollution is     Open database php file insde config dir folder  Edit  engine    gt  null  to  engine    gt   InnoDB    This worked for me    2nd sollution is    Open database php file insde config dir folder  2 Edit  charset    gt   utf8mb4    collation    gt   utf8mb4 unicode ci    to    charset    gt   utf8    collation    gt   utf8 unicode ci      Goodluck

User · Answer

The approached that work here was pass a second param with the key name  a short one     table- gt string  my field name  - gt unique null  key name

User · Answer

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- gt increments  id                 table- gt string  name   191                table- gt string  username   30 - gt unique                 table- gt string  email   191 - gt unique                 table- gt string  password                 table- gt rememberToken                 table- gt timestamps

User · Answer

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 - gt unsigned   when I meant for it to be an integer

User · Answer

I have solved this issue and edited my config- gt database php file to like my database   charset   gt  utf8   and the   collation   gt  utf8 general ci    so my problem is solved the code as follow   mysql    gt             driver    gt   mysql            host    gt  env  DB HOST    127 0 0 1             port    gt  env  DB PORT    3306             database    gt  env  DB DATABASE    forge             username    gt  env  DB USERNAME    forge             password    gt  env  DB PASSWORD                 unix socket    gt  env  DB SOCKET                 charset    gt   utf8            collation    gt   utf8 general ci            prefix    gt               strict    gt  true           engine    gt  null

User · Answer

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    gt                           engine    gt   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

User · Answer

works like charm for me  Add this to config database php  engine    gt   InnoDB ROW FORMAT DYNAMIC    instead of  engine    gt   null

User · Answer

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

User · Answer

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- gt string  email  191 - gt 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

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

Examples related to mysql

Examples related to laravel

Examples related to pdo

Examples related to laravel-5

Examples related to laravel-5.4