Framework: [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))

[marcosjosemenezes]

• Laravel Version: 5.6.26
• PHP Version: 7.1.16
• Database Driver & Version: MySQL 5.7.21

Description:

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

Steps To Reproduce:

a. Cria-se um novo projeto: laravel new [project]
b. cd na pasta do [project] criada
c. Altera as informações do arquivo ".env" banco de dados
d. Executa o "php artisan migrate"

Qual a causa:

O limite de comprimento da chave UNIQUE KEY do MySQL é de 1000 bytes e o sistema esta tentando gerar uma chave maior.

Correção

a. Edite o arquivo "vendor \ laravel \ framework \ src \ Illuminate \ Database \ Schema \ Builder.php".
b. Faça a seguinte alteração
[código original] public static $defaultStringLength = 255;
[código alterado] public static $defaultStringLength = 250;
c. Remova as tabelas que foram criadas no banco de dados
-- migrations
-- users
d. Execute novamente o "php artisan migrate"
e. Pronto resolvido

[decadence]

First link in Google

[wellingguzman]

A temporary solution would be going to your config/database.php and change the charset and collation from utf8mb4 to utf8

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

This happens because utf8mb4 uses 4 bytes per character, and the email column has a 255 character length which is greater than the limit 767 bytes. 255 x 4bytes = 1020b.

To fix this the email column length should be at most 191. 191 x 4 bytes = 764b.

[wellingguzman]

Something to notice is that MySQL at least, allow to define the index length.

Ref:

• https://dev.mysql.com/doc/refman/8.0/en/create-index.html
• https://dev.mysql.com/doc/refman/8.0/en/create-table.html

[sisve]

@WellingGuzman How is that solution temporary? Can it be easily reverted and start working with utf8mb4 later on?

[wellingguzman]

@sisve You can change it later on if you want to, this may not be the ideal solution if you want to use utf8mb4 charset.

There's three way to solve this:

Changing the migration length to 191

Changing the length to 191 or less.

$table->string('name', 191);

Change the string default length

Changing the default (global) string type length to 191. This can be done using the schema facade.

\Illuminate\Support\Facades\Schema::defaultStringLength(191);

You can add this line to your AppServiceProvider.php boot() method located in app/Providers/ directory.

public function boot()
{
    \Illuminate\Support\Facades\Schema::defaultStringLength(191);
}

Use utf8 instead utf8mb4

This may not be the ideal one, but if you don't care about utf8mb4 you can go to your config/database.php file and replace charset and collation to utf8

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

to

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

[sisve]

No, he cannot change it later. His database server will not allow it next week; the exact same problem will still be there. And he'll have to write migrations to fix all the things you just told him to do.

A fourth alternative is to enable innodb_large_prefix in his database. That's enabled per default in newer versions of mysql. Properly configuring the database server means he does not have to change his application code, and he can deploy the code to both an old or a new mysql versions, without changing any code.

[marcosjosemenezes]

As explicações agregam a resposta, obrigado a todos.

[derkinderfietsen]

You're welcome..

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

[tback]

Still appears on clean laravel project:

• Laravel 5.7.9
• PHP 7.2.10-1+0\~20181001133118.7+stretch\~1.gbpb6e829
• Mariadb 10.1.26-MariaDB-0+deb9u1

Project created with composer create-project --prefer-dist laravel/laravel auth.
All I did was change DB_CONNECTION in .env to mysql.
Then I called artisan migrate

This behavior is frustrating and confusing for new users. Please reopen the issue.

[sisve]

Configure your database to enable innodb_large_prefix. It may require you to start using the new innodb_file_format Barracuda (that mysql is using as default now).

[jeni1616]

A temporary solution would be going to your config/database.php and change the charset and collation from utf8mb4 to utf8

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

This happens because utf8mb4 uses 4 bytes per character, and the email column has a 255 character length which is greater than the limit 767 bytes. 255 x 4bytes = 1020b.

To fix this the email column length should be at most 191. 191 x 4 bytes = 764b.

Thanks, this worked !

[oubihis]

Go in the App\Providers\AppServiceProvider class and change the boot method to look like this

use Illuminate\Support\Facades\Schema;

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

[khanks]

