[Zabbix] - Incorrect characterset for Zabbix database "latin1" instead "UTF8, UTF8MB3"

NGUYÊN NHÂN

Lỗi này thường gặp kết nối với Database sau khi tải xong Zabbix. Nguyên nhân là Zabbix chỉ sử dụng các character khuyến nghị như UTF8, UTF8MB3

mysql  Ver 14.14 Distrib 5.7.43, for Linux (x86_64)

zabbix_server (Zabbix) 4.0.47

PHP 5.4.16

GIẢI PHÁP

Chuyển đổi character

MYSQL>SET @ZABBIX_DATABASE = 'database name>';

MYSQL>alter database <database name> character SET utf8 COLLATE utf8_bin;

Kiểm tra character của database

MYSQL>select default_character_set_name from information_schema.SCHEMATA S WHERE schema_name ="<database name>";

Do lúc tạo các table cũng đã ăn với character khác. Cần đổi tất cả table.

script utf8_convert.sql (script chuyển đổi character tất cả table )

Chạy lệnh sau:

#$ mysql -u root -p <database name> < utf8_convert.sql

Nội dung script utf8_convert.sql ( mọi người tạo file rồi copy đoạn bên dưới vào và sửa lại database name nhé )

ALTER TABLE <database name>.alerts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.application_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.acknowledges CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.actions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.alerts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.application_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.application_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.applications CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.auditlog CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.auditlog_details CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.conditions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.config CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.corr_condition_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.corr_condition_tagpair CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.corr_condition_tagvalue CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.correlation CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.dashboard CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.dchecks CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.drules CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.dservices CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.event_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.events CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.expressions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.functions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.globalmacro CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.graph_theme CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.graphs CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.graphs_items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.group_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.group_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.history_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.history_str CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.history_text CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.host_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.host_inventory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.hostmacro CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.housekeeper CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.hstgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.httpstep CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.httpstep_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.httptest CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.httptest_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.icon_map CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.icon_mapping CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.ids CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.images CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.interface CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.item_condition CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.item_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.item_preproc CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.maintenance_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.maintenances CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.mappings CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.media CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.media_type CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.opcommand CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.opconditions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.operations CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.opmessage CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.problem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.problem_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.profiles CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.proxy_autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.proxy_dhistory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.proxy_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.regexps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.screens CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.screens_items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.scripts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.services CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.services_times CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sessions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.slides CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.slideshows CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sysmap_element_url CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sysmap_shape CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sysmap_url CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sysmaps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sysmaps_elements CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sysmaps_link_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.sysmaps_links CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.tag_filter CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.task_remote_command CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.task_remote_command_result CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.trigger_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.users CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.valuemaps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.widget CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.widget_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.tag_filter CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.task_remote_command CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.task_remote_command_result CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.trigger_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.users CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.valuemaps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.widget CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE <database name>.widget_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;