2 Repairing Zabbix database character set and collation

MySQL/MariaDB

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. | utf8mb4 | utf8mb4_general_ci |
  6. +--------------------------+----------------------+

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:

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

Fixed values:

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

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

  1. mysql <your DB name> < utf8_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;

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.