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:
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:
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.