Press "Enter" to skip to content

zabbix报错:Unsupported charset or collation for tables

官方有issue说明  https://support.zabbix.com/browse/ZBX-17357

解决方法  https://www.zabbix.com/documentation/4.0/manual/appendix/install/db_charset_coll

主要原因是因为zabbix后台使用的MySQL字符集问题导致,字符排序需修改到utf8_bin

具体修改步骤:

1、先备份数据

一定要先备份数据!!!
一定要先备份数据!!!
一定要先备份数据!!!

2、关闭zabbix 服务

3、zabbix 数据库级别字符集排序修改

MariaDB [zabbix]> alter database zabbix  character set utf8 collate utf8_bin;

确认是否修改成功

MariaDB [zabbix]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_bin             |
+--------------------------+----------------------+

4、表数据级别字符集排序修改

注:alter table table_name character set utf8 collate utf8_bin;命令只是对表定义修改字符排序

alter table  table_name convert to  character set utf8 collate utf8_bin; 会对表定义,字段,现有数据全部修改 字符排序

zabbix官方提供了使用 utf8_bin的转换脚本,主要也是使用  convert to 命令转换 表定义,字段和现有数据

utf8_convert.sql 内容:

/* ChangeLog:
   2020.08.19 - initial release
   2020.09.04 - fixed syntax for running on MySQL
*/
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 utf8 COLLATE utf8_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 utf8 COLLATE utf8_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 utf8 COLLATE utf8_bin;
     */
     SELECT table_name AS sort1,
                   'B' AS sort2,
            CONCAT('ALTER TABLE ', table_schema, '.', table_name,
                   ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_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 ;

将转换SP导入DB

mysql -uroot -ppassword zabbix < utf8_convert.sql

执行SP转换字符集排序:

MariaDB [mysql]> use zabbix
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [zabbix]> set innodb_strict_mode = OFF;
Query OK, 0 rows affected (0.00 sec)

MariaDB [zabbix]>  SET @ZABBIX_DATABASE = 'zabbix';
Query OK, 0 rows affected (0.00 sec)

MariaDB [zabbix]> CALL zbx_convert_utf8();
Query OK, 0 rows affected, 1 warning (1 hours 53 min 17.85 sec)

MariaDB [zabbix]> set innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [zabbix]> drop procedure zbx_convert_utf8;
Query OK, 0 rows affected (0.04 sec)

操作完成后,可以查看修改后情况

MariaDB [zabbix]> select * from information_schema.tables where table_schema='zabbix';

5、开启zabbix服务


参考文档:http://blog.itpub.net/25583515/viewspace-2730846/

Be First to Comment

发表评论

电子邮件地址不会被公开。 必填项已用*标注