Laravel migration unique key is too long even if specified

Question

I am trying to migrate a users table in Laravel  When I run my migration I get this error       Illuminate Database QueryException    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 uniq email     my migration is as follows   Schema  create  users   function Blueprint  table         table- gt increments  id         table- gt string  name   32        table- gt string  username   32        table- gt string  email   320        table- gt string  password   64        table- gt string  role   32        table- gt string  confirmation code         table- gt boolean  confirmed  - gt default true        table- gt timestamps          table- gt unique  email    users email uniq          After some googling I came across this bug report where Taylor says you can specify the index key as the 2nd parameter of unique    which I have done  It still gives the error  What is going on here

User · Answer

for laravel 5 7 write these code in appserviceprovider php    use Illuminate Support Facades Schema   public function boot       Schema  defaultStringLength 191

User · Answer

I added to the migration itself  Schema  defaultStringLength 191   Schema  create  users   function  Blueprint  table         table- gt increments  id         table- gt string  name         table- gt string  email  - gt unique         table- gt string  password         table- gt rememberToken         table- gt timestamps          yes  I know I need to consider it on every migration but I would rather that than have it tucked away in some completely unrelated service provider

User · Answer

I d like to point that something that i missed      I m new at Laravel  and i didn t copy the  use Illuminate       because i really didn at paid atention  because right above the function boot you alread have a use Statement   Hope that helps anyone    use Illuminate Support Facades Schema     public function boot         Schema  defaultStringLength 191

User · Answer

All was well described in the others Anwser you can see more details in the link bellow  search with key  Index Lengths  amp  MySQL   MariaDB   https   laravel com docs 5 5 migrations  BUT WELL THAT s not what this answer is about  the thing is even with doing the above you will like get another error  that s when you like launch php artisan migrate command and because of the problem of the length  the operation like stuck in the middle  solution is bellow  and the user table is like created without the rest or not totally correctly  we need to roll back  the default roll back will not do  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 bellow   L  todos gt  php artisan tinker  Psy Shell v0 8 15  PHP 7 1 10     cli  by Justin Hileman   gt  gt  gt  Schema  drop  users      gt  null   I myself had a problem with users table    after that your good to go  php artisan migrate rollback  php artisan migrate

User · Answer

Give a smaller length for your email string like     table- gt string  email  128 - gt unique      In create user table   And    table- gt string  email  128 - gt index       create password resets table   This will definitely work

User · Answer

In file config database php where     charset    gt   utf8mb4    collation    gt   utf8mb4 unicode ci     Change this line to this     charset    gt   utf8    collation    gt   utf8 unicode ci

User · Answer

If someone having this problem even after doing  above mentioned changes  For an example  in my case I did below changes    namespace App Providers  use Illuminate Support ServiceProvider  use Illuminate Support Facades Schema   class AppServiceProvider extends ServiceProvider              Register any application services             return void          public function register                          public function boot             Schema  defaultStringLength 191            But it wouldn t work right away for two reasons  One is if you are using lumen instead of laravel  you may have to uncomment this line in your app php file first     app- gt register App Providers AppServiceProvider  class     And then you have to create the migration script again with the artisan command    php artisan make migration  lt your table name gt    Since now only the changes you made to ServiceProvider will work

User · Answer

Just installed MariaDB 10 2 4 RC  fired up new blank Laravel 5 4 project and default migration  varchar 255  columns  works   No need to change DB conf and Laravael config database php  Thus just as  scorer noted about default behaviour for 10 2 2

User · Answer

In some cases it can help if you agree to unique only the first 191  hars  public function up         DB  statement  ALTER TABLE  YOUR TABLE NAME     ADD UNIQUE INDEX  NAME FOR INDEX    YOUR COLUMN NAME   191

User · Answer

SOLUTION   First change the defaultStringLength to 191  in the app Providers AppServiceProvider php   public function boot         Schema  defaultStringLength 191       Then  change the charset and collation values as follows  in config database php    charset    gt   utf8    collation    gt   utf8 unicode ci      link to know about MariaDB charset

User · Answer

Please open user and password rest files  files are located in database migrations folder   add line   Schema  defaultStringLength 191   in up function in both files    Then run   php artisan migrate

User · Answer

You can go to app Providers AppServiceProvider php and import this use Illuminate Support Facades Schema  use Illuminate Support ServiceProvider   also in boot function add this Schema  defaultStringLength 191

User · Answer

Going to your config database php and change the charset and collation from utf8mb4 to utf8    charset    gt   utf8    collation    gt   utf8 unicode ci     My problem solved using this method  good luck dude

