Zabbix4.0から5.0にいきあたりばったりアップデートしたらハマったので対処法のメモ
なお、Zabbix5はCentOS6系では動かない。
7系で動かしたい場合はSCLを使う方法がある。
Zabbix 5.0をCentOS 7にインストール(SCL編)

環境

CentOS7
MariaDB
Zabbix5.0(4系からアップデートした環境)

テーブルの照合順序がサポートされていない

Zabbix Zabbix-Serverを起動すると以下のログが表示される場合の対処方法

character set name or collation name that is not supported by Zabbix found in 421 column(s) of database "zabbix"
only character set "utf8" and collation "utf8_bin" should be used in database

対応方法

ZabbixのDBに対して以下のSQLを実行する。 Zabbix5.0にDBをアップデート完了後用


ALTER TABLE acknowledges CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE actions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE alerts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE application_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE application_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE application_template CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE applications CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE auditlog CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE auditlog_details CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE conditions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE config CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE config_autoreg_tls CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE corr_condition CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE corr_condition_group CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE corr_condition_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE corr_condition_tagpair CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE corr_condition_tagvalue CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE corr_operation CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE correlation CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE dashboard CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE dashboard_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE dashboard_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE dbversion CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE dchecks CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE dhosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE drules CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE dservices CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE escalations CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE event_recovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE event_suppress CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE event_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE events CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE expressions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE functions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE globalmacro CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE globalvars CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE graph_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE graph_theme CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE graphs CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE graphs_items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE group_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE group_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE history CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE history_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE history_str CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE history_text CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE history_uint CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE host_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE host_inventory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE host_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE hostmacro CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE hosts_groups CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE hosts_templates CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE housekeeper CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE hstgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE httpstep CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE httpstep_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE httpstepitem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE httptest CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE httptest_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE httptestitem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE icon_map CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE icon_mapping CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE ids CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE images CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE interface CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE interface_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE interface_snmp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE item_application_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE item_condition CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE item_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE item_preproc CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE item_rtdata CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE items_applications CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_macro_path CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_condition CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_opdiscover CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_operation CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_ophistory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_opinventory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_opperiod CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_opseverity CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_opstatus CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_optag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_optemplate CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE lld_override_optrends CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE maintenance_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE maintenances CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE maintenances_groups CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE maintenances_hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE maintenances_windows CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE mappings CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE media CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE media_type CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE media_type_message CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE media_type_param CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE module CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opcommand CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opcommand_grp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opcommand_hst CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opconditions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE operations CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opgroup CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opinventory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opmessage CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opmessage_grp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE opmessage_usr CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE optemplate CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE problem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE problem_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE profiles CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE proxy_autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE proxy_dhistory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE proxy_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE regexps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE rights CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE screen_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE screen_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE screens CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE screens_items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE scripts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE service_alarms CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE services CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE services_links CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE services_times CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sessions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE slides CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE slideshow_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE slideshow_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE slideshows CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmap_element_trigger CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmap_element_url CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmap_shape CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmap_url CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmap_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmap_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmaps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmaps_elements CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmaps_link_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE sysmaps_links CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE tag_filter CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task_acknowledge CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task_check_now CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task_close_problem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task_data CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task_remote_command CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task_remote_command_result CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE task_result CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE timeperiods CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE trends CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE trends_uint CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE trigger_depends CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE trigger_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE trigger_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE users CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE users_groups CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE valuemaps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE widget CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE widget_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

対応完了の確認方法

show table status from zabbix;を実行し、Collation値がすべて「utf8_bin」に変わっていれば対応完了

DBの照合順序がサポートされていない

以下のログが表示された時の対処

Zabbix supports only "utf8_bin" collation. Database "zabbix" has default collation "utf8_general_ci"

対応方法

以下のsqlを実行する

ALTER DATABASE zabbix DEFAULT COLLATE utf8_bin;

対応完了の確認方法

SELECT @@character_set_database, @@collation_database;

を実行し、以下のようになっている

SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_bin             |
+--------------------------+----------------------+
1 row in set (0.00 sec)

「データベースのヒストリテーブルをアップグレードしました:いいえ」

Zabbixのアップデートも完了して、起動したらシステム情報に謎の表示がされている場合の対処法

原因

double型の指定の問題の模様

対処法

パッチを適用する

wget https://git.zabbix.com/projects/ZBX/repos/zabbix/raw/database/mysql/double.sql
mysql -u root -p zabbix < double.sql

/etc/zabbix/web/zabbix.conf.phpの修正 $DB['DOUBLE_IEEE754'] =false;を$DB['DOUBLE_IEEE754'] = true;に修正
Webサーバー再起動

パッチ適用前

desc history;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | double(16,4)        | NO   |     | 0.0000  |       |
| ns     | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+

desc trends;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| itemid    | bigint(20) unsigned | NO   | PRI | NULL    |       |
| clock     | int(11)             | NO   | PRI | 0       |       |
| num       | int(11)             | NO   |     | 0       |       |
| value_min | double(16,4)        | NO   |     | 0.0000  |       |
| value_avg | double(16,4)        | NO   |     | 0.0000  |       |
| value_max | double(16,4)        | NO   |     | 0.0000  |       |
+-----------+---------------------+------+-----+---------+-------+

パッチ適用後

desc history;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| itemid | bigint(20) unsigned | NO   | MUL | NULL    |       |
| clock  | int(11)             | NO   |     | 0       |       |
| value  | double              | NO   |     | 0       |       |
| ns     | int(11)             | NO   |     | 0       |       |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

desc trends;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| itemid    | bigint(20) unsigned | NO   | PRI | NULL    |       |
| clock     | int(11)             | NO   | PRI | 0       |       |
| num       | int(11)             | NO   |     | 0       |       |
| value_min | double              | NO   |     | 0       |       |
| value_avg | double              | NO   |     | 0       |       |
| value_max | double              | NO   |     | 0       |       |
+-----------+---------------------+------+-----+---------+-------+
6 rows in set (0.001 sec)

まとめ

かなり大きく画面等が変わるのでもろもろ対応が必要となる。
CentOS7系でZabbix5にアップデートするのは結構めんどくさいので計画する場合には慎重に

環境#

テーブルの照合順序がサポートされていない#

対応方法#

対応完了の確認方法#

DBの照合順序がサポートされていない#

対応方法#

対応完了の確認方法#

「データベースのヒストリテーブルをアップグレードしました:いいえ」#

原因#

対処法#

パッチ適用前#

パッチ適用後#

まとめ#

環境

テーブルの照合順序がサポートされていない

対応方法

対応完了の確認方法

DBの照合順序がサポートされていない

対応方法

対応完了の確認方法

「データベースのヒストリテーブルをアップグレードしました:いいえ」

原因

対処法

パッチ適用前

パッチ適用後

まとめ