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をアップデート完了後用

  • sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
 
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を実行する

  • sql
1
 
ALTER DATABASE zabbix DEFAULT COLLATE utf8_bin;

対応完了の確認方法

  • sql
1
 
SELECT @@character_set_database, @@collation_database;

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

  • sql
1
2
3
4
5
6
7
 
SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_bin             |
+--------------------------+----------------------+
1 row in set (0.00 sec)

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


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

原因

double型の指定の問題の模様

対処法

  1. パッチを適用する

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

  2. /etc/zabbix/web/zabbix.conf.phpの修正 $DB['DOUBLE_IEEE754'] =false;を$DB['DOUBLE_IEEE754'] = true;に修正

  3. Webサーバー再起動

パッチ適用前

  • sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
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  |       |
+-----------+---------------------+------+-----+---------+-------+

パッチ適用後

  • sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
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にアップデートするのは結構めんどくさいので計画する場合には慎重に