Mysql problem during zabbix update from 4.2.8 to 5.0

[Andressa]

Hello.

I followed the documentation for making zabbix from version 4.2.8 to version 5.0.

The update was performed, however, two errors were recorded in the log file, as shown in the attached image. The first is related to the required version and the second is related to the query error.

Before performing the zabbix upgrade I checked the mysql and php version, both are at the required version (mysql=8.0.30, php=7.2.0).At that moment, it was possible to visualize the error referring to the first problem in the front-end (image: front_end_mandatory_version).

I tried to correct the error of the mandatory version through the command: update dbversion set mandatory=05000000,optional=05000000;

After executing the command (image:correct_mandatory_version) the log followed in the same way, with the same errors, but the front-end changed to the image: front_end_error.

The resolution shown in ZBX-20165 cannot be applied since the @@innodb_large_prefix variable does not exist, as shown in the image: mysql_dynamic_row_format

Therefore, the update was not completed successfully and I have not found a way in the documentation to resolve these issues.

[cyber]

Thou shall not update dbversion manually! Its about the same as slapping a Mercedes badge on a Lada..

As You see in logs, your db upgrade failed. updating dbversion does not resolve the issue... So concentrate on resolving this issue. All the rest, like update user and frontend displaying "db mismatch" etc... its all because of that failed db upgrade...
Unfortunately I do not work with MySQL, so I cannot help you here..

[Andressa]

Thank you for your answer.

I would like to know how to do this.
How can I solve this problem? Could you help me to solve it?

[tim.mooney]

I tried to correct the error of the mandatory version through the command: update dbversion set mandatory=05000000,optional=05000000;

As cyber has said, don't ever do that. That doesn't fix the problem, but it will cause other issues. Zabbix will automatically update those values after it has successfully completed an upgrade of the schema.

Your first screenshot shows that at least the first few tables have ROW_FORMAT=Dynamic, but it doesn't show all your Zabbix tables. Just to confirm, do all the Zabbix tables have ROW_FORMAT=Dynamic, including the 'items' table?

I've posted in these forums in the past about the upgrade issues related to ROW_FORMAT=Compact, for example my comments in this thread: https://www.zabbix.com/forum/zabbix-...ariadb-upgrade

However, in your case, if all your tables are already in ROW_FORMAT=Dynamic, then you already support the larger key sizes. If you look at the error you're receiving, it's not saying that the max key size is 767 bytes, it's saying that the max key size is 3072 bytes. That's the same max key size that I would expect for a correctly-configured InnoDB table. Whatever is causing your upgrade to fail, it doesn't appear to be because the tables are still in an older format.

I don't think this would cause a key size error (it's more likely to cause a row length error), but can you confirm that your database is using the correct character set and collation?

Is this a Zabbix install that started with a much older version, and it has been upgraded many times over the years?

[Andressa]

Hello thanks for your help.

I have consulted the requested information.
The format is already using ROW_FORMAT=Dynamic.
Zabbix was installed in version 4.2.8 and has never been updated. This will be the first update.

[tim.mooney]

My guess was correct: you're using the wrong character set. Zabbix does not support MySQL's full 4-byte UTF-8 (utf8mb4) until version 6.0. Before then, it's expected that you're using the 'utf8' or 'utf8mb3' character set, which is the 3-byte version.

See: https://support.zabbix.com/browse/ZBX-20905