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

created at 12-30-2021 views: 740

problem

The zabbix monitor reported an error when importing the schema.sql file, and the following corresponding statements were found after inspection.

MySQL database version: 8.0.24

syntax

create database zabbix;

CREATE TABLE `items` (
    `itemid`        bigint unsigned                 NOT NULL,
    `type`          integer         DEFAULT '0'     NOT NULL,
    `snmp_oid`      varchar(512)    DEFAULT ''      NOT NULL,
    `hostid`        bigint unsigned                 NOT NULL,
    `name`          varchar(255)    DEFAULT ''      NOT NULL,
    `key_info`      varchar(2048)   DEFAULT ''      NOT NULL,
    PRIMARY KEY (itemid)
) ENGINE=InnoDB;

CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));

Error message: The length of the specified key exceeds 3072 bytes.

(root@localhost) [zabbix]> CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

solution

View the database default character set is utf8mb4

(root@localhost) [zabbix]> select @@global.character_set_server;
+-------------------------------+
| @@global.character_set_server |
+-------------------------------+
| utf8mb4                       |
+-------------------------------+

utf8mb4 character set, one character occupies 4 bytes

(root@localhost) [zabbix]> select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME='utf8mb4';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8mb4            | utf8mb4_0900_ai_ci   | UTF-8 Unicode |      4 |
+--------------------+----------------------+---------------+--------+

Index length calculation:

If NOT NULL is not set in the index field, one byte needs to be added. Fixed-length fields, such as:

  • INT occupies 4 bytes,
  • DATE occupies 3 bytes,
  • and CHAR(N) occupies N characters.
  • Variable length fields, such as VARCHAR(N), occupy N characters + 2 bytes.

Note the difference between characters and bytes, the length of characters depends on the current character set

MySQL 8.X version supports a single index column with a maximum length of 3072 bytes, and the created index column is a variable-length field VARCHAR, so the maximum supported length is (3072-1-2)/4≈767.

Then the index length specified by the index index_items_key that needs to be created is 1021, and I cannot change the length of the index, so I set the character set of the zabbix database to utf8 (you can also specify the character set of the table separately, but It is necessary to ensure that the character sets of the tables of the same business type are the same, otherwise the index will become invalid), and then re-import the SQL.

Utf8 one character occupies 3 bytes, the maximum length of a single index is (3072-1-2)/3=1023.

(root@localhost) [zabbix]> select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME='utf8';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8               | utf8_general_ci      | UTF-8 Unicode |      3 |
+--------------------+----------------------+---------------+--------+

Create statement

create database zabbix character utf8;

CREATE TABLE `items` (
    `itemid`        bigint unsigned                 NOT NULL,
    `type`          integer         DEFAULT '0'     NOT NULL,
    `snmp_oid`      varchar(512)    DEFAULT ''      NOT NULL,
    `hostid`        bigint unsigned                 NOT NULL,
    `name`          varchar(255)    DEFAULT ''      NOT NULL,
    `key_info`      varchar(2048)   DEFAULT ''      NOT NULL,
    PRIMARY KEY (itemid)
) ENGINE=InnoDB;

CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));

Index created successfully

(root@localhost) [zabbix]> CREATE INDEX `index_items_key` ON `items` (`hostid`,`key_info`(1021));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

PS:

The maximum length supported by MySQL 8.X single-column index is 3072bytes by default.

The maximum length supported by MySQL 5.7 single-column index is 768bytes by default. If the maximum length of single-column index supports 3072bytes, the parameter innodb_large_prefix=ON needs to be configured.

created at:12-30-2021
edited at: 06-04-2022: