[自分用メモ][zabbix][MariaDB] 気が付いたらエラー表示されるようになっていた文字コードと照合順序について対処してみた。

2020/08/07 10:59zabbixmariadb,zabbix,文字コード,照合順序

はじめに

いつごろからか(調べたところv 4.4.6からだそうです)Zabbixではデータベースの文字コード(utf8)と照合順序(utf8_bin)で設定されているかどうか確認し、違うようだとlogとダッシュボードに警告を表示するようになりました。

結局5.0.2まで使ってきて無視していたわけですが、ほったらかしは拙いということ、ダッシュボード上で煩わしく感じてきたので対応することにしました。

CAUTION

この作業手順は個人的なメモであり、作業の正確性や安全性は未確認です。データの破損、障害が発生しても一切保証できません。

現象

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)]> 

最近の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 |                                                    |               |    

データベースの既定値の修正

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で全体指定して実行する方法って、スクリプトを組むかテキストエディタなどで一旦全部の構文を作成して手動で実行する、ぐらいと理解しています(テーブルへの変更について不用意に全体に波及させないように、という意味だと理解しています。

が、やはり実行させたいという人はいるみたいで以下の情報を見つけました。

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_COLLATIONutf8_binになっていることが確認できました。一応設定の置換が出来たようです。10分ほど経過するとダッシュボードのシステム情報からも今回のエラーメッセージが消えたことを確認できました。

ちなみ

@atanaka7 さんによると通常の手段としては今回のエラーメッセージを抑止する方法は用意されていないそうです(ソースコードの改変が必要ではないかとのこと)。まぁ正しい設定にしてもらうための警告だと思っているので正しいかと思います。

公式のsupportより

見つけたTwitterのやりとり

参考

この記事を書いた人
kometchtech

kometchtech
うつ病を患いながら、IT業界の末席にいるおっさんエンジニア。科学計算をしたことがないのに、HPC分野にお邪魔している。興味のある分野で学習したことをblogにまとめつつ、うつ病の経過症状のメモも置いておく日々。じつはRouterboard User Group JPの中の人でもある。
Amazon欲しいものリスト / Arm板を恵んでくれる人募集中

Buy Me A Coffee

2020/08/07 10:59zabbixmariadb,zabbix,文字コード,照合順序