Level 9
I have the same error on OSX with Valet and MariaDB.
1 like
Jan 24, 2017
60
Level 13
Syntax error or access violation: 1071 Specified key was too long
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
usersadd uniqueusers_email_unique(
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.
Level 13
@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
27 likes
Level 10
Schema::defaultStringLength(191); works well
3 likes
Level 1
@imran Worked for me too. Let me share, I've been trying to do this for 3 days with what looked like an older version to Laravel. I updated Laravel to the latest and all of my problems but this one went away. Well, now that problem is gone too. Thanks so much guys!
Level 1
I do have this issue now:
ErrorException Undefined variable: activePage (View: C:\wamp64\www\MyWebApp\resources\views\users\index.blade.php)
Level 1
Class App\Providers\Schema not found
After adding that line I get that error
Level 13
@tahertechs You need to import the Schema namespace at the top.
use Illuminate\Support\Facades\Schema; // At the top of your file
13 likes
Level 7
Thank you Demers94. Your solution works.
Level 2
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.
Level 53
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);
}
19 likes
Level 1
I placed the same, but while installing spatie/ roles and permissions package and doing migrate , it gives error :
Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table permissions add unique permissions_name_guard_name_unique(name, guard_name))
2 likes
Level 1
@msyadav In your database.php replace the following with this: 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'engine' => env('DB_ENGINE', 'InnoDB'),
then php artisan optimize, try to migrate again :>
Level 2
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.
42 likes
Level 1
@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."
Level 8
Great! It work's fine. Thanks for the help!
Level 30
@Demers94 Thank you! Worked for me :)
Level 2
@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.
2 likes
Level 1
@ejdelmonico thanks, your solution worked...
Level 53
@proelitesunny I am glad it helped. Please mark my answer correct so that others can find the solution. Thank you.
Level 1
Woah! Thank you!
Level 122
I found easiest to set in migration;
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();
});
5 likes
Level 5
Can someone please explain why this is happening ?
I can't even use factory's or seeding properly anymore... I try to seed my CRM with companies and if I set my seeder to say "5", all is fine... if I try 50... I get that error... AND I am using
Schema::defaultStringLength(191);
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long;
Any real fix?
Level 67
@marcgaumont You can try changing the database config back to using utf8 instead of utf8mb4, which was the change L5.4 made. You'd have to change the database schema and any tables you've made to use utf8 as well.
1 like
Level 67
Also make sure your table names aren't too long. For relationship tables, it can create some very large index names using a combination of the 2 table names and also adding on some other stuff like "_index". I don't recall exactly off the top of my head what it adds. You might have to explicitly name the indexes when creating indexes and FKs for longer table name combinations instead of letting laravel do it automatically. Hard to explain but I've had this issue a few times.
Level 19
Just in case anyone is wondering, as Cronix mentioned Laravel 5.4 is in fact using utf8mb4. If you're running MySQL prior to 5.7.7 or MariaDB prior to 10.2.2 you will get this error. If you want to use utf8mb4 then just upgrade your MySQL, MariaDB, etc. to the most recent or development versions.
Level 13
@Nicholas I saw that mentionned in the Github thread, but I'm running into this issue even with 5.7.14 and utf8mb4. I never managed to get it to work on any of my computers.
Adding the Schema line in the AppServiceProvider or changing the engine option in config/database.php works, but it's a bit annoying to have to remember to do that every new project.
Level 1
@Demers94 Thanks it worked for me too
Level 1
It worked well!
Level 17
Updating and restarting MariaDB (homebrew) worked for me without any of the fixes.
Level 1
@k32y You're answer works perfectly. Thanks!
Level 1
@k32y Thanks! Works perfectly
Level 122
There is a good explanation of why this happens on Servers for Hackers - which I was surprised to see is now over 2 years old...
https://serversforhackers.com/mysql-utf8-and-indexing
It details strategies for dealing with this issue
Thanks @fideloper
1 like
Level 122
@stephy do you want to retype your rant please, we don't tolerate shouting and swearing here...
There are plenty of options listed in this post to get around the fact that you are using an old version of mysql
Level 1
@k32y Your answered worked for me. Thanks!
Please or to participate in this conversation.