При обновлении Zabbix с версии 4.0 на 5.0 вылетает сообщение вот такого вот вида:
Unsupported charset or collation for tables: acknowledges, actions, alerts, application_discovery, application_prototype, applications, auditlog, auditlog_details, autoreg_host, conditions, config, corr_condition_tag, corr_condition_tagpair, corr_condition_tagvalue, correlation, dchecks, drules, dservices, event_tag, events, expressions, functions, globalmacro, graphs, graphs_items, group_discovery, group_prototype, history_log, history_str, history_text, host_discovery, hostmacro, hosts, housekeeper, hstgrp, httpstep, httptest, icon_map, icon_mapping, ids, images, interface, item_condition, item_discovery, items, maintenances, mappings, media, media_type, opcommand, opconditions, operations, opmessage, problem, problem_tag, profiles, proxy_autoreg_host, proxy_dhistory, proxy_history, regexps, screens, screens_items, scripts, services, services_times, sessions, slides, slideshows, sysmap_element_url, sysmap_url, sysmaps, sysmaps_elements, sysmaps_link_triggers, sysmaps_links, trigger_tag, triggers, users, usrgrp, valuemaps.
Лечится следующим способом:
1. Выполняем это:
SELECT CONCAT('ALTER TABLE ',
table_schema,
'.',
table_name,
' COLLATE=utf8_bin;') AS cmd
FROM (SELECT table_schema,
table_name,
table_collation
FROM information_schema.tables t
WHERE table_schema = 'zabbix'
AND table_collation != 'utf8_bin') AS tables
ORDER BY cmd;
Получаем на выходе список команд для всех таблиц;
+-----------------------------------------------------------------+
| cmd |
+-----------------------------------------------------------------+
| ALTER TABLE zabbix.acknowledges COLLATE=utf8_bin; |
| ALTER TABLE zabbix.actions COLLATE=utf8_bin; |
| ALTER TABLE zabbix.alerts COLLATE=utf8_bin; |
| ALTER TABLE zabbix.applications COLLATE=utf8_bin; |
| ALTER TABLE zabbix.application_discovery COLLATE=utf8_bin; |
| ALTER TABLE zabbix.application_prototype COLLATE=utf8_bin; |
| ALTER TABLE zabbix.application_template COLLATE=utf8_bin; |
----skip----
| ALTER TABLE zabbix.users_groups COLLATE=utf8_bin; |
| ALTER TABLE zabbix.usrgrp COLLATE=utf8_bin; |
| ALTER TABLE zabbix.valuemaps COLLATE=utf8_bin; |
| ALTER TABLE zabbix.widget COLLATE=utf8_bin; |
| ALTER TABLE zabbix.widget_field COLLATE=utf8_bin; |
+-----------------------------------------------------------------+
2. Выполняем вот это:
SELECT CONCAT('ALTER TABLE ',
table_schema,
'.',
table_name,
' MODIFY COLUMN ',
column_name,
' ',
column_type,
' CHARACTER SET utf8 COLLATE utf8_bin',
defaultval,
nullable,
';') AS cmd
FROM (SELECT table_schema,
table_name,
column_name,
collation_name,
character_set_name,
column_type,
column_default,
CASE
WHEN column_default IS NULL THEN ''
ELSE CONCAT(' DEFAULT ''', column_default, ''' ')
END AS defaultval,
CASE
WHEN is_nullable = 'NO' THEN ' NOT NULL '
ELSE ''
END AS nullable
FROM information_schema.columns
WHERE table_schema = 'zabbix'
AND ( collation_name != 'utf8_bin'
OR character_set_name != 'utf8' )) AS cols
ORDER BY cmd;
Получаем список команд для всех columns;
+----------------------------------------------------------------------------------------------------------------------------------------------+
| cmd |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| ALTER TABLE zabbix.acknowledges MODIFY COLUMN message varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.actions MODIFY COLUMN esc_period varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '1h' NOT NULL ; |
| ALTER TABLE zabbix.actions MODIFY COLUMN formula varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.actions MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.alerts MODIFY COLUMN error varchar(2048) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.alerts MODIFY COLUMN message text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ; |
| ALTER TABLE zabbix.alerts MODIFY COLUMN parameters text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ; |
| ALTER TABLE zabbix.alerts MODIFY COLUMN sendto varchar(1024) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.alerts MODIFY COLUMN subject varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.applications MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
---- skip -----
| ALTER TABLE zabbix.users MODIFY COLUMN url varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.usrgrp MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.valuemaps MODIFY COLUMN name varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.widget MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.widget MODIFY COLUMN type varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.widget_field MODIFY COLUMN name varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
| ALTER TABLE zabbix.widget_field MODIFY COLUMN value_str varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' NOT NULL ; |
+----------------------------------------------------------------------------------------------------------------------------------------------+
320 rows in set (0.01 sec)
3. Заходим в MySQL под УЗ zabbix и выполняем ВСЕ команды полученные на первых двух этапах. Например:
4. После выполнения испытываем радость =)
PS: стырено отсюда Сисодминиум