Syntax error or access violation: 1071 Specified key was too long

Published 10 months ago by Demers94

Hi,

I've just upgraded to Laravel 5.4 and created my first project with it. When I first try to migrate the database, I get this error :

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table users add unique users_email_unique(email))

I can fix this by going in the migration for the users table, and manually specifying the max-length of the email field, like so :

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

If I run php artisan migrate again, it will work for the users table, but will fail for the password reset table. I have to do the same thing for the email field in that migration.

Has anyone else encountered this issue? It was working fine before with 5.3.

I'm running Windows 10 and using HeidiSQL to create my database, like I've always used before.

jtubbs

I have the same error on OSX with Valet and MariaDB.

Demers94

@jtubbs I've opened an issue in the Github page of the framework, you can follow the progress here : https://github.com/laravel/framework/issues/17508

The solution was to add this line in the boot() method of the AppServiceProvider :

Schema::defaultStringLength(191);

(don't forget to import the namespace)

You can read about this in the migrations' documentation : https://github.com/laravel/docs/blob/5.4/migrations.md#index-lengths--mysql

imran

Schema::defaultStringLength(191); works well

tahertechs

Class App\Providers\Schema not found

After adding that line I get that error

Demers94

@tahertechs You need to import the Schema namespace at the top.

use  Illuminate\Support\Facades\Schema; // At the top of your file
BeyondThePale

Thank you Demers94. Your solution works.

willrobinson

This workaround works but doesn't this mean that you can't have E-Mail addresses of more than 191 characters long? IIRC, the standard allows for E-Mail addresses that are up to 254 characters in length.

ejdelmonico

If you are using MariaDB or an older version of MySQL, you need to place this code in your AppServiceProvider.php:

use Illuminate\Support\Facades\Schema;

public function boot()
{
    Schema::defaultStringLength(191);
}
k32y
k32y
9 months ago (3,470 XP)

This fixed it for me:

Inside config/database.php, replace this line for mysql

'engine' => null',

with

'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

Instead of setting a limit on your string lenght.

elocke2017

@k32y I tried your solution, but then I get a new error in another of my tables, which says you cannot add a foreign key constraint. SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Is there another engine option for MySQL which can solve the UTF8MB4 issue as well as allow FK constraints in the standard way, or can MyISAM handle it with some other small change? I'm running MySQL 5.7.11 locally, but the application will eventually run in the cloud and needs to be robust for any MySQL db.

Thank you for any help.

https://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html "InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. InnoDB does not currently support foreign keys for tables with user-defined partitioning. This means that no user-partitioned InnoDB table may contain foreign key references or columns referenced by foreign keys."

migas
migas
9 months ago (13,330 XP)

Great! It work's fine. Thanks for the help!

christopher

@Demers94 Thank you! Worked for me :)

k32y
k32y
9 months ago (3,470 XP)

@elocke2017 Your problem is not with InnoDB. MyISAM does not support foreign key and therefore won't work. The problem is more likely one of the following:

  • Your tables have different storage engines, try setting the storage engine explicitly with $table->engine('InnoDB');.
  • Your foreign key is being referenced before it's created. You can change the order your migrations are run by changing the filename timestamp.
  • There is datatype mismatch between the keys.
  • etc.
proelitesunny

@ejdelmonico thanks, your solution worked...

ejdelmonico

@proelitesunny I am glad it helped. Please mark my answer correct so that others can find the solution. Thank you.

Please sign in or create an account to participate in this conversation.