Error code 1071 specified key was too long max key length is 1000 bytes

As @Devart says, the total length of your index is too long.

The short answer is that you shouldn’t be indexing such long VARCHAR columns anyway, because the index will be very bulky and inefficient.

The best practice is to use prefix indexes so you’re only indexing a left substring of the data. Most of your data will be a lot shorter than 255 characters anyway.

You can declare a prefix length per column as you define the index. For example:

...
KEY `index` (`parent_menu_id`,`menu_link`(50),`plugin`(50),`alias`(50))
...

But what’s the best prefix length for a given column? Here’s a method to find out:

SELECT
 ROUND(SUM(LENGTH(`menu_link`)<10)*100/COUNT(`menu_link`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`menu_link`)<20)*100/COUNT(`menu_link`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`menu_link`)<50)*100/COUNT(`menu_link`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`menu_link`)<100)*100/COUNT(`menu_link`),2) AS pct_length_100
FROM `pds_core_menu_items`;

It tells you the proportion of rows that have no more than a given string length in the menu_link column. You might see output like this:

+---------------+---------------+---------------+----------------+
| pct_length_10 | pct_length_20 | pct_length_50 | pct_length_100 |
+---------------+---------------+---------------+----------------+
|         21.78 |         80.20 |        100.00 |         100.00 |
+---------------+---------------+---------------+----------------+

This tells you that 80% of your strings are less than 20 characters, and all of your strings are less than 50 characters. So there’s no need to index more than a prefix length of 50, and certainly no need to index the full length of 255 characters.

PS: The INT(1) and INT(32) data types indicates another misunderstanding about MySQL. The numeric argument has no effect related to storage or the range of values allowed for the column. INT is always 4 bytes, and it always allows values from -2147483648 to 2147483647. The numeric argument is about padding values during display, which has no effect unless you use the ZEROFILL option.

NO_ENGINE_SUBSTITUTION disabled with INNODB not active, a bad combination

• Up to MySql 5.5 the sqlmode default was a blank string, that means that the sqlmode NO_ENGINE_SUBSTITUTION was not set by default

According to MySql docs (see https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_no_engine_substitution) this is the meaning of sqlmode NO_ENGINE_SUBSTITUTION:

Control automatic substitution of the default storage engine when a
statement such as CREATE TABLE or ALTER TABLE specifies a storage
engine that is disabled or not compiled in.

Because storage engines can be pluggable at runtime, unavailable
engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default
engine is used and a warning occurs if the desired engine is
unavailable. For ALTER TABLE, a warning occurs and the table is not
altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is
not created or altered if the desired engine is unavailable.

• So: if NO_ENGINE_SUBSTITUTION is disabled AND INNODB is switched OFF, MySql will switch to MYISAM also if you specify INNODB in your CREATE TABLE statement.
• If the table you are creating is OK for MYISAM, you just receive a warning and the table is created. That is not your case, your creation statement include an index that is beyond the 1000 bytes limit of MYISAM, then the creation fails with error 1071 reporting the error of MYISAM. That is because the working engine is MYISAM, not INNODB.

PROOF

MySql version 5.1.56 community

Case 1:

