Skip to main content

Why do I receive a database warning that the specified key was too long for my PHP application?

Aaron Horsley
  • Updated

Question

When installing a Larvel application, I see the following error when progressing through the installation.

Syntax error or access violation: 1071 Specified key was too long 

 

What can I do to resolve this?

 

Answer

 

This happens when the wrong character set is configured in the /etc/my.cnf file. To work around the issue, in Laravel modify the config/database.php and define the following character set:

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',

 

DYNAMIC allows to store long key indexes.

 

To change this on the MySQL server, you can use the following:

[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

 

For clients:

 

[mysql]
default-character-set=utf8mb4

 

After that is done, start and stop the MySQL service:

 

/scripts/restartsrv_mysql --stop
/scripts/restartsrv_mysql --start

 

 

Related articles