User · Answer

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   use Illuminate Support Facades Schema          Bootstrap any application services         return void     public function boot         Schema  defaultStringLength 191       You can check out of  https   laravel-news com laravel-5-4-key-too-long-error https   laravel com docs 5 5 migrations indexes

User · Answer

include this line at top  use Illuminate Support Facades Schema    then inside boot function add this   public function boot         Schema  defaultStringLenght 191

User · Answer

For laravel 5 6  This solution solve my problem  go to config database php  Find the code below   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   utf8mb4        collation    gt   utf8mb4 unicode ci        prefix    gt           strict    gt  true       engine    gt  null       Change this two field    charset    gt   utf8mb4    collation    gt   utf8mb4 unicode ci    With This    charset    gt   utf8    collation    gt   utf8 unicode ci

User · Answer

If you re on or updated to Laravel 5 4 This worked for me   Just 1 change  in AppServiceProvider php  use Illuminate Support Facades Schema   public function boot       Schema  defaultStringLength 191       As mentioned in the migrate guide https   laravel com docs master migrations creating-indexes

User · Answer

This worked for me     x000D   x000D    table- gt charset    utf8   x000D    table- gt collation    utf8 unicode ci   x000D   x000D   x000D

User · Answer

i had same problem and i am using a wamp   Solution    Open file   config database php   engine    gt  null    gt   engine    gt   InnoDB     Thanks

User · Answer

If you have tried every other answer and they have not worked  you can drop all tables from the database and then execute the migrate command all at once using this command   php artisan migrate fresh

User · Answer

The same error pops up because of the way Laravel unique   function works  The only thing you need to do is specify a shorter index name eg   table- gt unique   email    users email uniq    my custom uniq

User · Answer

Update 1  As of Laravel 5 4 those changes are no more needed      Laravel 5 4 uses the utf8mb4 character set by default  which includes support for storing  emojis  in the database  If you are upgrading your application from Laravel 5 3  you are not required to switch to this character set    Update 2  Current production MariaDB versions DO NOT support this setting by default globally  It is implemented in MariaDB 10 2 2  by default   Solution  And if you intentionally want to use the correct future-default  starting from Laravel 5 4  UTF8 multi-byte utf8mb4 support for  then start to fix  your database configuration   In Laravel config database php define    charset    gt   utf8mb4    collation    gt   utf8mb4 unicode ci    engine    gt   InnoDB ROW FORMAT DYNAMIC     DYNAMIC allows to store long key indexes   Server settings  by default included in MySQL 5 7 7    MariaDB 10 2 2      mysqld    default character set and collation collation-server   utf8mb4 unicode ci character-set-server   utf8mb4    utf8mb4 long key index innodb large prefix   1 innodb file format   barracuda innodb file format max   barracuda innodb file per table   1   For clients    mysql  default-character-set utf8mb4   And then STOP your MySQL MariaDB server  After that START  Hot RESTART may not work   sudo systemctl stop mysqld sudo systemctl start mysqld   Now you have Laravel 5 x with UTF8 support

User · Answer

It s because Laravel 5 4 uses utf8mb4 which supports storing emojis   Add this in your app Providers AppServiceProvider php  use Illuminate Support Facades Schema   public function boot         Schema  defaultStringLength 191       and you should be good to go

User · Answer

For laravel 5 4  simply edit file      App Providers AppServiceProvider php   use Illuminate Support Facades Schema   public function boot         Schema  defaultStringLength 191

User · Answer

Remove mb4 from charset and collation from config database php  then it will execute successfully   charset      utf8    collation      utf8 unicode ci

User · Answer

I had a problem  change the configuration of the  config   database    file  charset    gt   utf8    collation    gt   utf8 unicode ci     keeping the same pattern in the database   I then gave the command  php artisan migrate

User · Answer

Keep in mind that those articles goes with the easy and wrong solution  It s like chopping of part of your foot because your socks doesn t fit   The correct answer  to buy a new sock  is a bit more complex since it requires you to go to the store again  In this case it requires you to configure your mysql instance  and that isn t as copy pastable as a wrong answer  so we keep propagating the wrong answer  We want to seem copy pastable and easy to use  right   You forgot to specify which Laravel or mysql version you are using  The problem occurs because you are using an older mysql  or newer with wrong settings    Set innodb file format Barracuda Set innodb large prefix 1 Set innodb default row format dynamic   If you do not have the innodb default row format setting  introduced in 5 7 9   then you have to change your config database php to use  engine      innodb row format dynamic    The engine setting was introduced in Laravel 5 2 14  so I hope you have something newer than that   https   github com laravel framework issues 22660 issuecomment-355725923

User · Answer

