Zabbix升级版本后界面及告警乱码问题

应用	旧版本	新版本
Zabbix Server	5.0	6.0
Mysql	5.7.30	8.0.37

参考文档：https://www.zabbix.com/documentation/6.0/zh/manual/appendix/install/db_charset_coll

问题

数据库及Zabbix升级完成后，出现部分中文乱码，其中包括告警、主机名等地方的中文乱码。

经过排查发现Zabbix 5.0在Mysql 5.7中使用的是utf8mb3的字符集，但是在mysql 8.0中该字符集已被启用，并使用了utf8mb4的字符集。

解决方案

1. 查看现有数据库字符集及字符序。

SELECT @@character_set_database, @@collation_database;

如果看到此处字符集已经是utf8mb4 则可能是其他问题

2. 停止 Zabbix 服务
3. 数据库进行备份
4. 使用如下命令修复数据库的字符集及字符序:

alter database zabbix character set utf8mb4 collate utf8mb4_bin;

再次查看可以看到字符集已经被修改

5. 导入下面的 sql 来修复每张表中各个列的字符集

/* ChangeLog:
   2020.08.19 - initial release
   2020.09.04 - fixed syntax for running on MySQL
   2022.01.13 - updated the script to use standard UTF8 instead of utf8mb3
*/

DELIMITER $$
CREATE PROCEDURE zbx_convert_utf8 (
)

BEGIN
	declare cmd varchar(255) default "";
	declare finished integer default 0;

	declare cur_command cursor for 
		SELECT command
		FROM
		    (/* This 'select' statement deals with 'text' type columns to prevent
		        their automatic conversion into 'mediumtext' type.
		        The goal is to produce statements like
		         ALTER TABLE zabbix.hosts MODIFY COLUMN description text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null;
		     */
		     SELECT table_name AS sort1,
		                   'A' AS sort2,
		            CONCAT('ALTER TABLE ', table_schema, '.', table_name,
		                   ' MODIFY COLUMN ', column_name, ' ', column_type,
		                   ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin',
		                case
		                    when column_default is null then ''
		                    else concat(' default ', column_default, ' ')
		                end,
		                case
		                    when is_nullable = 'no' then ' not null '
		                    else ''
		                end,
		            ';') AS command
		        FROM information_schema.columns
		        WHERE table_schema = @ZABBIX_DATABASE        
		           AND column_type = 'text'
		    UNION
		     /* This 'select' statement deals with setting character set and collation for
		        each table and converting varchar fields on a per-table basis.
		        It is necessary to process all tables (even those with numeric-only columns)
		        otherwise in future Zabbix upgrades text (e.g. varchar) columns may be added
		        to these tables or numeric columns can be turned into text ones and
		        the old character set/collation can reappear again.
		        The goal is to produce statements like
		         ALTER TABLE zabbix.hosts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
		     */
		     SELECT table_name AS sort1,
		                   'B' AS sort2,
		            CONCAT('ALTER TABLE ', table_schema, '.', table_name,
		                   ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') AS command
		        FROM information_schema.tables
		        WHERE table_schema = @ZABBIX_DATABASE) s
		/* Sorting is important: 'MODIFY COLUMN' statements should precede 'CONVERT TO' ones
		   for each table. */
		ORDER BY sort1, sort2;
	
	declare continue handler for not found set finished = 1;

	open cur_command;
	cmd_loop: loop
		fetch cur_command into cmd;
		if finished = 1 then
			leave cmd_loop;
		end if;
		SET @value = cmd;
		PREPARE stmt FROM @value;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	end loop cmd_loop;
	close cur_command;

END$$

DELIMITER ;

6. 执行以下命令

SET @ZABBIX_DATABASE = 'zabbix';
set innodb_strict_mode = OFF; # mariadb需要执行     
CALL zbx_convert_utf8();
set innodb_strict_mode = ON;  # mariadb需要执行    
drop procedure zbx_convert_utf8;

如果没有错误信息 ，则表示成功

8. 启动zabbix服务，等待字符集自动修改过来即可