Ошибка specified key was too long

The Problem

There are max key length limits in MySQL.

• InnoDB — max key length is 1,536 bytes (for 8kb page size) and 768 (for 4kb page size) (Source: Dev.MySQL.com).
• MyISAM — max key length is 1,000 bytes (Source Dev.MySQL.com).

These are counted in bytes! So, a UTF-8 character may take more than one byte to be stored into the key.

Therefore, you have only two immediate solutions:

• Index only the first n’th characters of the text type.
• Create a FULL TEXT search — Everything will be Searchable within the Text, in a fashion similar to ElasticSearch

Indexing the First N’th Characters of a Text Type

If you are creating a table, use the following syntax to index some field’s first 255 characters: KEY sometextkey (SomeText(255)). Like so:

CREATE TABLE `MyTable` (
    `id` int(11) NOT NULL auto_increment,
    `SomeText` TEXT NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `sometextkey` (`SomeText`(255))
);

If you already have the table, then you can add a unique key to a field with: ADD UNIQUE(ConfigValue(20));. Like so:

ALTER TABLE
MyTable
ADD UNIQUE(`ConfigValue`(20));

If the name of the field is not a reserved MySQL keyword, then the backticks («`) are not necessary around the fieldname.

Creating a FULL TEXT Search

A Full Text search will allow you to search the entirety of the value of your TEXT field. It will do whole-word matching if you use NATURAL LANGUAGE MODE, or partial word matching if you use one of the other modes. See more on the options for FullText here: Dev.MySQL.com

Create your table with the text, and add the Full text index…

ALTER TABLE
        MyTable
ADD FULLTEXT INDEX
        `SomeTextKey` (`SomeTextField` DESC);

Then search your table like so…

SELECT
        MyTable.id, MyTable.Title,
MATCH
        (MyTable.Text)
AGAINST
        ('foobar' IN NATURAL LANGUAGE MODE) AS score
FROM
        MyTable
HAVING
        score > 0
ORDER BY
        score DESC;

You can increase the maximum InnoDB index prefix size in MySQL 5.6 to 3072 bytes by setting innodb_large_prefix to ON along with other settings that you’ll also need in order to enable that one, discussed here:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

These changes should allow these indexes to be valid for InnoDB tables.

With a character set of (I assume) utf8, a VARCHAR(1024) would need 1024 x 3 = 3072 bytes for its index.

Updates:

I incorrectly showed the value of the variable as Yes when it should have been ON.

But also:

I think it needs 3072+2 – ypercube

When I thought about this, it sounded correct, because 1 byte is needed to store the size of the value when the value is 255 bytes or less, and 2 bytes are needed otherwise.

However, testing reveals that this isn’t the case, in this case — InnoDB using the COMPRESSED row format from Barracuda can actually index the full size of a VARCHAR(1024)… so either they’ve documented it strangely or the COMPRESSED row format stores the length out-of-band along with another block of metadata, so it doesn’t count in the total bytes in this format.

COMPRESSED doesn’t truncate the index into a prefix index and throw a warning until you go to VARCHAR(1025).

+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Error | 1071 | Specified key was too long; max key length is 3072 bytes |
+-------+------+----------------------------------------------------------+

It’s nice the way it throws a warning instead of throwing an error, but that doesn’t help us here because this still requires the explicit ROW_FORMAT declaration to trigger this behavior.

So, my initial answer is still wrong, because you have to explicitly add ROW_FORMAT=COMPRESSED to the end of the table definition. Otherwise you still get the same old error.

Actually, you get two different errors in 5.6.10. If you try to create a table with a fully-indexed VARCHAR(1024) you get this:

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

…but if you try with a fully-indexed VARCHAR(1025) you get this:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

That’s sloppy code but the bottom line is that my answer doesn’t actually fix this problem.

I don’t see a way to use ROW_FORMAT=COMPRESSED by default, nor does it seem like a good idea if it were possible, and I’m inclined to suggest that the answer is…

…there’s not a readily available workaround here. My other thought was «character sets» but the difference between latin1 and utf8 still isn’t sufficient to explain 1,024 vs 1000 or 767. I’ll happily get behind a better idea but at the moment, I can’t think of a version of MySQL Server that this code would work properly on.

Migrating: 2021_02_23_202702_create_permission_tables

   IlluminateDatabaseQueryException 

  SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes (SQL: alter table `permissions` add unique `permissions_name_guard_name_unique`(`name`, `guard_name`))   

  at D:PROJECTvendorlaravelframeworksrcIlluminateDatabaseConnection.php:678
    674▕         // If an exception occurs when attempting to run a query, we'll format the error
    675▕         // message to include the bindings with SQL, which will make this exception a
    676▕         // lot more helpful to the developer instead of just the database's errors.
    677▕         catch (Exception $e) {
  ➜ 678▕             throw new QueryException(
    679▕                 $query, $this->prepareBindings($bindings), $e
    680▕             );
    681▕         }
    682▕

  1   D:PROJECTvendorlaravelframeworksrcIlluminateDatabaseConnection.php:471
      PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes")
key length is 1000 bytes")

  2   D:PROJECTvendorlaravelframeworksrcIlluminateDatabaseConnection.php:471
      PDOStatement::execute()