[5.4] SQL error when migrating tables

[Demers94]

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

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]

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.

[i-bajrai]

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

[devcircus]

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]

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]

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]

@devcircus : why using 191 ?

[ChristopherDosin]

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]

@devcircus Thank you!!!

[kirgy]

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]

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

[adenijiayocharles]

Thanks @devcircus your solution solved my problem.

[pedzed]

@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 :+1: emoji like the others did.

[snapey]

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]

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]

@snapey I like Snapey's solution

[Jameron]

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

[zanjs]

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]

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]

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]

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]

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

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

[mario125]

Schema::defaultStringLength(191);

[kjdion84]

Adding the Schema default string length DOES NOT WORK. Changing the charset version DOES NOT WORK. Using a brand new install of WampServer.

[kjdion84]

This is the only solution that worked: https://github.com/the-control-group/voyager/issues/901#issuecomment-291470960

[kjdion84]

Set your mysql engine to InnoDB ROW_FORMAT=DYNAMIC.

[ayushpratap]

I was also facing the same problem. This is for using wamp and using phpmyadmin to access mysql.

1. Go to config>database.php
2. Change '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' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ],``

with

'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_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null, ],

I hope this helps.

[BytewaveMLP]

@ayushpratap Switching to the utf8 charset and collation removes proper UTF8 support (yes, this sounds backwards as all hell). utf8mb4 is the only charset that proply implements the entire set of UTF8 characters, which is why this is an issue.

[rc1021]

@temmiecool

Why utf8mb4 in MySQL? The character utf8 stores 3 bytes per character and contains only BMP (Basic Multilingual Plane) characters. As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of 4 bytes per character supports supplemental character. http://logout.logdown.com/posts/199047-why-utf8mb4-in-mysql

@JGrunder , @karllo There are two solutions:

1. [recommend] add \Schema::defaultStringLength(191); into function boot of the AppServiceProvider.php file.

2. modify charset, collation columns in config\database.php file , look like:

[ghost]

Hi, I still have same error reported by Demers94 a few month ago with a fresh Laravel install ! I have to manually update files as explain in devcircus' message. How is it possible 5 months later ?

[karllo]

@Benyaminrmb have you solved this error ?

[karllo]

@Easter1021 it didn't work

Laravel Version: 5.4.0 PHP Version: 7.0.10 MySQL 5.7.18

php artisan voyager:install --with-dummy and then error here

[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 translations add unique trans lations_table_name_column_name_foreign_key_locale_unique(table_name, column_name, foreign_key, locale))

[rc1021]

@karllo I did work

Laravel Version: 5.4.23 PHP Version: 7.1.3 MySQL: 10.1.22-MariaDB-1~jessie

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

$ php artisan voyager:install --with-dummy

[rc1021]

@karllo may be, you send me your project to me (mufasa.hsu@gmail.com) or share git repo. I really intended to help you with the problem.

[kjdion84]

Just use InnoDB for the engine .

[karllo]

@Easter1021 git is here https://github.com/karllo/laravel_voyager

still have error and help me out

[julianstark999]

When I came from 5.3 to 5.4 I got an similar Error. I figured out, that the standard charsetand collationmysql config in database.php changed. So I used the old 5.3 and it works for me. So you can try this: database.php => connections => mysql

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

[karllo]

@xDestinatioNx
i have tried many ways but didn't work

[rc1021]

@karllo I have been fork your project, and it did work... what's your error message? please give me screencast.

[karllo]

I have fixed this error.my god... @Easter1021 whatever thank you very much ^.^

open database.php and then

'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' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => 'INNODB', ],

[josealgardataborges]

Edit you mysql.ini file and change default-storage-engine=MYISAM to default-storage-engine=InnoDB if you dont want to change Laravel files, don't forget to restart the mysql server afterwards.

If you preffer to change your laravel files, then open /config/database.php and in the "MYSQL Connection" replace 'engine' => null, with 'engine' => 'InnoDB',

so that it becomes like this:

'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' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => "InnoDB",
        ],

NOTE: nevertheless, its my belief that Laravel should have a validation of the MySQL default engine, and adjust accordingly the key size, but i'm not 100% sure that this would not affect other areas of Laravel's framework.

[prasadmadv]

Hi guys,

The 100% fix is here:

Step 1: Include Facades/Schema in AppServiceProvider.php file use Illuminate\Support\Facades\Schema;

Step 2: Edit your AppServiceProvider.php file and inside the boot method set a default string length-

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

Step 3: Drop users, password_resets, migrations tables if already created.

Step 4: Run the php artisan migrate command. Done!

Thank you so much :)

[sicaps]

Read the documentation!

https://laravel.com/docs/master/migrations, and scroll down to the section on: Index Lengths & MySQL / MariaDB (towards the end of the page)

[ayushpratap]

@prasadmadv : Yes this works, I am using it for a while now. I wonder why it is not included in the framework itself and we have to change it manually.

[snapey]

Surprised that noone has mentioned this article https://serversforhackers.com/mysql-utf8-and-indexing. Its two years old but describes the reason for the issue.

Two takeaways from this.

1. Your hands might be tied for a solution if you have data to preserve.
2. You only need to worry about indexed or unique columns so in the standard auth migrations, the 191 length only needs to be applied to the email column.

   $table->string('email',191)->unique();

[shyandsy]

@devcircus i just wanna know why the default string length must be 191? i tried 192 to 255, all these value doesnt work

[snapey]

@shyandsy read the article on servers for hackers. It explains it well. 191 is the largest string that can be used in an index when that string is in utf8mb4 format. This applies to fields that are explicitly indexed or have unique attribute.

[brickgale]

@devcircus thanks +1

[BeRoots]

@beroots to devcircus : why using Schema::defaultStringLength(191); ? Nobody have response about this ?