You will not have this problem if you re using MySQL 5 7 7  or MariaDB 10 2 2    To update MariaDB on your Mac using Brew first unlink the current one  brew unlink mariadb and then install a dev one using brew install mariadb --devel   After installation is done stop start the service running  brew services stop mariadb brew services start mariadb  Current dev version is 10 2 3  After the installation is finished you won t have to worry about this anymore and you can use utf8mb4  that is now a default in Laravel 5 4  without switching back to utf8 nor editing AppServiceProvider as proposed in the Laravel documentation   https   laravel com docs master releases laravel-5 4  scroll down to  Migration Default String Length

User · Answer

If anyone else stumbles upon this answer like I did but for a different reason  you may check your Laravel DB charset collation   I was installing an application  Snipe-IT  and had configured the Laravel database config to use the following    charset    gt   utf8mb4    collation    gt   utf8mb4 general ci     Removing mb4 from both strings fixed the issue  although I believe Antonio s answer is the real solution to the problem

User · Answer

If you re on or updated to Laravel 5 4 and latest version it works  Just 1 change in AppServiceProvider php  use Illuminate Support Facades Schema   public function boot       Schema  defaultStringLength 191

User · Answer

In 24 october 2016 Taylor Otwell the Author of Laravel  announced on is Twitter      utf8mb4  will be the default MySQL character set in Laravel 5 4 for better emoji support   Taylor Otwell Twitter Post   which before version 5 4 the character set was utf8  During this century many web app  include chat or some kind platform to allow their users to converses  and many people like to use emoji or smiley  and this are some kind of super characters that require more spaces to be store and that is only possible using utf8mb4 as the charset  That is the reason why they migrate to utf8mb4 just for space purpose   if you look up in the Illuminate Database Schema Builder class you will see that the  defaultStringLength is set to 255  and to modify that you can procede through the Schema Facade and call the defaultStringLength method and pass the new length   to perform that change call that method within your AppServiceProvider class which is under the app providers subdirectory like this  class AppServiceProvider extends ServiceProvider                  Bootstrap any application services                 return void             public function boot                    all other code              Schema  defaultStringLength 191                 and all other code goes here     I will suggest to use 191 as the value just because MySQL support 767 bytes  and because 767   4 which is the number of byte take by each multibyte character you will get 191   You can learn more here  The utf8mb4 Character Set  4-Byte UTF-8 Unicode Encoding      Limits on Table Column Count and Row Size

User · Answer

For Laravel    5 6 users  Open AppServiceProvider php file  Use the following class    use Illuminate Support Facades Schema    Then inside boot method add the following line  public function boot         Schema  defaultStringLength 191

User · Answer

Change charset to from  utf8mb4  to  utf8  and  collation to  utf8mb4 unicode ci  to  utf8 unicode ci   in config database php file  It worked for me

User · Answer

Specify a smaller length for your e-mail    table- gt string  email   100     100 WORKS

User · Answer

Set database engine InnoDB     Schema  create  users   function  Blueprint  table                 table- gt engine    InnoDB                table- gt increments  id                 table- gt string  name                 table- gt string  email  - gt unique                 table- gt string  password                 table- gt rememberToken                 table- gt timestamps

User · Answer

If you are seeing this error then definitely you need to make following changes on  AppServiceProvider php file -  which can be found inside app- gt providers   changes to be made  use Illuminate Support Facades Schema    see if you have this class if not add it   public function boot         Schema  defaultStringLength 191     make sure you add this line     this should solve your problem for more you can see the Laravel news regarding this issue   https   laravel-news com laravel-5-4-key-too-long-error

User · Answer

Specify a smaller length for your e-mail    table- gt string  email   250     Which is the default  actually    table- gt string  email      And you should be good   For Laravel 5 4 you can find a solution in this Laravel 5 4  Specified key was too long error  Laravel News post   As outlined in the Migrations guide to fix this all you have to do is edit your AppServiceProvider php file and inside the boot method set a default string length   use Illuminate Database Schema Builder    public function boot         Builder  defaultStringLength 191

User · Answer

File  config database php change the following FROM - gt   charset    gt   utf8mb4    collation    gt   utf8mb4 unicode ci    TO - gt   charset    gt   utf8    collation    gt   utf8 unicode ci

User · Answer

I have faced the same issue  and fixed it by adding the below two lines in my app database php   charset    gt   utf8    collation    gt   utf8 unicode ci     My file looks like below     lt  php  return                --------------------------------------------------------------------------       Default Database Connection Name      --------------------------------------------------------------------------             Here you may specify which of the database connections below you wish       to use as your default connection for all database work  Of course       you may use many connections at once using the Database library                     default    gt  env  DB CONNECTION    mysql          charset    gt   utf8        collation    gt   utf8 unicode ci

[php] Laravel migration: unique key is too long, even if specified

Examples related to php

Examples related to mysql

Examples related to laravel