Problem after upgrade from 4.0.6 to 4.2

[ChiefPilot]

Help, please, who can. Made an upgrade from 4.0.6 to 4.2 and got the following:

[Z3005] query failed: [1071] Specified key was too long; max key length is 767 bytes [create unique index lld_macro_path_1 on lld_macro_path (itemid,lld_macro)]

in server log. The base can not upgrade to 4020000. What can I do here? mysql: Ver 15.1 Distrib 10.0.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[kloczek]

On mysql 5.7.25 everything went smoothly ..

[holantomas]

Hi guys, I have same issue

[Z3005] query failed: [1071] Specified key was too long; max key length is 767 bytes [create unique index lld_macro_path_1 on lld_macro_path (itemid,lld_macro)]

DB is:

10.1.38-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

I Tried to change file format, without any progress

MariaDB [(none)]> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

The bad thing on this is I cannot rollback to 4.0.6 cause, "Wrong DB mandatory version"

[Atsushi]

Are you using utf8 as database character set?

ex.

MariaDB [zabbix]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [zabbix]>

[holantomas]

Hi, thx for reply
and yes. I forgot say that I'm using docker images.

I don't know how but there can be problem when using utf8mb4. But I cannot remeber how was DB created(I think, by automatic script in docker image).
Should I collate it to standard utf8?

MariaDB [zabbix]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

[holantomas]

Don't worry,
I already fix that.

first changed

default_character_set_name

to "utf8" and COLLATE to "utf8_general_ci"
Than check tables charset and found that all tables already have utf8 but upgrade script create one table with default utf8mb4.
So changed table charset to utf8 and utf8_general_ci.
Server successfully upgraded and working.

Thanks for point me out

[kloczek]

Quick search for "Specified key was too long; max key length is" using google
First link from the list https://stackoverflow.com/questions/...h-is-767-bytes
Looks like you are usung quite old DB engine.
If that is the cause your issue can be solved by mysql upgrade => it has nothing to do with zabbix per se.