A Model is created of a simple Task object. The room fell silence after the command, php artisan migrate
SQLSTATE: 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
- In UTF8 it uses 3 bytes per character, containing only BMP characters. Utf8mb4 supports maximum 4 bytes per character and includes supplementary characters.
- Utf8mb4 is a superset of Utf8.
- To save space, utf8mb4 is to use VARCHAR instead of CHAR type for BMP characters.
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SolutionMake the whole Laravel application and the database server utf8mb4 aware.
Step 1. Edit file <laravel5>/config/database.php
Step 2. Edit file /etc/my.cnf
Step 3. Edit file /etc/my.cnf.d/client.cnf
Step 4. Restart database serverStop then start the database server with
Points to noteThe 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;"
Add in the "public function boot( )"
Save and from the project's main folder, run migration
$ php artisan migrate
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.