Sqlstate 42000 syntax error or access violation 1071 specified key was too long

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 How is that solution temporary? Can it be easily reverted and start working with utf8mb4 later on?

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

IlluminateSupportFacadesSchema::defaultStringLength(191);

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

public function boot()
{
    IlluminateSupportFacadesSchema::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',

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.

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

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.

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).

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 !

Go in the AppProvidersAppServiceProvider class and change the boot method to look like this

use IlluminateSupportFacadesSchema;

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

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).

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.

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.

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
<#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.

@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

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!

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!!!!

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

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

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.

…

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

…

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.

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.

@sisve Puede cambiarlo más adelante si lo desea, esta puede no ser la solución ideal si desea usar utf8mb4charset.

Hay tres formas de resolver esto:

Cambiar la duración de la migración a 191

Cambiando la longitud 191ao menos.

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

Cambiar la longitud predeterminada de la cadena

Cambiar la stringlongitud de tipo predeterminada (global) a 191. Esto se puede hacer usando la fachada del esquema.

IlluminateSupportFacadesSchema::defaultStringLength(191);

Puede agregar esta línea a su AppServiceProvider.php boot()método ubicado en el app/Providers/directorio.

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

Utilizar en su utf8lugarutf8mb4

Esto puede no ser la ideal, pero si no se preocupan por utf8mb4usted puede ir a su config/database.phparchivo y reemplazar charsety collationalutf8

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

a

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

Muchas Gracias, la solución para mi fue cambiar la longitud a 191, pero primero revise que hubiera borrado en la base de datos las migraciones que había podido realizar, y luego ejecutar nuevamente el comando php artisan migrate

simpley login to phpmyadmin of hosting account and select database >> go to «operations» >> change the collections to ‘utf8mb4_unicode_ci’ and restored backup now work for me.

PLEASE ANYONE HELP?

IlluminateDatabaseQueryException

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘hello.core_settings’ doesn’t exist (SQL: select * from core_settings where name = site_locale limit 1)

at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
667| // If an exception occurs when attempting to run a query, we’ll format the error
668| // message to include the bindings with SQL, which will make this exception a
669| // lot more helpful to the developer instead of just the database’s errors.
670| catch (Exception $e) {

671| throw new QueryException(
672| $query, $this->prepareBindings($bindings), $e
673| );
674| }
675|

1 [internal]:0
IlluminateFoundationApplication::IlluminateFoundation{closure}(Object(AppProvidersAppServiceProvider))

14 modules/Core/Models/Settings.php:31
IlluminateDatabaseEloquentBuilder::first()

Good afternoon @Lakshsay, could I put the Model Settings source code?
Apparently the table name is wrong — hello.core_settings.

@polares552 Yes please advice me the steps / changes needs to be done.
where exactly I need to make changes.
im a student so learning

@Lakshsay add your application’s source code here or provide the repository link so we can help you.
You can start by checking your migrations, the migrations are inside the databases folder.
If you haven’t created it, make sure the connection to your database is working, the connection settings can be adjusted in the .env file at the root of the project, then run the command php artisan migrate in your command terminal.
Check your controller where the SQL statement is being assembled via Eloquent or QueryBuilder.
If you prefer you can call me on Skype caiofelipe.up@hotmail.com.

@polares552 best personality I have ever came across. you are so helpful , polite and understandable. listened to all the concerns and resolved it I am really thankful to you.

@polares552 best personality I have ever came across. you are so helpful , polite and understandable. listened to all the concerns and resolved it I am really thankful to you.

Thanks!!!

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!
Works on Heroku

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.

wow thanks