mysql key length errors with utf8mb4

[mintbridge]

• Laravel Version: 5.4
• PHP Version: 7.0.14
• Database Driver & Version: 10.1.18-MariaDB

Description:

Setting the default collation to utf8mb4 (see laravel/laravel@9d01389) means that out of the box migrations failed with a mysql key length error:

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 `email`(`email`))

Previously raised in #15725

Steps To Reproduce:

composer create-project laravel/laravel okr dev-develop
config the db connection in .env and add a database with utf8mb4 and utf8mb4_unicode_ci
php artisan migrate

[JayBizzle]

I was also seeing this issue yesterday. L5.4, PHP 7.0.13, MySQL 5.6.34

[spyric]

It was discussed many times. for example here #11451

Shortly what is going wrong:
By default string column uses 255 characters, in utf8mb4 each character cost 4 bytes. 4*255=1020. But maximum key length is 767.

So you can reduce length of column to 191
or try to use MySQL 5.7, it has no errors like that for me

[sisve]

Mysql 5.7.7 introduced changes to the default innodb parameters.

Important Change; InnoDB: The following changes were made to InnoDB configuration parameter default values:

• The innodb_file_format default value was changed to Barracuda. The previous default value was Antelope. This change allows tables to use Compressed or Dynamic row formats.
• The innodb_large_prefix default value was changed to ON. The previous default was OFF. When innodb_file_format is set to Barracuda, innodb_large_prefix=ON allows index key prefixes longer than 767 bytes (up to 3072 bytes) for tables that use a Compressed or Dynamic row format.
• The innodb_strict_mode default value was changed to ON. The previous default was OFF. When innodb_strict_mode is enabled, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior).

Source: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html

[mintbridge]

Yeah i understand whats going on, my issue is that out of the box things don't work, if mysql5.7 is a requirement we should document that, or at least have something in the docs to explain whats going on when this happens

[sisve]

#11518 (comment)

[alexardalich]

Been playing with this one tonight.

Did the switch from mysql -> mariadb. Going back to get mysql 5.7 to make this go away would cost me a lot of time with having to dump and replay a couple of large databases.

When mariadb 10.2 is stable and released, this will go away as the defaults mirror mysql 5.7.7

Using mariadb 10.1.21
Adding to my.cnf
innodb_large_prefix=1
innodb_file_format=Barracuda

which are both defaults in 10.2

unfortunately the directive ROW_FORMAT=DYNAMIC only exists from 10.2.2

but found adding the directive to the sql string in MySqlGrammar after collation for every table creation does the trick and creates the larger index keeping everything happy in 10.1.x

Believe this will also work for MySQL 5.6

[sisve]

An hack would be $table->engine='innodb ROW_FORMAT=DYNAMIC'; in every migration that creates a table. The dynamic row_format is the default starting from mariadb 10.2.2, it is supported starting at mysql 5.5 so mariadb 5.5 and upward (including all 10.x) should support it.

[nekromoff]

This should be still open. Lots of migrations just die on this issue for MariaDB users.

BTW, for anyone out there, the easy fix is here:
https://laravel-news.com/laravel-5-4-key-too-long-error

Why is this (191) not default for Laravel?

[whereisaaron]

I agree this should be open and probably the default length changed. I assume there is no special reason that it is 255 now, other than that used to be the max length back in MySQL < 5.0.3?

migrate should generate a schema compatible with the target database server. It knows the target database engine and version when it generates the schema, so it should be smarter about either warning the user, or generating a compatible schema.

There are lots of tickets for this same thing #17530 #17508 #17337 #17714 #20012 because migrate is creating a bad user experience.

On the upside, it is well documented 👍, but better migrate behavior would be preferable.

[dev-technoscore]

I propose an alternative to Schema::defaultStringLength(191); in artisan command line. Something like php artisan migrate -dsl=191. This way we can accommodate for various versions of Mysql/MariaDB in different environments.

[heychazza]

Any up to date ways to solve this? With latest mariadb, and laravel.

[eartahhj]

Problem still seems to persist as of today, at least for me.
I am using Laravel 9 and MySQL with MariaDB 10.3.3.
Note that innodb_large_prefix and innodb_file_format have been removed from MariaDB (see https://mariadb.com/kb/en/innodb-system-variables/)
At the same time it seems that starting from MariaDB 10.2.2 the default File Format has been changed to Barracuda, so I am wondering why the error keeps popping out.
Maybe we should just turn off MySQL Safe mode?
I mean, setting defaultStringLength(191) does not seem to be the best solution either anyway.