はじめに
いつごろからか（調べたところv 4.4.6からだそうです）Zabbixではデータベースの文字コード(utf8)と照合順序(utf8_bin)で設定されているかどうか確認し、違うようだとlogとダッシュボードに警告を表示するようになりました。
結局5.0.2まで使ってきて無視していたわけですが、ほったらかしは拙いということ、ダッシュボード上で煩わしく感じてきたので対応することにしました。
現象
Zabbixデータベースのデフォルト文字コードが正しくありません: "UTF8"ではなく"utf8mb4"が設定されています。
※ダッシュボードのシステム情報にも表示されていたのですがスクリーンショットの取得を忘れていました。
対象になるテーブルは以下の通り。
acknowledges, events, group_discovery, expressions, autoreg_host, proxy_autoreg_host, usrgrp, drules, httpstep_field, group_prototype, hosts, slides, items, valuemaps, operations, history_text, lld_macro_path, event_tag, sysmaps, users, profiles, module, task_remote_command_result, sysmap_element_url, images, opcommand, lld_override_condition, config_autoreg_tls, scripts, task_remote_command, lld_override, history_log, config, lld_override_opperiod, lld_override_ophistory, application_prototype, trigger_tag, opconditions, opmessage, media_type_param, alerts, media_type, graphs, auditlog, host_tag, icon_map, graphs_items, lld_override_operation, graph_theme, regexps, httptest, maintenances, sessions, proxy_history, slideshows, correlation, item_preproc, history_str, auditlog_details, corr_condition_tagpair, task_result, dservices, proxy_dhistory, problem_tag, widget_field, interface, actions, globalmacro, functions, dchecks, sysmaps_link_triggers, sysmaps_links, mappings, dashboard, ids, lld_override_optrends, triggers, host_discovery, media_type_message, corr_condition_tag, problem, item_condition, screens, media, maintenance_tag, host_inventory, application_discovery, services, sysmaps_elements, interface_snmp, httptest_field, corr_condition_tagvalue, task_data, httpstep, sysmap_url, services_times, applications, icon_mapping, conditions, widget, hstgrp, tag_filter, lld_override_optag, sysmap_shape, screens_items, item_rtdata, item_discovery, hostmacro, housekeeper
MariaDBの状況
MariaDB [(none)]> select *
-> from information_schema.schemata
-> where schema_name = 'zabbix';
+--------------+-------------+----------------------------+------------------------+----------+----------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT |
+--------------+-------------+----------------------------+------------------------+----------+----------------+
| def | zabbix | utf8mb4 | utf8mb4_general_ci | NULL | |
+--------------+-------------+----------------------------+------------------------+----------+----------------+
1 row in set (0.001 sec)
MariaDB [(none)]>
MariaDB [(none)]> select *
-> from information_schema.schemata
-> where schema_name = 'zabbix';
+--------------+-------------+----------------------------+------------------------+----------+----------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT |
+--------------+-------------+----------------------------+------------------------+----------+----------------+
| def | zabbix | utf8mb4 | utf8mb4_general_ci | NULL | |
+--------------+-------------+----------------------------+------------------------+----------+----------------+
1 row in set (0.001 sec)
MariaDB [(none)]>
最近のutf8mb4が良いみたいな記事をよく見ていたのでutf8mb4を設定していたのですが、やはりデータベース設計はちゃんとしないとダメなんでしょうね。しかもこれが拙いのは照合順序がgeneral_ciなので_binでもないので、よくこれまで大きなトラブルがなかったな、と（気が付いていない可能性はあります）。
対応
my.cnfの修正
Webの情報であるのはデータベースを作成しなおす、ダンプしたファイルに対して修正を加える、というものでした。実験用とはいえダウンタイムは作りたくなかったのでオンライン修正してみました（推奨されない作業だと思います）。
まずはMariaDBの設定ファイルの文字コードを修正して再起動してみます。これはTwitterのやり取りで再起動後にALTER TABLEが実行されることを期待するものです。
collation-server = utf8_bin character-set-server = utf8
で結果を確認しても変わらず。
MariaDB [zabbix]> SELECT *
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA="zabbix"
-> AND TABLE_TYPE="BASE TABLE";
+---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------------------------------------------+---------------+------------------+-----------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | MAX_INDEX_LENGTH | TEMPORARY |
+---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------------------------------------------+---------------+------------------+-----------+
| def | zabbix | maintenances_hosts | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-05-20 16:51:13 | NULL | NULL
| utf8_general_ci | NULL | | | 0 | N |
| def | zabbix | acknowledges | BASE TABLE | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 49152 | 0 | NULL | 2020-05-20 16:51:13 | NULL | NULL
| utf8_general_ci | NULL | | |
データベースの既定値の修正
ZABBIX システム情報のエラーが解決できません
### はじめに ZABBIXのダッシュボードにシステム情報を表示しています。 つい最近スクロールバーに気づきスクロールしてみると、赤字のメッセージが表示されていました。 ### 動作環境
teratail.com
MariaDB [(none)]> alter database zabbix character set utf8 collate utf8_bin; Query OK, 1 row affected (0.002 sec) MariaDB [(none)]> select * from information_schema.schemata where schema_name = 'zabbix'; +--------------+-------------+----------------------------+------------------------+----------+----------------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | SCHEMA_COMMENT | +--------------+-------------+----------------------------+------------------------+----------+----------------+ | def | zabbix | utf8 | utf8_bin | NULL | | +--------------+-------------+----------------------------+------------------------+----------+----------------+ 1 row in set (0.000 sec) MariaDB [(none)]>
データベースは修正されたけど結果として各テーブルには反映されるわけでもないし、設定値が乖離しているのでトラブルにしかならない、ということで続く。
各テーブルの修正
データベースを普段ほとんど触らないので知らなかったのですが、ALTER TABLEで全体指定して実行する方法って、スクリプトを組むかテキストエディタなどで一旦全部の構文を作成して手動で実行する、ぐらいと理解しています（テーブルへの変更について不用意に全体に波及させないように、という意味だと理解しています。
が、やはり実行させたいという人はいるみたいで以下の情報を見つけました。
How to convert all tables in database to one collation?
I'm getting error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='" I tried changing both tables manuall...
stackoverflow.com
You need to execute a alter table statement for each table. The statement would follow this form:
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]Now to get all the tables in the database you would need to execute the following query:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDataBaseName"
AND TABLE_TYPE="BASE TABLE";So now let MySQL write the code for you:
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDatabaseName"
AND TABLE_TYPE="BASE TABLE";You can copy the results and execute them. I have not tested the syntax but you should be able to figure out the rest. Think of it as a little exercise.
https://stackoverflow.com/a/10860122
最初に現在のテーブルの状態を確認する。（ごめんなさい、実行前のログを取っておくのを忘れてました。）
MariaDB [zabbix]> SELECT *
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA="zabbix"
-> AND TABLE_TYPE="BASE TABLE";
+---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------------------------------------------+---------------+------------------+-----------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | MAX_INDEX_LENGTH | TEMPORARY |
+---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------------------------------------------+---------------+------------------+-----------+
| def | zabbix | maintenances_hosts | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-05-20 16:51:13 | NULL | NULL
| utf8_general_ci | NULL | | | 0 | N |
| def | zabbix | acknowledges | BASE TABLE | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 49152 | 0 | NULL | 2020-08-05 18:51:48 | NULL | NULL
| utf8_bin | NULL | | | 0 | N |
| def | zabbix | events | BASE TABLE | InnoDB | 10 | Compressed | 2815673 | 47 | 133160960 | 0 | 94044160 | 3145728 | NULL | 2020-08-05 18:52:25 | 2020-08-05 18:59:34 | NULL
| utf8_bin | NULL | row_format=COMPRESSED key_block_size=8 | | 0 | N |
| def | zabbix | task | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-05-20 16:51:13 | NULL | NULL
| utf8_general_ci | NULL | | | 0 | N |
テーブルすべてを抽出しALTER TABLE構文を組み立て実行すべきSQLを生成する。（一部抜粋）
MariaDB [zabbix]> SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME, " CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;") AS ExecuteTheString
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA="zabbix"
-> AND TABLE_TYPE="BASE TABLE";
+-----------------------------------------------------------------------------------------------+
| ExecuteTheString |
+-----------------------------------------------------------------------------------------------+
| ALTER TABLE zabbix.maintenances_hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.acknowledges CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.events CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.task CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.dashboard_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.lld_override_optemplate CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.task_check_now CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.graph_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.sysmap_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.maintenances_groups CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.group_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.expressions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.dhosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.task_acknowledge CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.proxy_autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
| ALTER TABLE zabbix.usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; |
結果を確認する。（一部抜粋）
MariaDB [zabbix]> SELECT *
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA="zabbix"
-> AND TABLE_TYPE="BASE TABLE";
+---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------------------------------------------+---------------+------------------+-----------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | MAX_INDEX_LENGTH | TEMPORARY |
+---------------+--------------+----------------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------------------------------------------+---------------+------------------+-----------+
| def | zabbix | maintenances_hosts | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-08-05 21:40:26 | NULL | NULL
| utf8_bin | NULL | | | 0 | N |
| def | zabbix | acknowledges | BASE TABLE | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 49152 | 0 | NULL | 2020-08-05 21:40:26 | NULL | NULL
| utf8_bin | NULL | | | 0 | N |
| def | zabbix | events | BASE TABLE | InnoDB | 10 | Compressed | 2815650 | 47 | 133160960 | 0 | 94044160 | 3145728 | NULL | 2020-08-05 21:41:05 | NULL | NULL
| utf8_bin | NULL | row_format=COMPRESSED key_block_size=8 | | 0 | N |
| def | zabbix | task | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-08-05 21:41:05 | NULL | NULL
| utf8_bin | NULL | | | 0 | N |
| def | zabbix | dashboard_user | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-08-05 21:41:05 | NULL | NULL
| utf8_bin | NULL | | | 0 | N |
| def | zabbix | lld_override_optemplate | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 32768 | 0 | NULL | 2020-08-05 21:41:05 | NULL | NULL
| utf8_bin | NULL | `PAGE_COMPRESSED`='ON' | | 0 | N |
| def | zabbix | task_check_now | BASE TABLE | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2020-08-05 21:41:05 | NULL | NULL
| utf8_bin | NULL | | | 0 | N |
| def | zabbix | graph_discovery | BASE TABLE | InnoDB | 10 | Dynamic | 2935 | 55 | 163840 | 0 | 81920 | 0 | NULL | 2020-08-05 21:41:05 | 2020-08-05 21:42:36 | NULL
TABLE_COLLATION がutf8_binになっていることが確認できました。一応設定の置換が出来たようです。10分ほど経過するとダッシュボードのシステム情報からも今回のエラーメッセージが消えたことを確認できました。
ちなみ
@atanaka7 さんによると通常の手段としては今回のエラーメッセージを抑止する方法は用意されていないそうです（ソースコードの改変が必要ではないかとのこと）。まぁ正しい設定にしてもらうための警告だと思っているので正しいかと思います。
公式のsupportより
[ZBX-17357] "Unsupported charset or collation for tables" warning displayed on System information - ZABBIX SUPPORT
support.zabbix.com
見つけたTwitterのやりとり
参考
Zabbix 4.4.6でUnsupported charset or collation for tablesと表示される | ITログ
Zabbix 2.4くらいの時からZabbixを使い続けているのですが、時々新しいバージョンへアップグレードし…
www2.filewo.net
データベース名設定時の「utf8 / utf8_general_ci」とは？ / 文字セット（文字コード名）と照合順位のまとめ
以前書いた記事「【phpMyAdmin】MySQLで初めてのデータベース作成」では、phpMyAdmin画面からデータベース名を設定する時に、「utf8_general_ci」という照合順位を設定しました。もちろん、コマンドプロンプトからMySQLのデータベースの作成をする時にも、文字セットに「utf8」、照合順位に「...
lovee7.blog.fc2.com
文字セットと照合順序
MySQL ではデータベース、テーブル、カラムのそれぞれに対して文字セットと照合順序を設定することができます。ここでは MySQL における照合順序に関する簡単な説明と文字セット毎に指定可能な照合順序の一覧を確認する方法について解説します。
www.javadrive.jp