It seems obvious that newer versions of MySQL/MariaDB should not have this 'key length' error. But it also seems obvious to me that the solutions presented here have some issues of their own. Both Oracle and Percona have vacillated on turning innodb_large_prefix "ON" or "OFF" by default and both of them have it listed as 'deprecated' (Mysql ->https://dev.mysql.com/worklog/task/?id=7703, last paragraph says they will remove it. Percona docs have similar comments).
As for changing the AppServiceProvider 'default' string length, how does that help a situation where a developer specifically defines a varchar of 255? (side note: as a DB guy, I always wonder why anyone would define an index on a varchar).
Changing the collation seems, IMHO, to be the least fraught with weirdness (as long as you haven't already stored data using utf8mb4 that actually required utf8mb4). Someone commented that "no, you cannot change it later". Well man, I've changed several of my databases back and forth from UTF-8 to UTF8MB4 a number of times and never had a problem. But I'm serving English-only users and they don't use weird things like emoticons in a business website.
I realize I've not offered a great 'solution' for people (like me) who are not on the latest version of mysql/mariadb. I just thought these points needed to be brought up, for those who don't already have enough to think about. Smile (no emoji needed to convey that).

[sisve]

Hi @khanks ,

It sounds like you're against modifying the innodb_large_prefix because Oracle/Percona have discussions about it. Just to clarify, the linked discussion argues for removing the setting entirely, and forcing it to the setting ON, which is my suggested solution for this problem. The last two paragraphs is interesting to read.

The parameter innodb_large_prefix was introduced in MySQL 5.5, so that users could avoid accidentally creating tables that would be incompatible with the InnoDB Plugin in MySQL 5.1. Now that MySQL 5.1 is not supported any more, it makes no sense to use any other value than innodb_large_prefix=ON.

Basically, the only purpose of innodb_file_format and innodb_large_prefix is to cripple the capabilities of InnoDB. This is the reason to change the default values to ‘uncrippled’ and to deprecate and remove the parameters for future removal, so that InnoDB cannot be accidentally crippled.

It was I that said that the collation couldn't be changed later. The context is important, the poster cannot change the collation later because his database settings would still not allow it. If the correct settings where set, then the utf8mb4 collation can be used with the field sizes in question. My argument here was that it made more sense to modify those settings a year ago, when the question was asked, instead of waiting. They need to be changed someday anyhow.

[aliadhillon]

If you are looking for a permanent solution then just change the default storage engine in Mysql _ini_ configuration file to InnoDB which is the default storage engine in Mysql v5.5 and later.
default-storage-engine=InnoDB
In many cases like mine people complain that they have the latest version of Mysql but still having this error but the problem is sometime latest versions use old configuration files sitting in servers which uses MYISAM as default engine which create above problem.

[khanks]

On 11/8/2019 2:13 PM, Ali A. Dhillon wrote:
>

If you are looking for a permanent solution then just change the
default storage engine in Mysql ini configuration file to InnoDB
which is the default storage engine in Mysql v5.5 and later. In many
cases like mine people complain that they have the latest version of
Mysql but still having this error but the problem is sometime latest
versions use old configuration files stored in servers.
|default-storage-engine=InnoDB|

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/laravel/framework/issues/24711?email_source=notifications&email_token=AAG67TKAO773FLB4N42PLYDQSXB6JA5CNFSM4FHYIT62YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDTHH2I#issuecomment-551973865,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAG67TNQHW7A67IV4BMFKPTQSXB6JANCNFSM4FHYIT6Q.

Thanks, that's a great point. I don't have that command specifically set
in my "my.ini", but I verified the default storage engine is InnoDb by
doing a "SHOW ENGINES" query. Good point though, and definitely worth
looking at.

[aliadhillon]

@khanks I am using WAMP and in my case it had the old configuration my.ini which has the
default-storage-engine=MYISAM but in mysql v5.5 or latere InnoDB is default engine so if you just omit the above line (if there is any) it will use the default engine which is InnoDB.
So you don't have to explicitly set the default-storage-engine=InnoDB if you are using mysql v5.5+. That's why you don't have this in you my.ini but still your storage engine is InnoDB.

If you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can specify the default engine by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.Ref

[farridkun]

A temporary solution would be going to your config/database.php and change the charset and collation from utf8mb4 to utf8

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

This happens because utf8mb4 uses 4 bytes per character, and the email column has a 255 character length which is greater than the limit 767 bytes. 255 x 4bytes = 1020b.

To fix this the email column length should be at most 191. 191 x 4 bytes = 764b.

This is Work for me, Thanks Dude!

[prabaharanh]

A temporary solution would be going to your config/database.php and change the charset and collation from utf8mb4 to utf8

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

This happens because utf8mb4 uses 4 bytes per character, and the email column has a 255 character length which is greater than the limit 767 bytes. 255 x 4bytes = 1020b.

To fix this the email column length should be at most 191. 191 x 4 bytes = 764b.

Success!!!!

[israelkingz]

A temporary solution would be going to your config/database.php and change the charset and collation from utf8mb4 to utf8

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

This happens because utf8mb4 uses 4 bytes per character, and the email column has a 255 character length which is greater than the limit 767 bytes. 255 x 4bytes = 1020b.
To fix this the email column length should be at most 191. 191 x 4 bytes = 764b.

Thanks, this worked !

Thanks, It worked

[polares552]

@khanks I am using WAMP and in my case it had the old configuration my.ini which has the
default-storage-engine=MYISAM but in mysql v5.5 or latere InnoDB is default engine so if you just omit the above line (if there is any) it will use the default engine which is InnoDB.
So you don't have to explicitly set the default-storage-engine=InnoDB if you are using mysql v5.5+. That's why you don't have this in you my.ini but still your storage engine is InnoDB.

If you omit the ENGINE option, the default storage engine is used. The default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can specify the default engine by using the --default-storage-engine server startup option, or by setting the default-storage-engine option in the my.cnf configuration file.Ref

Thanks, It worked.

[khanks]

Thanks. I figured out a solution long ago. And when my host finally updates
the version of MySQL that they provide, it will be a non-issue.
Also, there are about 50 duplicates of this same answer and every time
somebody posts one of those duplicates, I get copied on it -- and probably
many others do as well. So maybe we shouldn't pursue this thread anymore.
The real question for me, was why in the hell anybody would define an email
field as big as Laravel defined that default. it's ridiculous.

On Fri, Jun 26, 2020, 8:59 PM Caio Felipe [e-mail] wrote:

@khanks https://github.com/khanks I am using WAMP and in my case it had
the old configuration my.ini which has the
default-storage-engine=MYISAM but in mysql v5.5 or latere InnoDB is
default engine so if you just omit the above line (if there is any) it will
use the default engine which is InnoDB.
So you don't have to explicitly set the default-storage-engine=InnoDB if
you are using mysql v5.5+. That's why you don't have this in you my.ini but
still your storage engine is InnoDB.

If you omit the ENGINE option, the default storage engine is used. The
default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can
specify the default engine by using the --default-storage-engine server
startup option, or by setting the default-storage-engine option in the
my.cnf configuration file.Ref
https://dev.mysql.com/doc/refman/5.5/en/storage-engine-setting.html

Thanks, It worked.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/laravel/framework/issues/24711#issuecomment-650472858,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAG67TNBRXADWHOZL6GANCTRYVHAXANCNFSM4FHYIT6Q
.

[khanks]

In addition, my default storage engine has always been Innodb -- I don't
even use Myisam.

On Fri, Jun 26, 2020, 9:27 PM Smiddick Hanks [e-mail] wrote:

Thanks. I figured out a solution long ago. And when my host finally
updates the version of MySQL that they provide, it will be a non-issue.
Also, there are about 50 duplicates of this same answer and every time
somebody posts one of those duplicates, I get copied on it -- and probably
many others do as well. So maybe we shouldn't pursue this thread anymore.
The real question for me, was why in the hell anybody would define an email
field as big as Laravel defined that default. it's ridiculous.

On Fri, Jun 26, 2020, 8:59 PM Caio Felipe [e-mail]
wrote:

@khanks https://github.com/khanks I am using WAMP and in my case it
had the old configuration my.ini which has the
default-storage-engine=MYISAM but in mysql v5.5 or latere InnoDB is
default engine so if you just omit the above line (if there is any) it will
use the default engine which is InnoDB.
So you don't have to explicitly set the default-storage-engine=InnoDB if
you are using mysql v5.5+. That's why you don't have this in you my.ini but
still your storage engine is InnoDB.

If you omit the ENGINE option, the default storage engine is used. The
default engine is InnoDB as of MySQL 5.5.5 (MyISAM before 5.5.5). You can
specify the default engine by using the --default-storage-engine server
startup option, or by setting the default-storage-engine option in the
my.cnf configuration file.Ref
https://dev.mysql.com/doc/refman/5.5/en/storage-engine-setting.html

Thanks, It worked.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/laravel/framework/issues/24711#issuecomment-650472858,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAG67TNBRXADWHOZL6GANCTRYVHAXANCNFSM4FHYIT6Q
.

[skino2019]

A temporary solution would be going to your config/database.php and change the charset and collation from utf8mb4 to utf8

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

This happens because utf8mb4 uses 4 bytes per character, and the email column has a 255 character length which is greater than the limit 767 bytes. 255 x 4bytes = 1020b.

To fix this the email column length should be at most 191. 191 x 4 bytes = 764b.

This fixed it for me. when i created the DB it set to utf8mb and collation utf8mb_general_ci... changed the formatting and worked out the box.

[anasroud]

A temporary solution would be going to your config/database.php and change the charset and collation from utf8mb4 to utf8

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

This happens because utf8mb4 uses 4 bytes per character, and the email column has a 255 character length which is greater than the limit 767 bytes. 255 x 4bytes = 1020b.

To fix this the email column length should be at most 191. 191 x 4 bytes = 764b.

This fixed it for me. thank you so much.