Zenn
🐬

MySQL Parameters のデータの作り方

2020/12/20に公開
MySQL
tech

MySQL Parameters のデータの作り方

これは MySQL Advent Calendar 2020 の 25日目の記事です。

MySQL Parameters というページのメンテをしてます。

MySQL Paramters は MySQL のバージョン間の差分を表示できるものです。
現在次のバージョンを比較できます。

  • 5.0.96
  • 5.1.72
  • 5.5.58〜62
  • 5.6.34〜50
  • 5.7.16〜32
  • 8.0.11〜22

比較できる値の種類は次のものです。

  • mysqld のコマンドラインパラメータ
  • mysql のコマンドラインパラメータ
  • システム変数
  • ステータス変数
  • キャラクタセット
  • コレーション
  • 権限
  • 関数/演算子
  • information_schema データベースのテーブル名とカラム名
  • performance_schema データベースのテーブル名とカラム名
  • エラーメッセージ

たとえばシステム変数の 8.0.* のバージョン間の差分は https://mysql-params.tmtms.net/variable/?vers=8.0.11,8.0.12,8.0.13,8.0.14,8.0.15,8.0.16,8.0.17,8.0.18,8.0.19,8.0.20,8.0.21,8.0.22&diff=true で表示できます。

黄色のセルはその左隣のセルの値と違いがあることを示しています。

元々 Vue.js の勉強のつもりで作ってて、だいたいそのままなのでフロントエンドは酷い作りです。作り直したい。

フロントエンドには触れたくないので、データをどのように作ってるのかを説明します。

mysqld のコマンドラインパラメータ

基本的には mysqld --no-defaults --user mysql --help -v の出力の後半からパラメータ名と値を拾ってます。

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}                             Value (after reading options)
------------------------------------------------------------ -------------
abort-slave-event-count                                      0
activate-all-roles-on-login                                  FALSE
admin-address                                                (No default value)
admin-port                                                   33062
admin-ssl                                                    TRUE
admin-ssl-ca                                                 (No default value)
admin-ssl-capath                                             (No default value)
admin-ssl-cert                                               (No default value)
admin-ssl-cipher                                             (No default value)
admin-ssl-crl                                                (No default value)
admin-ssl-crlpath                                            (No default value)
...

--plugin-load でできるだけ多くのプラグインを読み込んで値を表示してるので、 example ストレージエンジンのようなパラメータも表示されてます。

mysql のコマンドラインパラメータ

mysqld と同じように mysql --no-defaults --help -v の出力の後半を使用しています。

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
auto-rehash                       TRUE
auto-vertical-output              FALSE
bind-address                      (No default value)
binary-as-hex                     FALSE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
database                          (No default value)
default-character-set             auto
delimiter                         ;
...

システム変数

mysqld --help -v で出力されるパラメータと、mysqld 起動後に SHOW VARIABLES で表示される変数がビミョーに異なっているので、追加しました。

mysqld 起動後に全プラグインとコンポーネントを読み込んでから、SHOW GLOBAL VARIABLES の出力を使用しています。

activate_all_roles_on_login	OFF
admin_address	
admin_port	33062
admin_ssl_ca	
admin_ssl_capath	
admin_ssl_cert	
admin_ssl_cipher	
admin_ssl_crl	
admin_ssl_crlpath	
admin_ssl_key	
admin_tls_ciphersuites	
admin_tls_version	TLSv1,TLSv1.1,TLSv1.2,TLSv1.3
auto_generate_certs	ON
auto_increment_increment	1
auto_increment_offset	1
autocommit	ON
...

ステータス変数

SHOW GLOBAL STATUS の出力の1番目のカラムだけを使用しています。

Aborted_clients 0
Aborted_connects        0
Acl_cache_items_count   0
Audit_null_authorization_column 0
Audit_null_authorization_db     0
Audit_null_authorization_procedure      0
Audit_null_authorization_proxy  0
Audit_null_authorization_table  0
Audit_null_authorization_user   0
Audit_null_called       2222
Audit_null_command_end  8
Audit_null_command_start        9
Audit_null_connection_change_user       0
Audit_null_connection_connect   2
Audit_null_connection_disconnect        1
...

キャラクタセット

mysql --no-defaults -e 'SHOW CHARSET' の出力の1番目のカラムを使用しています。

Charset	Description	Default collation	Maxlen
armscii8	ARMSCII-8 Armenian	armscii8_general_ci	1
ascii	US ASCII	ascii_general_ci	1
big5	Big5 Traditional Chinese	big5_chinese_ci	2
binary	Binary pseudo charset	binary	1
cp1250	Windows Central European	cp1250_general_ci	1
cp1251	Windows Cyrillic	cp1251_general_ci	1
cp1256	Windows Arabic	cp1256_general_ci	1
cp1257	Windows Baltic	cp1257_general_ci	1
cp850	DOS West European	cp850_general_ci	1
cp852	DOS Central European	cp852_general_ci	1
cp866	DOS Russian	cp866_general_ci	1
...