Options in my.cnf  
sql-mode=""  
default-storage-engine=MYISAM  
skip-innodb uncommented (without#) 

Return on execution of your create statement:
Error Code: 1071. Specified key was too long; max key length is 1000 bytes

Explanation: INNODB is not active, the engine is automatically switched to MYISAM  
that returns this error as they key is longer than MYISAM 1000 bytes limit.  
The key length is: 
2 fields x 255 char x 3 bytes utf8 encoding + 2 x 1 length byte = 1532 bytes   

Case 2:

Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb uncommented (without#)

Return on execution of your create statement:
Error Code: 1286. Unknown table engine 'INNODB'

Explanation: INNODB is not active but the engine substitution is not permitted
by sql mode therefore the DB returns an error about the attempt of using a disabled engine. 

Case 3:

Options in my.cnf
sql-mode="NO_ENGINE_SUBSTITUTION"
default-storage-engine=MYISAM
skip-innodb commented (with#)

Return on execution of your create statement:
Table creation OK!

Explanation: INNODB is active (skip-innodb commented) and it is used also if      
the default engine is MYISAM.

To reproduce the tests restart MySql after every change in my.cnf.

Since MySql version 5.6 sqlmode is no more empty by default and contains NO_ENGINE_SUBSTITUTION, moreover INNODB is the default engine, so the error is difficult to meet.

OTHER TESTS

No other way of reproducing the error:

Error Code: 1071. Specified key was too long; max key length is 1000 bytes 

while trying to create an INNODB table has been found.

In INNODB you have two kinds of ERROR 1071:

Error Code: 1071. Specified key was too long; max key length is 767 bytes

this has nothing to do with innodb_large_prefix ON or OFF, but is only related to the size of a single VARCHAR column being used as an index.

Mysql store varchar utf8 with 3 bytes plus 1 byte for the length up 255 character and 2 after (see: http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html), so if you try to set a key with VARCHAR(256) utf8 you get:

256 x 3 + 2 = 770 bytes

and you get the previous error, as the max key length for a single column is 767 bytes for an InnoDB table. A VARCHAR(255) is ok, because:

255 x 3 + 1 = 766 bytes

I tested it on four installations of Mysql, version 5.1.56, 5.5.33, 5.6 and 5.7, and that is confirmed. No issue with your query with VARCHAR(255), issue with VARCHAR(256):

Error Code: 1071. Specified key was too long; max key length is 767 bytes

As you can see the message is different, because it is an INNODB message not a MYISAM one!

The other type of ERROR 1071 for INNODB tables is:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

This is related to keys with multiple columns. For those keys to be enabled you need to set innodb_large_prefix to on.
Anyway, if you try to run something like this:

CREATE TABLE `phppos_modules_actions` (
  `action_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `module_id` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `action_name_key2` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
  `sort` INT NOT NULL ,
  PRIMARY KEY ( `action_id` , `module_id`, `action_name_key`, `action_name_key1`, `action_name_key2` )
) ENGINE = INNODB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

With a key of 5 VARCHAR(255) utf8 columns, that is 3830 bytes, you will run into the:

Error Code: 1071. Specified key was too long; max key length is 3072 bytes

Exotic Hypothesis

During the search of the cause I formulated and tested different and pretty weird hypothesis:

ROW format

Tested REDUNDANT, COMPACT, COMPRESS, DYNAMIC: no impact on table creation with your statement.

FILE format

Tested Antelope and Barracuda: no impact on table creation with your statement.

MySql built

Tested 32bit and 64 bit MySql: no impact on table creation with your statement.

Others similar failures

Here you can find the same error in the same situation:

https://www.drupal.org/node/2466287

I tested that statement in the 3 test situations listed in PROOF and it reproduced exactly the same behavior as yours, so I can say the issue is the same. In that case they switched to other DB, but the problem is the mix of setting, not the DB version.

References

A very good article of indexing with INNODB is given here:

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

WARNING: disabling INNODB by uncommenting skip-innodb in my.cnf after the creation of INNODB table with index longer than 1000 will not permit the start of MySql service

Regards

You may to have adjust some VARCHAR length in some table based on the Character Set you are using.

That exact error message is actually posted as a bug report from Nov 2004, when in fact, it is not really a bug. That should direct you on how to adjust key lengths, especially your PRIMARY KEYs.

If you know which table is causing the Error 1071, you have to do the following:

Step 01) mysqldump only database schema

mysqldump --routines --triggers --all-databases --no-data > MySQLSchema.sql

Step 02) mysqldump only data

mysqldump --all-databases --no-create-info > MySQLData.sql

Step 03) Using vi or some other editor, edit the table’s PRIMARY KEY to manually limit the PRIMARY KEY in such a way that the key does not exceed 1000 characters.

Step 04) Load the schema

mysql -A < MySQLSchema.sql

Step 05) Load the data

mysql -A < MySQLData.sql

You may have to resort to this if you cannot otherwise figure this out,

UPDATE 2012-01-23 11:43 EDT

Since you mentioned a certain table has the wrong storage engine, here is what you do:

Look above at Step 03. Go find the table in the file. Look for ENGINE=MyISAM in the file and change it to ENGINE=InnoDB. Then, load the schema file first and the data file afterwards.

UPDATE 2012-01-23 11:52 EDT

You should add this to to /etc/my.cnf

[mysqld]
default-storage-engine=InnoDB

then run service mysql restart. If you cannot restart mysql at this time, run this in the mysql client:

mysql> SET GLOBAL default_storage_engine = 'InnoDB';

However, the mysqldump will still have the ENGINE=MyISAM at the end of each CREATE TABLE. You will have to perform the 5-Step plan I mentioned above including changing the ENGINE=MyISAM to ENGINE=InnoDB. DO NOT CHANGE THAT FOR THE mysql DATABASE !!! Change only those tables that are yours.

Согласно информации портала laravel-news, начиная с версии Laravel 5.4 были внесены изменения в дефолтный charset базы данных. Если быть более точным, то 25.10.2016 года Taylor Otwell сделал соответствующий коммит с аннотацией «use utf8mb4 as default character set». Данная кодировка поддерживает хранение emoji.

Таким образом, во всех современных релизах Laravel, после 25.10.16 установлена кодировка по умолчанию utf8mb4. Именно она и вызывает ошибку Specified key was too long. Однако, ошибка не должна проявляться при выполнении миграции для БД MySQL v5.7.7 или старше или MariaDB старше чем 10.2.2.

Но что же делать, если у Вас все таки при выполнении миграций:

php artisan migrate

 в консоли появляется вот такая неприятная ошибка?

IlluminateDatabaseQueryException  : SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `users` add unique `users_email_unique`(`email`))

Exception trace:

1   DoctrineDBALDriverPDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes")

Ну что же, если Вам не повезло и подобная ошибка все таки появилась — не расстраивайтесь. Она довольно быстро и легко исправляется и сейчас я расскажу, как это можно сделать.

Варианты исправления ошибки Laravel: Specified key was too long

Собственно, как и всегда, у нас на выбор есть несколько вариантов решения данной проблемы. Рассмотрим их далее по очереди:

1. Переход на более новую версию MySQL

Это наиболее очевидный вариант. И если вы только создаете новое приложение, то конечно же лучше сразу выбрать более новую версию MySQL. Зачем разрабатывать на устаревших технологиях? Но! Если у вас шаред хостинг, то скорее всего опция смены версии БД у вас отсутствует.

2.  Изменить параметр defaultStringLegth

Если открыть оф. документацию Laravel в разделе «Создание Индексов», то разработчики фреймворка советуют установить вручную параметр defaultStringLegth равным 191 символам для более старых версий MySQL или MariaDB. Сделать это возможно, вызвав метод Schema::defaultStringLength внутри AppServiceProvider. Для этого открываем файл:

nano app/Providers/AppServiceProvider.php

и добавляем вызов в методе boot, предварительно импортировав неймспейс Schema:

use IlluminateSupportFacadesSchema;

/**
 * Bootstrap any application services.
 *
 * @return void
 */
public function boot()
{
    Schema::defaultStringLength(191);
}

После этого Ваши миграции будут успешно выполняться.

3. Уменьшить длину строки в отдельно взятой миграции

Вместо глобального изменения длины строки для всех миграций, возможно изменить длину строки непосредственно для определенной таблицы. К примеру, изменим значение длины строки для миграции, создающей таблицу пользователей:

Schema::defaultStringLength(191);

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

4. Уменьшить длину индекса, но не уменьшать длину строки

Аналогично, тому как можно устанавливать длину строки непосредственно при выполнении миграции в отедльно взятую таблицу, можно уменьшить длину индекса в поле, но не уменьшать при этом длину самого поля. Для этого все в том же файле миграции нужно добавить:

$table->index([DB::raw('email(191)')]);

5. Изменить кодировку в таблице на utf8

Не знаю как Вам, но лично мне не сильно нравится рекомендуемый вариант с уменьшением длины строки. Вместо этого я предпочитаю изменить кодировку по умолчанию в своей БД на utf8, т.к. хранить emoji я в ней явно не планирую. За одно я всегда помимо смены кодировки (charset), еще и меняю сравнение (collation) на utf8_general_ci. Для этого в файле конфигурации БД:

nano app/config/database.php

я меняю для mySQL соответствующие значения на:

'charset' => 'utf8',
'collation' => 'utf8_general_ci',

Теперь у Вас всё должно работать как полагается! Если у Вас остались вопросы — смело задавайте в комментариях.

Странная ошибка, возникающая при выполнении первой миграции в Laravel 8, тянется ещё со времён Laravel 5.4. И её почему-то до сих пор не устранили. Связано ли это с тем, что программисты, работающие с Laravel, предпочитают исключительно MySQL и люто ненавидят MariaDB, или ещё по какой причине, но ошибка случается и поправить её на самом деле, не составляет труда.

Ошибка выглядит следующим образом. При выполнении команды:

php artisan migrate

Миграции начинают обрабатываться:

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table

Но тут же вылетает сообщение об ошибке:

   IlluminateDatabaseQueryException

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `users` add unique `users_email_unique`(`email`))

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:671
    667▕         // If an exception occurs when attempting to run a query, we'll format the error
    668▕         // message to include the bindings with SQL, which will make this exception a
    669▕         // lot more helpful to the developer instead of just the database's errors.
    670▕         catch (Exception $e) {
  ➜ 671▕             throw new QueryException(
    672▕                 $query, $this->prepareBindings($bindings), $e
    673▕             );
    674▕         }
    675▕

      +9 vendor frames
  10  database/migrations/2014_10_12_000000_create_users_table.php:26
      IlluminateSupportFacadesFacade::__callStatic("create")

      +21 vendor frames
  32  artisan:37
      IlluminateFoundationConsoleKernel::handle(Object(SymfonyComponentConsoleInputArgvInput), Object(SymfonyComponentConsoleOutputConsoleOutput))

Решение проблемы с ошибкой php artisan migrate — SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes при применении миграции в Laravel

Для устранения этой ошибки нужно внести изменения в метод boot класса AppServiceProvider. Для этого открываем файл, находящийся по адресу:

/папка_проекта/app/Providers/AppServiceProvider.php

Изначально он имеет вид:

<?php

namespace AppProviders;

use IlluminateSupportServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        //
    }
}

В него нужно вписать 2 строчки (использование библиотеки фасадов use IlluminateSupportFacadesSchema; и модификацию метода boot: Schema::defaultStringLength(191);) таким вот образом:

use IlluminateSupportFacadesSchema;

public function boot()
{
    Schema::defaultStringLength(191);
}

Таким образом модифицированный файл с классом AppServiceProvider будет иметь вид (добавляется 6-я и 26-я строки):

<?php

namespace AppProviders;

use IlluminateSupportServiceProvider;
use IlluminateSupportFacadesSchema;
class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        Schema::defaultStringLength(191);
    }
}

После сохранения этих изменений, команда php artisan migrate работает без ошибок, все миграции применяются и проект на фреймворке Laravel оживает. =)

Важно(!)
Перед тем, как накатывать повторно миграции, удалите созданные ранее таблицы из базы данных.
Иначе будет новая ошибка!

По материалам:
laravel-news.com

Заберите ссылку на статью к себе, чтобы потом легко её найти!
Выберите, то, чем пользуетесь чаще всего: