Laravel 5 and Error key was too long

The server was running PHP applications without any fuss, everyone is happy on this 12th March 2018. Laravel 5 came in that one day and a simple application was launched to show the default Laravel welcome screen. The next step was to create a CRUD application with storage to the local database.

A Model is created of a simple Task object. The room fell silence after the command, php artisan migrate

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

What was wrong? Never mind that, lets see whats on the server!

• Centos version 7.4.1708
• Apache httpd  version Apache/2.4.6 (CentOS)
• Mariadb (MySQL fork) version 5.5.56-MariaDB
• PHP version 7.2.2 (cli) with Zend Engine v3.2.0
• Laravel 5.6.7
• Composer versioon 1.6.3
• npm version 3.10.10

All of above are tested and working on its own. Going back to analyse the error, its database related. The respective tables of migrate, users were generated in the database, but the new model "task" isn't.The related code is in the file <laravel5>/database/migrations/2014_10_12_000000_create_users_table.php

On the database side, changed Collation to utf8mb4_unicode_ci but the error is still there. This is a 4-byte UTF-8 Unicode Encoding and as described in MySQL 5.5, it is

1. In UTF8 it uses 3 bytes per character, containing only BMP characters. Utf8mb4 supports maximum 4 bytes per character and includes supplementary characters. 
2. Utf8mb4 is a superset of Utf8.
3. To save space, utf8mb4 is to use VARCHAR instead of CHAR type for BMP characters. 

 Changing a table from utf8 to utf8mb4 can be done with
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

e.g.
ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Solution

Make the whole Laravel application and the database server utf8mb4 aware.

Step 1. Edit file  <laravel5>/config/database.php

'charset' => 'utf8mb4',

'collation' => 'utf8mb4_unicode_ci',

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

DYNAMIC allows to store of long key indexes.

Step 2.  Edit file /etc/my.cnf

[mysqld]

# default character set and collation

collation-server = utf8mb4_unicode_ci

character-set-server = utf8mb4

# utf8mb4 long key index

innodb_large_prefix = 1

innodb_file_format = barracuda

innodb_file_format_max = barracuda

innodb_file_per_table = 1

 Step 3. Edit file /etc/my.cnf.d/client.cnf

[mysql]

default-character-set=utf8mb4

Step 4. Restart database server

Stop then start the database server with

$ sudo systemctl stop mysqld

$ sudo systemctl start mysqld

Done.

Points to note

The error states maximum key length is 767 bytes for the email field. In Laravel 5 the migration file for "Users" declared a string that gets created in the Mariadb database. Problem in question is adding the "unique" string that get generated in the database "Users" for field "email" with type VARCHAR(255) and collation utf8mb4. MySQL has to actually index the whole value to complete the operation. This calculates to give maximum size of

255 characters x 4 bytes = 1,020 bytes (MAXIMUM BYTES)

This looks like sufficient space, but why the error?
When Laravel writes/reads data to MySQL, it utilises a call to the local MySQL client/server that takes this as default of uf8 which is having maximum 3 bytes per character (due to the mysql default client/server settings). A little bit of calculation gives

255 characters x 3 bytes = 765 bytes (MAXIMUM BYTES)

In the above solution, there is access to modify the database configurations. What if you do not have such an access? The solution is to reduce the default string length sent by Laravel. Taking 191 as maximum characters, we get

191 characters x 4 bytes = 764 bytes

Edit file <laravel5>/app/Providers/AppServiceProvider.php

Add after the "use .... \ServiceProvider;"
use Illuminate\Support\Facades\Schema;

Add in the "public function boot( )"
Schema::defaultStringLength(191);

Save and from the project's main folder, run migration
$ php artisan migrate

OR
Alternatively, changed the field "email" that was declared unique to collation "utf8". It worked for me, but some people have mentioned it didn't work for them.

Done