How to fix MySql: index column size too large (Laravel migrate)
Three solutions, each with a drawback:
MySQL 5.7 avoids the problem. Consider upgrading.
VARCHAR(255)
is usually var bigger than necessary. If you can safely shrink to 191 or less, the error will go away.Switch to utf8 (from utf8mb4), if you don't need Chinese or Emoji.
2 more options here: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes
As you can see in the error message - "The maximum column size is 767 bytes", if you want to create an index on it. A VARCHAR(255)
column can take up to 765 (255*3) bytes using utf8
and 1020 (255*4) bytes using utf8mb4
. This is because in MySQL utf8
takes up to 3 bytes and utf8mb4
up to 4 bytes (the real UTF8). Thus creating a VARCHAR(255)
(unique) index with utf8mb4
will fail.
This are your options to fix the problem:
Set default collation in my.ini
:
collation_server=utf8_unicode_ci
character_set_server=utf8
Set default collation for the database when creating:
CREATE DATABASE IF NOT EXISTS `your_db` COLLATE 'utf8_unicode_ci'
Set default collation for the table/column. (I don't recommend that)
Change the column size to 190 (varchar(190)
) or less.
Laravel 5.4 fix
The Mysql server configuration is overwriten by Laravel's migration command. It will set the collation and charset to the configuration's version.
Change the fields charset
and collation
of the db engine in the database config file located in config/database.php
.
..
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
//'charset' => 'utf8mb4',
//'collation' => 'utf8mb4_unicode_ci',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
..
For mariadb, update your *my.cnf
file with following configuration,
innodb_default_row_format=dynamic
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_large_prefix=true
Then, you have to restart mariadb
service for updated configuration to take effect.