Zabbix 4.0.4 - upgrade MySQL 5.7.25 to 8.0.15

[stevenfoo]

Hi All,

After resolving the issue on PHP 7.3.3, now we are looking into upgrade the database from 5.7.25 to 8.0.15.
Before we upgrade the database from mysql 5.6.38 to 5.7.25 first as a pre-requisite.

Before we upgrade to mysql 8.0.15 we run the upgrade check and encountered a list of messages that required Zabbix database to be updated.

Any idea from anyone or from Zabbix developer on how to resolved the below, What are the standard guideline for it?


mysqlsh -u root -p -e "util.checkForServerUpgrade();"


2) Usage of db objects with names conflicting with reserved keywords in 8.0
Warning: The following objects have names that conflict with reserved
keywords that are new to 8.0. Ensure queries sent by your applications use
`quotes` when referring to them or they will result in errors.
More information:
https://dev.mysql.com/doc/refman/en/keywords.html

zabbix.groups - Table name
zabbix.functions.function - Column name

3) Usage of utf8mb3 charset
Warning: The following objects use the utf8mb3 character set. It is
recommended to convert them to use utf8mb4 instead, for improved Unicode
support.
More information:
https://dev.mysql.com/doc/refman/8.0...e-utf8mb3.html

zabbix.acknowledges.message - column's default character set: utf8
zabbix.actions.name - column's default character set: utf8
zabbix.actions.esc_period - column's default character set: utf8
zabbix.actions.def_shortdata - column's default character set: utf8
...
...
...
...
...
(all the zabbix tables are affected).

12) Removed system variables for error logging to the system log configuration
ERROR: This check requires server to be at minimum at 8.0.11 version

More information:
https://dev.mysql.com/doc/relnotes/m...8-0-13-logging


15) New default authentication plugin considerations
Warning: The new default authentication plugin 'caching_sha2_password' offers
more secure password hashing than previously used 'mysql_native_password'
(and consequent improved client connection authentication). However, it also
has compatibility implications that may affect existing MySQL installations.
If your MySQL installation must serve pre-8.0 clients and you encounter
compatibility issues after upgrading, the simplest way to address those
issues is to reconfigure the server to revert to the previous default
authentication plugin (mysql_native_password). For example, use these lines
in the server option file:

[mysqld]
default_authentication_plugin=mysql_native_passwor d

However, the setting should be viewed as temporary, not as a long term or
permanent solution, because it causes new accounts created with the setting
in effect to forego the improved authentication security.
If you are using replication please take time to understand how the
authentication plugin changes may impact you.
More information:
https://dev.mysql.com/doc/refman/8.0...ibility-issues
https://dev.mysql.com/doc/refman/8.0...rd-replication

[stevenfoo]

Hi,

I have managed to fix the 1 part of the issue in mysql 5.7.25, that is the conversion from utf8mb3 to utf8mb4.
Manage to convert all the database/table/columns that have the issue.

The following 3 are the ones left.

Below cannot be change since it is a standard Zabbix naming tables.
Any idea what is Zabbix going to address this for mysql 8.0.x ??


2) Usage of db objects with names conflicting with reserved keywords in 8.0
Warning: The following objects have names that conflict with reserved
keywords that are new to 8.0. Ensure queries sent by your applications use
`quotes` when referring to them or they will result in errors.
More information:
https://dev.mysql.com/doc/refman/en/keywords.html

zabbix.groups - Table name
zabbix.functions.function - Column name


The other 2 are mysql setup for 8.0.x.
What are the Zabbix workround for their customer?


12) Removed system variables for error logging to the system log configuration
ERROR: This check requires server to be at minimum at 8.0.11 version

More information:
https://dev.mysql.com/doc/relnotes/m...8-0-13-logging


15) New default authentication plugin considerations
Warning: The new default authentication plugin 'caching_sha2_password' offers
more secure password hashing than previously used 'mysql_native_password'
(and consequent improved client connection authentication). However, it also
has compatibility implications that may affect existing MySQL installations.
If your MySQL installation must serve pre-8.0 clients and you encounter
compatibility issues after upgrading, the simplest way to address those
issues is to reconfigure the server to revert to the previous default
authentication plugin (mysql_native_password). For example, use these lines
in the server option file:

[mysqld]
default_authentication_plugin=mysql_native_passwor d

However, the setting should be viewed as temporary, not as a long term or
permanent solution, because it causes new accounts created with the setting
in effect to forego the improved authentication security.
If you are using replication please take time to understand how the
authentication plugin changes may impact you.
More information:

https://dev.mysql.com/doc/refman/8.0...ibility-issues
https://dev.mysql.com/doc/refman/8.0...rd-replication

[dimir]

The groups and function thingie was fixed in 4.0.0 (https://support.zabbix.com/browse/ZBXNEXT-4569). You must be using something else than 4.0.4 .
As to the charset, you must use what we advise:

create database zabbix character set utf8 collate utf8_bin;

on https://zabbix.com/download .

[stevenfoo]

The groups and function thingie was fixed in 4.0.0 (https://support.zabbix.com/browse/ZBXNEXT-4569). You must be using something else than 4.0.4 .
As to the charset, you must use what we advise:

create database zabbix character set utf8 collate utf8_bin;

on https://zabbix.com/download .

Thanks for the information. After I upgrade from 3.4.7 to 4.0.4 and rerun mysqlsh -u root -p -e "util.checkForServerUpgrade();" the warning is now fix.

We will now try to upgrade to mysql 8.0.15 from mysql 5.7.25.

From the preupgrade check, the following warning still appear.

12) Removed system variables for error logging to the system log configuration
ERROR: This check requires server to be at minimum at 8.0.11 version

More information:
https://dev.mysql.com/doc/relnotes/m...8-0-13-logging

15) New default authentication plugin considerations
Warning: The new default authentication plugin 'caching_sha2_password' offers
more secure password hashing than previously used 'mysql_native_password'
(and consequent improved client connection authentication). However, it also
has compatibility implications that may affect existing MySQL installations.
If your MySQL installation must serve pre-8.0 clients and you encounter
compatibility issues after upgrading, the simplest way to address those
issues is to reconfigure the server to revert to the previous default
authentication plugin (mysql_native_password). For example, use these lines
in the server option file:

[mysqld]
default_authentication_plugin=mysql_native_passwor d

However, the setting should be viewed as temporary, not as a long term or
permanent solution, because it causes new accounts created with the setting
in effect to forego the improved authentication security.
If you are using replication please take time to understand how the
authentication plugin changes may impact you.
More information:
https://dev.mysql.com/doc/refman/8.0...ibility-issues
https://dev.mysql.com/doc/refman/8.0...rd-replication

[stevenfoo]

Finally able to upgrade from to mysql 8.0.15 from mysql 5.7.25.
Remember to run after the upgrade.

mysql_upgrade -u root -p

systemctl stop mysqld
systemctl start mysqld