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

by

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

More Related Contents:

  1. With MySQL, how can I generate a column containing the record index in a table?
  2. How do I add indexes to MySQL tables?
  3. Is it possible to have function-based index in MySQL?
  4. Laravel Migration Error: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
  5. How do MySQL indexes work?
  6. Best type of indexing when there is LIKE clause [duplicate]
  7. Does MySQL index foreign key columns automatically?
  8. What does the KEY keyword mean?
  9. Difference between Key, Primary Key, Unique Key and Index in MySQL
  10. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  11. How to see indexes for a database or table in MySQL?
  12. Speeding up row counting in MySQL
  13. What is cardinality in MySQL?
  14. MySQL varchar index length
  15. What’s the difference between using INDEX vs KEY in MySQL?
  16. Mysql Improve Search Performance with wildcards (%%)
  17. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  18. How do I get MySQL to use an INDEX for view query?
  19. MySQL performance: multiple tables vs. index on single table and partitions
  20. Mysql covering vs composite vs column index
  21. How to disable index in innodb
  22. Higher cardinality column first in an index when involving a range?
  23. MySQL load data infile – acceleration?
  24. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  25. Can MySQL use multiple indexes for a single query?
  26. MySQL 5.0 indexes – Unique vs Non Unique
  27. MySQL distinction between e and é (e acute) – UNIQUE index
  28. MySQL optimizing INSERT speed being slowed down because of indices
  29. MySQL: how to index an “OR” clause
  30. Mysql performance on 6 million row table

Leave a Comment