コレーション

mysql --no-defaults -e 'SHOW COLLATION' の出力の1番目のカラムを使用しています。

Collation	Charset	Id	Default	Compiled	Sortlen	Pad_attribute
armscii8_bin	armscii8	64		Yes	1	PAD SPACE
armscii8_general_ci	armscii8	32	Yes	Yes	1	PAD SPACE
ascii_bin	ascii	65		Yes	1	PAD SPACE
ascii_general_ci	ascii	11	Yes	Yes	1	PAD SPACE
big5_bin	big5	84		Yes	1	PAD SPACE
big5_chinese_ci	big5	1	Yes	Yes	1	PAD SPACE
binary	binary	63	Yes	Yes	1	NO PAD
cp1250_bin	cp1250	66		Yes	1	PAD SPACE
cp1250_croatian_ci	cp1250	44		Yes	1	PAD SPACE
cp1250_czech_cs	cp1250	34		Yes	2	PAD SPACE
cp1250_general_ci	cp1250	26	Yes	Yes	1	PAD SPACE
cp1250_polish_ci	cp1250	99		Yes	1	PAD SPACE
cp1251_bin	cp1251	50		Yes	1	PAD SPACE
cp1251_bulgarian_ci	cp1251	14		Yes	1	PAD SPACE
...

権限

DESC mysql.user のカラム名(*_priv)から権限を得ています。

Field	Type	Null	Key	Default	Extra
Host	char(255)	NO	PRI		
User	char(32)	NO	PRI		
Select_priv	enum('N','Y')	NO		N	
Insert_priv	enum('N','Y')	NO		N	
Update_priv	enum('N','Y')	NO		N	
Delete_priv	enum('N','Y')	NO		N	
Create_priv	enum('N','Y')	NO		N	
Drop_priv	enum('N','Y')	NO		N	
Reload_priv	enum('N','Y')	NO		N	
...

カラム名と権限名がビミョーに異なるので(Create_tmp_table → CREATE TEMPORARY TABLES 等)補正したりしてます。

あと 8.0 では PROXY という権限があるんですが、これは mysql.user に含まれていないので、mysql.proxies_priv を見ています。

Field	Type	Null	Key	Default	Extra
Host	char(255)	NO	PRI		
User	char(32)	NO	PRI		
Proxied_host	char(255)	NO	PRI		
Proxied_user	char(32)	NO	PRI		
...

さらに 8.0 では動的権限(Dynamic Privileges)という(個人的には名前が良くないと思う)、プラグインやコンポーネントによって追加される権限があります。

これは ALL 権限をつけたユーザーを作って SHOW GRANTS FOR ユーザー名 で出力されるのでそれを使用しています。

Grants for test@%
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test`@`%` WITH GRANT OPTION
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test`@`%` WITH GRANT OPTION

実はこの出力にすべての権限が含まれてるので mysql.user を見なくても良さそうなもんなのですが、このような出力は 8.0 だけで、5.7 以下だと次のように出力されるので、これだけじゃわからないのでした。

Grants for test@%
GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION

関数/演算子

mysql で help Functions でカテゴリが表示され、さらにそのカテゴリ名を help すると関数名の一覧が表示されます。

mysql> help Functions;
You asked for help about help category: "Functions"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Aggregate Functions and Modifiers
   Bit Functions
   Cast Functions and Operators
   Comparison Operators
   Control Flow Functions
   Date and Time Functions
   Encryption Functions
   Enterprise Encryption Functions
...

mysql> help Aggregate Functions and Modifiers;
You asked for help about help category: "Aggregate Functions and Modifiers"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AVG
   BIT_AND
   BIT_OR
   BIT_XOR
   COUNT
   COUNT DISTINCT
   GROUP_CONCAT
   JSON_ARRAYAGG
   JSON_OBJECTAGG
   MAX
...

初めはこれを使ってたんですが、出力のパースが面倒だったので、今は mysql.help_category と mysql.help_topic テーブルから名前を得ています。

mysql> select help_category_id,name,parent_category_id from mysql.help_category;
+------------------+---------------------------------+--------------------+
| help_category_id | name                            | parent_category_id |
+------------------+---------------------------------+--------------------+
|                0 | Contents                        |                  0 |
|                1 | Help Metadata                   |                  0 |
|                2 | Data Types                      |                  0 |
|                3 | Administration                  |                  0 |
|                4 | Functions                       |                  0 |
...

