Error 1071 specified key was too long max key length is 767 bytes

Are you stuck with the error message ‘SQL error 1071’ and looking for a solution? We can help you resolve this SQL error

This SQL error mainly occurs if the combined key is too long. So by adjusting the varchar value, we can resolve this error message.

Here at Bobcares, we have seen several such SQL-related errors as part of our Server Management Services for web hosts and online service providers.

Today, let us see why this SQL error message occurs and also take a look at how our Support Engineers resolve this error.

How we resolve ‘SQL error 1071’

Now let us take a look at how our Support Engineers resolve this SQL error message for our customers.

1. Recently, one of our customers came across the error message ‘#1071 – Specified key was too long; max key length is 767 bytes’ while running the below query.

CREATE TABLE wp_locations (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`place` VARCHAR(255) NOT NULL,
`name` VARCHAR(255) NOT NULL,
CONSTRAINT `place_name` UNIQUE (`city`, `name`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Here it is clear that the combined key is too long. So we need to either make separate keys or reduce the column lengths.

Generally, MySQL always reserves the max amount for a UTF8 field which is 4 bytes so with 255 + 255 with the DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; this query is over the 767 max key length limit.

So we suggested our customer reduce the single varchar length or not use a composite key.

So our customer resolved this error by reducing the varchar value to 128.

2. Another way to fix such an error is to add the below lines in /etc/my.conf.d directory named umb4-support.cnf

[mysqld]
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true

After that, we can restart the SQL service.

[Need any further assistance with SQL-related errors? – We are here to help you.]

Conclusion

In short, this ‘SQL error 1071’ mainly occurs if the combined key is too long, and adjusting the varchar value must resolve this error. Today, we saw how our Support Engineers resolve this SQL error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Ispirer Home Page
Ispirer SQLWays Product Page — Migration to MySQL
Request SQLWays

CREATE TABLE department 
  (
    id INT,
    name VARCHAR(1000)
   );

ALTER TABLE department
  ADD PRIMARY KEY (id, name);

ERROR 1071 (42000) at line 8: Specified key was too long; max key length is 767 bytes

ALTER TABLE department
  ADD PRIMARY KEY (id, name(100));

Мы столкнулись с этой проблемой при попытке добавить индекс UNIQUE в поле VARCHAR (255), используя utf8mb4. Хотя проблема здесь уже хорошо описана, я хотел бы добавить некоторые практические советы о том, как мы это поняли и решили.

При использовании utf8mb4 символы считаются 4 байтами, тогда как под utf8 они могут составлять 3 байта. Базы данных InnoDB имеют ограничение на то, что индексы могут содержать только 767 байт. Поэтому при использовании utf8 вы можете сохранить 255 символов (767/3 = 255), но используя utf8mb4, вы можете хранить только 191 символ (767/4 = 191).

Вы абсолютно можете добавлять регулярные индексы для полей VARCHAR(255), используя utf8mb4, но случается, что размер индекса усекается с 191 символом автоматически — например unique_key здесь:

Это прекрасно, потому что регулярные индексы просто используются для быстрого поиска MySQL через ваши данные. Не нужно индексировать все поле.

Итак, почему MySQL автоматически обрезает индекс для обычных индексов, но бросает явную ошибку при попытке сделать это для уникальных индексов? Ну, для того, чтобы MySQL смог выяснить, существует ли уже существующее или обновляемое значение, ему нужно фактически индексировать все значение, а не только его часть.

В конце дня, если вы хотите иметь уникальный индекс в поле, все содержимое поля должно вписываться в индекс. Для utf8mb4 это означает сокращение длины полей VARCHAR до 191 символа или меньше. Если вам не нужна utf8mb4 для этой таблицы или поля, вы можете вернуть ее обратно в utf8 и сохранить свои длины длины.

Странная ошибка, возникающая при выполнении первой миграции в 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

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