New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[5.4] SQL error when migrating tables #17508

Closed
Demers94 opened this Issue Jan 24, 2017 · 84 comments

Comments

Projects
None yet
@Demers94

Demers94 commented Jan 24, 2017

  • Laravel Version: 5.4.0
  • PHP Version: 7.0.12
  • Database Driver & Version: MySQL 5.6.33

Description:

When I create a new 5.4 project and try to migrate the database tables, I get this SQL 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_unique(email))

I get this error when running php artisan migrate on a fresh install on a Macbook, on my Windows computer I get the same error except it says that the max length is 1000 bytes instead of 767 bytes.

Steps To Reproduce:

  • Create a new project (laravel new project, with installer 1.3.3)
  • cd into the project
  • Fill in the database information in the .env file
  • Run php artisan migrate
@devcircus

This comment has been minimized.

Contributor

devcircus commented Jan 24, 2017

In your appserviceprovider boot method, try adding

Schema::defaultStringLength(191);

Best sure to import Illuminate\Support\Facades\Schema at the top of the service provider.
There's a note about this in the 5.4 documentation. It's possible this is what is causing your problems.

@Demers94

This comment has been minimized.

Demers94 commented Jan 24, 2017

Thank you @devcircus , adding this line fixed my issue.

It should be noted however that the problem occurs on my Windows computer (if I don't add the extra line of code in the boot() method) despite running MySQL 5.7.14 on that computer. The documentation says that there could be an issue with versions before 5.7.7, but I'm having a problem with a newer version as well.

@themsaid themsaid closed this Jan 24, 2017

@i-bajrai

This comment has been minimized.

i-bajrai commented Jan 26, 2017

Why is this closed? The issue is still there? Using homestead I still get this error.

@devcircus

This comment has been minimized.

Contributor

devcircus commented Jan 26, 2017

Check the 5.4 docs in the database / migration section. It's been covered several times in the last few days here, on slack and on the laracasts forum as well.

@fernandobandeira

This comment has been minimized.

Contributor

fernandobandeira commented Jan 26, 2017

If you are on Mysql < 5.7 or MariaDB < 10.2 then enable innodb_large_prefix this will let you have 255 fields on your database you won't need to touch any code.

On these other versions you don't need, it works out of the box.

@pedzed

This comment has been minimized.

pedzed commented Jan 28, 2017

I got this error too with MySQL 5.7.11.

As @devcircus said, the workaround on https://laravel.com/docs/5.4/migrations#indexes works.

UPDATE

According to @rbkkm, to fix this issue, you can decrease the index rather than the field size:

This lets you use the full 255 characters at the "expense" of index length, rather than getting the full index length at the expense of the varchar field length.

What @rbkkm seems to be suggesting is this:

$table->index([DB::raw('email(191)')]);
$table->unique([DB::raw('email(191)')]);

So for example, the migration of password_resets's up() method would look like this:

Schema::create('password_resets', function (Blueprint $table) {
    $table->string('email');
    $table->string('token');
    $table->timestamp('created_at')->nullable();

    $table->unique([DB::raw('email(191)')]);
});

And for the migration of users:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email');
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();

    $table->index([DB::raw('email(191)')]);
});
@BeRoots

This comment has been minimized.

BeRoots commented Jan 30, 2017

@devcircus : why using 191 ?

@kayyyy

This comment has been minimized.

kayyyy commented Feb 4, 2017

Got this Error too with Laravel 5.4.9, Spark 4.0, Valet v2.0.3 & 10.1.16-MariaDB Homebrew.
Adding Schema::defaultStringLength(191); in the AppServiceprovider boot Method worked for me in this Case.

@htxuankhoa

This comment has been minimized.

htxuankhoa commented Feb 6, 2017

@devcircus Thank you!!!

@kirgy

This comment has been minimized.

kirgy commented Feb 10, 2017

Should this not be considered an open problem? I understand that it's a documented problem, but shouldn't we expect some level of backwards compatibility with homestead? My homestead version is almost a year old and a new deploy caused this problem for me. The above solution solved it for me.

@brandonsimpson

This comment has been minimized.

brandonsimpson commented Feb 16, 2017

Just had a fresh install, still getting this problem. Adding the Schema::defaultStringLength(191); that @devcircus was the trick.

@adenijiayocharles

This comment has been minimized.

adenijiayocharles commented Feb 17, 2017

Thanks @devcircus your solution solved my problem.

@pedzed

This comment has been minimized.

pedzed commented Feb 17, 2017

@devcircus Thank you!!!

Thanks @devcircus your solution solved my problem.

Please, guys. Do not post comments if it does not add anything of value to the issue thread. Every subscriber to the issue gets a useless email. Show your thanks by using the 👍 emoji like the others did.

@snapey

This comment has been minimized.

snapey commented Feb 18, 2017

I added to the migration itself

        Schema::defaultStringLength(191);
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->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

@pedzed

This comment has been minimized.

pedzed commented Feb 19, 2017

than have it tucked away in some completely unrelated service provider

Then make it related? Service providers are great for that sort of thing.

If you still don't want to, you could also make a different parent class, although I think that that would need a manual change each time you run artisan make:migration...

@nikocraft

This comment has been minimized.

nikocraft commented Mar 6, 2017

@snapey I like Snapey's solution

@jameron

This comment has been minimized.

jameron commented Mar 13, 2017

This is an issue on fresh installs, Laravel Framework 5.4.15, MacBook Pro, Server version: 5.6.23 MySQL Community Server

@zanjs

This comment has been minimized.

zanjs commented Mar 15, 2017

AppServiceProvider.php It has been modified

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

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

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

But still is error!

λ php artisan voyager:install --with-dummy
Publishing the Voyager assets, database, and config files
Copied Directory [\vendor\tcg\voyager\publishable\assets] To [\public\vendor\tcg\voyager\assets]
Copied Directory [\vendor\tcg\voyager\publishable\database\migrations] To [\database\migrations]
Copied Directory [\vendor\tcg\voyager\publishable\database\seeds] To [\database\seeds]
Copied Directory [\vendor\tcg\voyager\publishable\demo_content] To [\storage\app\public]
Publishing complete.
Publishing complete.
Migrating the database tables into your application
Migration table created successfully.


  [Illuminate\Database\QueryException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `tran
  slations` add unique `translations_table_name_column_name_foreign_key_locale_unique`(`table_name`, `column_name`, `foreign_key`, `locale
  `))



  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes



  [PDOException]
  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes
@mblackritter

This comment has been minimized.

mblackritter commented Mar 24, 2017

In case someone else ends up here first and still banging his head, the whole solution for Maria/MySQL below 10.2/5.7 is explained here:
http://stackoverflow.com/questions/23786359/laravel-migration-unique-key-is-too-long-even-if-specified/39750202#39750202

For me, the one part that did the trick after correctly setting everything else and which I was still missing until I found that post, is:

In Laravel config/database.php define:
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

@geraldarcega

This comment has been minimized.

geraldarcega commented Apr 4, 2017

I've also experience this today, given that I already included Schema::defaultStringLength(191); in my AppServiceProvider.php.

@zanjs have you fixed this?! We have the same issue.

@sicaps

This comment has been minimized.

sicaps commented Apr 4, 2017

Check that your AppServiceProvider.php It has been modified to include what is in bold:

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

@geraldarcega

This comment has been minimized.

geraldarcega commented Apr 5, 2017

@sicaps thanks for your reply. I also have that in my AppServiceProvider.php. Anyway, I manage to fix it by removing the length of the string, before one of my index is like this $table->string('name', 200) I just change it to $table->string('name') and it works.

@temmiecool

This comment has been minimized.

temmiecool commented Apr 5, 2017

solved mine by changing the collation in the database.php file located in the config folder of my project
changed it from 'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

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

apparently the 'utf8mb4' collation takes more characters than the 'utf8' collation

MehulBawadia added a commit to MehulBawadia/ecommerce that referenced this issue Jan 27, 2018

@rbkkm

This comment has been minimized.

rbkkm commented Feb 4, 2018

This is a really BIG issue. Trim the column is ridiculous, u should trim the index.

"Best" Solution: Use Smaller Indexes
This solution fixes the underlying issue and allows us to avoid issues caused by any customer's particular configuration of MySQL.

On VARCHAR fields (this hasn't been necessary on INT fields, and generally only FULLTEXT indexes are created on TEXT fields), we have the option to use smaller indexes; Up to the first 191 characters.

-- Index first 20 characters of the username column
ALTER TABLE users ADD INDEX username_index_name (username(20))
This lets you use the full 255 characters at the "expense" of index length, rather than getting the full index length at the expense of the varchar field length.

Depending on your use case, this solution can provide a speed boost to MySQL, as we're not storing as much information in the indexes (making it faster for MySQL to search the index). It definitely will remove warnings and errors about the index length when using the utf8mb4 character set.

This also makes an application less susceptible to issues to using within different/unknown MySQL environments.

Because this could be managed within our code, not create an issue where customers needed to do MySQL configuration changes, and wouldn't risk truncating "legacy" customer data, this was our most viable solution.

@rbkkm

This comment has been minimized.

rbkkm commented Feb 4, 2018

I can't believe that Laravel deploys 5.5 with such a hassle about indexes on large columns, and only gives wrong solutions. I looked for a good solution for 2 weeks.

@fernandobandeira

This comment has been minimized.

Contributor

fernandobandeira commented Feb 5, 2018

@rbkkm Could you send a PR to the docs adding this solution to the migration docs?

https://github.com/laravel/docs/blob/5.5/migrations.md

I think others could benefit from it too since it seems like the best solution here...

@pedzed

This comment has been minimized.

pedzed commented Feb 12, 2018

@fernandobandeira Here you go: laravel/docs#4075.

LukeTowers added a commit to octobercms/october that referenced this issue Feb 17, 2018

Fix issues with MySQL utf8mb4 support
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.
@jas095

This comment has been minimized.

jas095 commented Feb 19, 2018

Hi everyone, in the version of Laravel 5.6 I've fix the issue changing the .env file in the line

DB_HOST=127.0.0.1
to
DB_HOST=localhost

@jamel2020

This comment has been minimized.

jamel2020 commented Feb 20, 2018

capture
plzz wat is my problem

@pedzed

This comment has been minimized.

pedzed commented Feb 20, 2018

@jamel2020 Just use @rbkkm's solution, as I described here: #17508 (comment).

@jamel2020

This comment has been minimized.

jamel2020 commented Feb 20, 2018

wer i creat it !!! im not good in laravel

@jamel2020

This comment has been minimized.

jamel2020 commented Feb 20, 2018

exemple plzzz

@jamel2020

This comment has been minimized.

jamel2020 commented Feb 20, 2018

thnx its good i find

@jttfbd

This comment has been minimized.

jttfbd commented Feb 28, 2018

thanks to all specially zanjs due to it code help me

@kieuquangloc147

This comment has been minimized.

kieuquangloc147 commented Mar 4, 2018

I made a video to help beginner to fix the problem: https://youtu.be/LzD3Cda7GuY
Here is the link to read more information: https://laravel-news.com/laravel-5-4-key-too-long-error

octoberapp pushed a commit to octoberrain/system that referenced this issue Mar 7, 2018

Fix issues with MySQL utf8mb4 support
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

Fix issues with MySQL utf8mb4 support
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.
@YayaDy

This comment has been minimized.

YayaDy commented May 25, 2018

Please add
use Illuminate\Support\Facades\Schema;
boot(){
Schema::defaultStringLength(191);
}
to your app/provider/AppServiceProvider.php

@kieuquangloc147

This comment has been minimized.

kieuquangloc147 commented May 25, 2018

@JensHH

This comment has been minimized.

JensHH commented Jun 20, 2018

Still have the same problem with a fresh installation. Diffrence is, max key length is 1000 bytes instead of 767 bytes.
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table ltm_translations add unique ixk_ltm_translations_locale_group_key(locale, group, key))

mysql is 5.7.21, Laravel Framework 5.5.35
Content of AppServiceProvider.php is:
`namespace App\Providers;

use Auth;
use Carbon\Carbon;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;

class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
Schema::defaultStringLength(191);
// set correct locale for Carbon
Carbon::setLocale(config('app.locale'));
}

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

}
`

@FaizanNoor

This comment has been minimized.

FaizanNoor commented Jul 3, 2018

File: config/database.php
change the following
FROM ->
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

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

@SantinoVega

This comment has been minimized.

SantinoVega commented Jul 10, 2018

Hi there. I hope can understandme.
I solved to follow this steps. in laravel-project/databases/migrations
edit both files (create_users_table, create_password_reset_table) in function up().
like this:

public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email');
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();

            $table->index([DB::raw('email(191)')]);
        });
    }

Tested on:
Ubuntu 16.04
10.1.13-MariaDB
PHP 7.2.7-1

@Aryan2500

This comment has been minimized.

Aryan2500 commented Jul 15, 2018

In your appserviceprovider boot method, try adding

Schema::defaultStringLength(191);

Dont Forget to copy paste " Illuminate\Support\Facades\Schema; " at the top of the service provider.

@pedzed

This comment has been minimized.

pedzed commented Jul 19, 2018

@themsaid This issue thread does not get any more useful comments. People are just repeating things. I suggest closing and locking it.

P.S. I still find it unreasonable not to mention this more thoroughly in the documentation as I proposed here: laravel/docs#4075.

To summarize, I think the best solution is provided by @rbkkm, which I explained way up (in this thread): #17508 (comment).


According to @rbkkm, to fix this issue you can decrease the index rather than the field size:

This lets you use the full 255 characters at the "expense" of index length, rather than getting the full index length at the expense of the varchar field length.

What @rbkkm seems to be suggesting is this:

$table->index([DB::raw('email(191)')]);
$table->unique([DB::raw('email(191)')]);

So for example, the migration of password_resets's up() method would look like this:

Schema::create('password_resets', function (Blueprint $table) {
    $table->string('email');
    $table->string('token');
    $table->timestamp('created_at')->nullable();

    $table->unique([DB::raw('email(191)')]);
});

And for the migration of users:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email');
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();

    $table->index([DB::raw('email(191)')]);
});
@sancyclopsxx

This comment has been minimized.

sancyclopsxx commented Aug 14, 2018

I think this is the answer .

https://laravel-news.com/laravel-5-4-key-too-long-error

@udhavsarvaiya

This comment has been minimized.

udhavsarvaiya commented Oct 17, 2018

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

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

@M-Adnan-Adi

This comment has been minimized.

M-Adnan-Adi commented Nov 4, 2018

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

Answer:
Dir:
App/Providers/AppServiceProvider.php

Add line in Top:
use Illuminate\Support\Facades\Schema;

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment