How to fix MySql: index column size too large (Laravel migrate)

Question

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?

set the length for the key: **alter table `courses` add unique `courses_name_unique` (FIELDNAME(299));** — Bernd Buffen, Feb 04 '17 at 17:22
@BerndBuffen I want to use Laravels migrate method. That method creates the tables and fails. Why does the migration works on database server A and not on B? Is there a setting that is not set correctly? — Sven van Zoelen, Feb 04 '17 at 17:23
You are probably using different charsets. While `varchar(255)` might be indexed with `utf8` - it will fail with `utf8mb4` — Paul Spiegel, Feb 04 '17 at 17:25
@PaulSpiegel you're correct, the migration command creates `utf8_unicode_ci` on the working server, and `utf8mb4_unicode_ci` collation on the not working one. How can I make the database create the correct charset by default? — Sven van Zoelen, Feb 04 '17 at 17:31

score 67 · Accepted Answer · edited Feb 04 '17 at 18:58

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

Thanks, you had put me on the right track! The server config you provided didn't work because the latest version of laravel (5.4) changed the default collation and charset. I updated your answer to also include the Laravel fix. — Sven van Zoelen, Feb 04 '17 at 19:00
Or you can follow this answer which is better : https://stackoverflow.com/questions/23786359/laravel-migration-unique-key-is-too-long-even-if-specified/39750202#39750202 — Arcesilas, May 30 '17 at 19:45

Pratik · Answer 2 · 2018-11-19T08:01:00.797

20

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.

edited Nov 19 '18 at 08:01

answered Oct 12 '18 at 11:41

Pratik

959
1
14
20

1

Thanks! This was very helpful. – Amit Joki Oct 15 '18 at 10:36
1

Thanks for pointing out about the [row format](https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html) option - I had the other config but not that, so my key length was stuck at 767. – artfulrobot Jun 30 '19 at 10:32
for me, I had to edit */etc/mysql/mariadb.conf.d/50-mysql_innodb_custom.cnf* on my Ubuntu server 18.04 and add the line `innodb_default_row_format=dynamic` – razor7 Feb 21 '20 at 18:57
2

This doesn't work!! I added those lines and my mariadb stopped working!! – Martijn Hiemstra Apr 12 '20 at 21:24

Rick James · Answer 3 · 2022-06-27T00:59:17.637

7

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

edited Jun 27 '22 at 00:59

answered Feb 05 '17 at 02:08

Rick James

135,179
13
127
222

More details about "191" are in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta – Rick James Nov 27 '17 at 17:14
> "MySQL 5.7 avoids the problem", you mean because they have the [`innodb_large_prefix`](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix) [enabled by default](https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html)? – guessimtoolate Mar 12 '19 at 10:24
@guessimtoolate - yes. (and some related things; see my link) – Rick James Mar 12 '19 at 15:16

score 6 · Answer 4 · answered Mar 09 '20 at 12:55

For Laravel 5.5 or higher change on your config/database.php file the engine to InnoDB ROW_FORMAT=DYNAMIC:

'connections' => [
    ...
    'mysql' => [
        ...
        'engine' => 'InnoDB ROW_FORMAT=DYNAMIC',
 ]]

Thanks to laracasts: https://laracasts.com/discuss/channels/eloquent/migrations-and-table-options-row-format

score 6 · Answer 5 · edited Feb 24 '22 at 17:03

6

In App\Providers\AppServiceProvider.php, import Schema with:

use Illuminate\Support\Facades\Schema;

and in the boot() function add this line:

Schema::defaultStringLength(191);

edited Feb 24 '22 at 17:03

Daishi

12,681
1
19
22

answered Aug 27 '20 at 09:34

Mohamed Shahid

476
5
18

score 4 · Answer 6 · answered Apr 01 '18 at 05:12

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

score 1 · Answer 7 · answered Aug 14 '17 at 15:40

1

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

answered Aug 14 '17 at 15:40

Fulela

21
4

1

utf8_general_ci is deprecated. Use utf8mb4 instead. – psylosss May 22 '19 at 20:56
In Laravel 8, I applied this suggestion (utf8 / utf8_unicode_ci) and it works perfectly with MySQL 5.7.36 – jgarcias Dec 30 '21 at 13:41

score 0 · Answer 8 · edited Feb 26 '18 at 13:06

0

It works with 5.5.52-MariaDB.

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

edited Feb 26 '18 at 13:06

Ivan Aracki

4,861
11
59
73

answered Feb 26 '18 at 12:15

Anton

19
1

utf8_general_ci is deprecated. Use utf8mb4 instead. – psylosss May 22 '19 at 20:56

How to fix MySql: index column size too large (Laravel migrate)

8 Answers8

Laravel 5.4 fix

Linked