mysql> select help_topic_id,name,help_category_id from mysql.help_topic;
+---------------+----------------------------+------------------+
| help_topic_id | name                       | help_category_id |
+---------------+----------------------------+------------------+
|             0 | HELP_DATE                  |                1 |
|             1 | HELP_VERSION               |                1 |
|             2 | AUTO_INCREMENT             |                2 |
|             3 | HELP COMMAND               |                3 |
|             4 | ASYMMETRIC_DECRYPT         |                5 |
|             5 | ASYMMETRIC_DERIVE          |                5 |
|             6 | ASYMMETRIC_ENCRYPT         |                5 |
|             7 | ASYMMETRIC_SIGN            |                5 |
...

この中から関数/演算子っぽいカテゴリ配下にある name を次のクエリで取り出しています。

select t.name from mysql.help_topic t join mysql.help_category c using (help_category_id)
  where c.name rlike "functions|operators|geometry (constructors|relations)|properties|mbr|wkt|wkb|xml|gtid"
    and c.name != "user-defined functions" and t.name not rlike "definition"
  order by t.name

information_schema データベースのテーブル名とカラム名

SELECT TABLE_NAME,COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA='information_schema' の出力を使用しています。

ADMINISTRABLE_ROLE_AUTHORIZATIONS	GRANTEE
ADMINISTRABLE_ROLE_AUTHORIZATIONS	GRANTEE_HOST
ADMINISTRABLE_ROLE_AUTHORIZATIONS	HOST
ADMINISTRABLE_ROLE_AUTHORIZATIONS	IS_DEFAULT
ADMINISTRABLE_ROLE_AUTHORIZATIONS	IS_GRANTABLE
ADMINISTRABLE_ROLE_AUTHORIZATIONS	IS_MANDATORY
ADMINISTRABLE_ROLE_AUTHORIZATIONS	ROLE_HOST
ADMINISTRABLE_ROLE_AUTHORIZATIONS	ROLE_NAME
ADMINISTRABLE_ROLE_AUTHORIZATIONS	USER
APPLICABLE_ROLES	GRANTEE
APPLICABLE_ROLES	GRANTEE_HOST
APPLICABLE_ROLES	HOST
APPLICABLE_ROLES	IS_DEFAULT
...

performance_schema データベースのテーブル名とカラム名

SELECT TABLE_NAME,COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA='performance_schema' の出力を使用しています。

accounts	CURRENT_CONNECTIONS
accounts	HOST
accounts	TOTAL_CONNECTIONS
accounts	USER
binary_log_transaction_compression_stats	COMPRESSED_BYTES_COUNTER
binary_log_transaction_compression_stats	COMPRESSION_PERCENTAGE
binary_log_transaction_compression_stats	COMPRESSION_TYPE
binary_log_transaction_compression_stats	FIRST_TRANSACTION_COMPRESSED_BYTES
binary_log_transaction_compression_stats	FIRST_TRANSACTION_ID
binary_log_transaction_compression_stats	FIRST_TRANSACTION_TIMESTAMP
binary_log_transaction_compression_stats	FIRST_TRANSACTION_UNCOMPRESSED_BYTES
binary_log_transaction_compression_stats	LAST_TRANSACTION_COMPRESSED_BYTES
binary_log_transaction_compression_stats	LAST_TRANSACTION_ID
binary_log_transaction_compression_stats	LAST_TRANSACTION_TIMESTAMP
binary_log_transaction_compression_stats	LAST_TRANSACTION_UNCOMPRESSED_BYTES
...

エラーメッセージ

エラーメッセージは perror コマンドで出力されます。

% perror 1022
MySQL error code MY-001022 (ER_DUP_KEY): Can't write; duplicate key in table '%-.192s'

ですが、エラー番号の一覧はわからないので、ソースコードを見るしかないかと思ってたのですが、include/mysqld_error.h から取れることがわかりました。

//#define OBSOLETE_ER_HASHCHK 1000
//#define OBSOLETE_ER_NISAMCHK 1001
#define ER_NO 1002
#define ER_YES 1003
#define ER_CANT_CREATE_FILE 1004
#define ER_CANT_CREATE_TABLE 1005
#define ER_CANT_CREATE_DB 1006
#define ER_DB_CREATE_EXISTS 1007
#define ER_DB_DROP_EXISTS 1008
//#define OBSOLETE_ER_DB_DROP_DELETE 1009
#define ER_DB_DROP_RMDIR 1010
//#define OBSOLETE_ER_CANT_DELETE_FILE 1011

これを使ってひたすら perror を実行して出力を得ています。

おわり

とまあ、こんなことを MySQL がリリースされる3ヶ月毎にやってます。

元々作り始めたのは 8.0 GA リリースよりも前だったのですが、これを作っておいたおかげで、パッチレベルリリースなのになぜか色々変更される MySQL 8.0.x の差分を確認することが簡単にできるようになりました。(GA とはいったい…？)

投げ銭はこのページの右の「￥サポートする」から受け付けております。

Discussion