Specified key was too long; max key length is 3072 bytes

[manosnms]

Hello,

While I am trying to import data to database i receive the below error

[root@zabbix zabbix-server-pgsql-5.0.1]# zcat create.sql.gz | mysql -uzabbix -p zabbix
Enter password:
ERROR 1071 (42000) at line 348: Specified key was too long; max key length is 3072 byte

So I am not able to proceed with Zabbix installation

Details Cannot connect to the database.

• The frontend does not match Zabbix database.

mysql Ver 15.1 Distrib 10.5.4-MariaDB, for Linux (x86_64) using readline 5.1


Any help would be great.

Thank you

[gvtjongahung]

Please change your database to a different (smaller) character-sets and collations

https://mariadb.com/kb/en/supported-character-sets-and-collations/

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
+--------------------------+----------------------------+
| Variable_name        | Value |
+--------------------------+----------------------------+
 | character_set_client          | latin1 |
 | character_set_connection | latin1 |
 | character_set_database   | utf8 |
 | character_set_filesystem  | binary |
 | character_set_results       | latin1 |
 | character_set_server        | utf8 |
 | character_set_system       | utf8 |
 | character_sets_dir             | /usr/share/mysql/charsets/ |
 | collation_connection          | latin1_swedish_ci |
 | collation_database             | utf8_general_ci |
 | collation_server                  | utf8_general_ci |
+--------------------------+----------------------------+
11 rows in set (0.003 sec)

With docker it would be something like:
docker run --name mariadb \
--network zabbix \
-e MYSQL_ROOT_PASSWORD=stillInsecurePassword \
-e MYSQL_USER=zabbix \
-e MYSQL_DATABASE=zabbix_proxy \
-e MYSQL_PASSWORD=insecurePassword \
-p 3306:3306 \
-d mariadb \
--collation_server=utf8_general_ci \
--character_set_server=utf8

[frank108]

Mysql 8 throws the same error.
To suggest changing utf8mbr4 to utf8 makes no sense, utf8mbr4 will be more and more in use.
This is bug or non proper setting in sql backup.
Changing the line 258 from :

CREATE INDEX `items_1` ON `items` (`hostid`,`key_`(1021));

something like:

CREATE INDEX `items_1` ON `items` (`hostid`,`key_`(191));

and line 264 from:

CREATE INDEX `items_8` ON `items` (`key_`(1024));

to

CREATE INDEX `items_8` ON `items` (`key_`(1024));

However, maybe some different numbers for this index would be better, but going back to utf-8 is certainly not a good solution.