How to fix MySql: index column size too large (Lar

问题:

I duplicated a project with a vagrant box which installs Debian, Nginx, PhpMyAdmin, .. With the new project the Laravel's php artisan migrate is not working anymore and I get the error:

[Illuminate\Database\QueryException]                                                                                                                                      
  SQLSTATE[HY000]: General error: 1709 Index column size too large. The maximum column size is 767 bytes. (SQL: alter table `courses` add unique `courses_name_unique`(`na  
  me`))

When I make a dump (structure + data) of the working project database and import it in the database giving the errors on migrate, then everything is ok and it creates all the tables and data is imported..

How can I fix the size so that I can run the migrate method?

回答1:

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,
        ],
..

回答2:

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.

回答3:

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.

回答4:

By default MySQL uses the character set utf8 which means that we use 3 bytes for every 1 character. This means, column type of varchar(10) uses 30 bytes resulting in the max prefix size for compact row format to be equivalent to varchar(255). That is 255 * 3bytes = 765 bytes which is, two bytes less than the max of 767 bytes.

With innodb_large_prefix set to on and using row format COMPRESSED or DYNAMIC, you can increase the max prefix character size to 65536 bytes instead of 767 bytes. The below chart shows the max character length with InnoDB large prefix and [COMPRESSED| DYNAMIC] row formats. These values, expect for utf8mb4, are higher than the maximum row size of a table, so there is no way to hit these limits

More info here https://discuss.pivotal.io/hc/en-us/articles/115004086747-Apps-are-down-due-to-the-Maximum-Column-Size-is-767-bytes-Constraint-in-MySQL

回答5:

Also had this issue what I just did, was revert from utf8mb4_unicode_ci to utf8_unicode_ci in the db connection script

回答6:

It works with 5.5.52-MariaDB.

Set all encoding to utf8_general_ci (server, database, connection).