2 Repairing Zabbix database character set and collation

MySQL/MariaDB

Historically, MySQL and derivatives used ‘utf8’ as an alias for utf8mb3 - MySQL’s own 3-byte implementation of the standard UTF8, which is 4-byte. Starting from MySQL 8.0.28 and MariaDB 10.6.1, ‘utf8mb3’ character set is deprecated and at some point its support will be dropped while ‘utf8’ will become a reference to ‘utf8mb4’. Since Zabbix 6.0, ‘utf8mb4’ is supported. To avoid future problems, it is highly recommended to use ‘utf8mb4’. Another advantage of switching to ‘utf8mb4’ is support of supplementary Unicode characters.

1. Check the database character set and collation.

For example:

  1. mysql> SELECT @@character_set_database, @@collation_database;
  2.        +--------------------------+----------------------+
  3.        | @@character_set_database | @@collation_database |
  4.        +--------------------------+----------------------+
  5.        | latin2                   | latin2 _general_ci   |
  6.        +--------------------------+----------------------+

Or:

  1. mysql> SELECT @@character_set_database, @@collation_database;
  2.        +--------------------------+----------------------+
  3.        | @@character_set_database | @@collation_database |
  4.        +--------------------------+----------------------+
  5.        | utf8                     | utf8_bin             |
  6.        +--------------------------+----------------------+

As we see, the character set here is not ‘utf8mb4’ and collation is not ‘utf8mb4_bin’, so we need to fix them.

2. Stop Zabbix.

3. Create a backup copy of the database!

4. Fix the character set and collation on database level:

  1. alter database <your DB name> character set utf8mb4 collate utf8mb4_bin;

Fixed values:

  1. mysql> SELECT @@character_set_database, @@collation_database;
  2.        +--------------------------+----------------------+
  3.        | @@character_set_database | @@collation_database |
  4.        +--------------------------+----------------------+
  5.        | utf8mb4                  | utf8mb4_bin          |
  6.        +--------------------------+----------------------+

5. Load the script to fix character set and collation on table and column level:

  1. mysql <your DB name> < utf8mb4_convert.sql

6. Execute the script:

  1.                SET @ZABBIX_DATABASE = '<your DB name>';
  2.        If MariaDB →  set innodb_strict_mode = OFF;        
  3.                       CALL zbx_convert_utf8();
  4.        If MariaDB →  set innodb_strict_mode = ON;   
  5.                       drop procedure zbx_convert_utf8;

Please note that ‘utf8mb4’ is expected to consume slightly more disk space.

7. If no errors - you may want to create a database backup copy with the fixed database.

8. Start Zabbix.