2 Repairing Zabbix database character set and collation

MySQL/MariaDB

1. Check the database character set and collation.

For example:

mysql> SELECT @@character_set_database, @@collation_database;
       +--------------------------+----------------------+
       | @@character_set_database | @@collation_database |
       +--------------------------+----------------------+
       | utf8mb4                  | utf8mb4_general_ci   |
       +--------------------------+----------------------+

As we see, the character set here is not ‘utf8’ and collation is not ‘utf8_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:

alter database <your DB name> character set utf8 collate utf8_bin;

Fixed values:

mysql> SELECT @@character_set_database, @@collation_database;
       +--------------------------+----------------------+
       | @@character_set_database | @@collation_database |
       +--------------------------+----------------------+
       | utf8                     | utf8_bin             |
       +--------------------------+----------------------+

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

mysql <your DB name> < utf8_convert.sql

6. Execute the script:

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

Note that data encoding will be changed on disk. For example, when converting characters like Æ, Ñ, Ö from ‘latin1’ to ‘utf8’ they will go from 1 byte to 2 bytes. Thus the repaired database may require more space than before.

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

8. Start Zabbix.