2018年2月8日木曜日

LOAD DATA INFILEステートメントの中でカラムの順番とかをゴニョる

TL;DR

  • LOAD DATA INFILE ステートメントで、CSVなりTSVなりのフィールドの並び順とテーブルのカラムの並び順が一緒じゃない時にほげる方法とか
  • 読み取った値を加工してからテーブルに突っ込む方法とか

Twitter からダウンロードできる tweets.csv はこんなフォーマットをしている。
"tweet_id","in_reply_to_status_id","in_reply_to_user_id","timestamp","source","text","retweeted_status_id","retweeted_status_user_id","retweeted_status_timestamp","expanded_urls"
"878118626489802752","","","2017-06-23 05:12:49 +0000","<a href=""http://twitter.softama.com/"" rel=""nofollow"">ツイタマ+ for Android</a>","宇宙の 法則が 乱れる!","","","",""
先頭1行がヘッダ行になっていて、フィールドは全てダブルクォートされ、タイムスタンプはUTCで、UTCであることが +0000 でわかるようになっている(聞こえますかMySQLのみなさん)
ちなみに expanded_urls が複数ある場合、特に断りもなくコンマ区切りで後ろに要素が続く(10フィールドより多くフィールドがある場合がある、ばかな)
これをこんな感じのテーブルに、MySQLだけでパースシテ何とか上手いことLOAD DATA INFILEしたいとする。
mysql80 23> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `tweet_id` bigint(20) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  `source` text COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `text` text COLLATE utf8mb4_ja_0900_as_cs NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs
1 row in set (0.01 sec)
まずはわかりやすくするために、全フィールドの内容を変数に受け取るステートメント。
mysql80 23> LOAD DATA
    ->   INFILE '/home/yoku0825/tweets.csv'
    ->   INTO TABLE t1
    ->   FIELDS TERMINATED BY ','
    ->          ENCLOSED BY '"'
    ->   IGNORE 1 LINES
    ->   (@tweet_id,
    ->    @in_reply_to_status_id,
    ->    @in_reply_to_user_id,
    ->    @timestamp,
    ->    @source,
    ->    @text,
    ->    @retweeted_status_id,
    ->    @retweeted_status_user_id,
    ->    @retweeted_status_timestamp,
    ->    @expanded_urls)
    -> ;
Query OK, 41123 rows affected (0.50 sec)
Records: 41123  Deleted: 0  Skipped: 0  Warnings: 0
FILEDSとかIGNOREとかのフォーマット指定の 後に (いつも忘れる) 、変数のリストを並べることで、それぞれのフィールドをそれぞれの変数に受け取ることができる。
(↑のLOAD DATA INFILEは変数にセットするだけでカラムに何も値をセットしていないので、恐ろしく空っぽな行がt1テーブルにロードされている…)
この変数を使って、SET句でカラムに値を指定することができる。
mysql80 23> TRUNCATE t1;
Query OK, 0 rows affected (0.09 sec)

mysql80 25> LOAD DATA
    ->   INFILE '/home/yoku0825/tweets.csv'
    ->   INTO TABLE t1
    ->   FIELDS TERMINATED BY ','
    ->          ENCLOSED BY '"'
    ->   IGNORE 1 LINES
    ->   (@tweet_id,
    ->    @in_reply_to_status_id,
    ->    @in_reply_to_user_id,
    ->    @timestamp,
    ->    @source,
    ->    @text,
    ->    @retweeted_status_id,
    ->    @retweeted_status_user_id,
    ->    @retweeted_status_timestamp,
    ->    @expanded_urls)
    ->   SET
    ->     tweet_id = @tweet_id,
    ->     timestamp = TIMESTAMPADD(HOUR, 9, REGEXP_SUBSTR(@timestamp, '\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}')),
    ->     source = @source,
    ->     text = @text
    -> ;
Query OK, 41123 rows affected (1.33 sec)
Records: 41123  Deleted: 0  Skipped: 0  Warnings: 0
折角なので REGEXP_SUBSTRで遊んでみた 時のを活かして、タイムスタンプを正規表現でゴニョっている。
基本的な仕組みはこんなところで、余計なフィールドを変数で受けずに捨てるための変数( @dummy )に受けたりカラムに直接渡したりなんてことをすると最終的に
mysql80 25> TRUNCATE t1;
Query OK, 0 rows affected (0.04 sec)

mysql80 25>
mysql80 25>
mysql80 25> LOAD DATA
    ->   INFILE '/home/yoku0825/tweets.csv'
    ->   INTO TABLE t1
    ->   FIELDS TERMINATED BY ','
    ->          ENCLOSED BY '"'
    ->   IGNORE 1 LINES
    ->   (tweet_id,
    ->    @dummy,
    ->    @dummy,
    ->    @timestamp,
    ->    source,
    ->    text,
    ->    @dummy,
    ->    @dummy,
    ->    @dummy,
    ->    @dummy
    ->   )
    ->   SET
    ->     timestamp = TIMESTAMPADD(HOUR, 9, REGEXP_SUBSTR(@timestamp, '\\d{4}-\\d{2}-\\d{2}\\s+\\d{2}:\\d{2}:\\d{2}'))
    -> ;
Query OK, 41123 rows affected (1.05 sec)
Records: 41123  Deleted: 0  Skipped: 0  Warnings: 0

mysql80 25> SELECT * FROM t1 LIMIT 3;
+--------------------+---------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
| tweet_id           | timestamp           | source                                                                               | text                                                                           |
+--------------------+---------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
| 878118626489802752 | 2017-06-23 14:12:49 | <a href="http://twitter.softama.com/" rel="nofollow">ツイタマ+ for Android</a>       | 宇宙の 法則が 乱れる!                                                         |
| 878116753091448832 | 2017-06-23 14:05:23 | <a href="http://twitter.com/download/android" rel="nofollow">Twitter for Android</a> | RT @xaicron: @yoku0825 MySQLは時を超える...                                    |
| 878116497389797377 | 2017-06-23 14:04:22 | <a href="http://twitter.softama.com/" rel="nofollow">ツイタマ+ for Android</a>       | @xaicron (  д ) ゚  ゚ ホントだ!! ありがとうございます!                       |
+--------------------+---------------------+--------------------------------------------------------------------------------------+--------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
こんな感じになる。


って感じでどうでしょう? :)



2018年2月5日月曜日

MySQL 8.0.4の正規表現で更に遊ぶ(REGEXP_SUBSTR, REGEXP_INSTR)


MySQL 8.0.4で新たに追加された関数として、 REGEXP_SUBSTRREGEXP_INSTR がある。
REGEXP_REPLACE もあるけどこれはいいや( mroonga_snippet 的なことができるかもなのでまた別で遊ぶかも)
検索する正規表現にマッチした文字列を返してくれるREGEXP_SUBSTRとその文字列が現れるオフセットを返してくれるREGEXP_INSTR。
特にREGEXP_SUBSTRは面白そうなんだけど、引数が (expr, pat[, pos[, occurrence[, match_type]]]) になっている時点で複数回マッチしたものを配列で受けるなんてやり方はできない。残念…。
という訳で、折角MySQL 8.0なのでCTEを使ってこれを受け取れるんじゃないかテスト。
mysql80 19> SET @pattern := 'MySQL\\s*\\d[\.\\d]+';
Query OK, 0 rows affected (0.00 sec)

mysql80 19> WITH RECURSIVE cte (step, tweet_id, text, str, pos) AS (
    -> SELECT 1 AS step, tweet_id, text, CAST(REGEXP_SUBSTR(text, @pattern, 1, 1) AS char(1024)) AS str, REGEXP_INSTR(text, @pattern, 1, 1) FROM t1 WHERE REGEXP_INSTR(text, @pattern, 1, 1)   
    -> UNION ALL   
    -> SELECT step + 1, tweet_id, text, CAST(REGEXP_SUBSTR(text, @pattern, 1, step + 1) AS char(1024)), REGEXP_INSTR(text, @pattern, 1, step + 1) FROM cte WHERE REGEXP_INSTR(text, @pattern, 1, step + 1)  )
    -> 
    -> SELECT tweet_id, ANY_VALUE(text), GROUP_CONCAT(str), GROUP_CONCAT(pos) FROM cte GROUP BY tweet_id HAVING COUNT(*) > 1 ORDER BY tweet_id LIMIT 5\G
*************************** 1. row ***************************
         tweet_id: 219335904735657984
  ANY_VALUE(text): RT @sh2nd: MySQL 5.1はInnoDBの管理スレッドがos_thread_sleep(1000000);していて、MySQL 5.5はos_event_wait_time_low(srv_timeout_event, 1000000, sig_count) ...
GROUP_CONCAT(str): MySQL 5.1,MySQL 5.5
GROUP_CONCAT(pos): 12,66
*************************** 2. row ***************************
         tweet_id: 255543243696136192
  ANY_VALUE(text): 最近Vadimさん、MySQL5.6にお熱で嬉しい。

MySQL 5.6.7-RC in tpcc-mysql benchmark http://t.co/aBUKFgaL
GROUP_CONCAT(str): MySQL5.6,MySQL 5.6.7
GROUP_CONCAT(pos): 11,30
*************************** 3. row ***************************
         tweet_id: 294125457853513729
  ANY_VALUE(text): RT @i_rethi: ぐぐったらあんまりtcmallocをMySQL5.5で使うケースが日本語では見当たらなかったので書いてみた / MySQL5.5でtcmallocを使用する http://t.co/GuatemFS
GROUP_CONCAT(str): MySQL5.5,MySQL5.5
GROUP_CONCAT(pos): 32,70
*************************** 4. row ***************************
         tweet_id: 298969186275844097
  ANY_VALUE(text): RT @nippondanji: ブログ書きました:  MySQL 5.6正式リリース!! #mysql56 http://t.co/zGSh0ArK
GROUP_CONCAT(str): MySQL 5.6,mysql56
GROUP_CONCAT(pos): 29,48
*************************** 5. row ***************************
         tweet_id: 301220291202412544
  ANY_VALUE(text): ひょひょっと調べたので書いてみた。

日々の覚書: MySQL5.6のマスターにMySQL5.5(とそれ以前)のスレーブをぶら下げるとエラる http://t.co/kIjOmBdd
GROUP_CONCAT(str): MySQL5.6,MySQL5.5
GROUP_CONCAT(pos): 28,42
5 rows in set (0.17 sec)
textが MySQL\\s*\\d[\.\\d]+ にマッチするもの(MySQL バージョン番号っぽい文字列)のうち、2回以上マッチしそうなもの( SELECT .. FROM cte .. HAVING COUNT(*) > 1 )を引っこ抜いてみる。
WITH RECURSIVEで再帰CTEにして、引数の occurrence (=何番目のマッチ) をインクリメントさせている。
有用かどうかは置いておいてMySQLでもこういうことができるようになったかとちょっと感慨深い。

2018年1月30日火曜日

MySQL 8.0.4でMySQLの正規表現演算がだいぶマシになる


TL;DR

  • MySQL 5.7とそれ以前にも一応 REGEXP演算子 (またはRLIKE演算子)はあって、多少正規表現っぽいことはできるんだけど正規表現としては全然物足りなかった。
    • 少なくとも \s で空白文字にマッチできないとかちょっとPerlの正規表現で甘やかされた身にとってはつらい
    • しかも遅いんだこれが
    • あとマルチバイト非対応(マルチバイトに対して使おうと思ったことないけど)
  • MySQL 8.0.4とそれ以降ではICUの正規表現エンジンを使うことでかなーりマシに。

古くからこういう使い方はできた。
カラム名 REGEXP '正規表現文字列' (俺はRLIKEの方が好きでRLIKEって書くけどREGEXP演算子と意味は一緒、カラム名で使うことが多いけど正しくは判定文字列)
mysql57 5> SELECT * FROM t1 WHERE text RLIKE '^MySQL 5.[67]' LIMIT 3;
+--------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id           | timestamp           | text                                                                                                                                                                |
+--------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 252971369963335681 | 2012-10-02 12:20:49 | MySQL 5.6.7のチェンジログにしっかり見つけた。。 http://t.co/fR5yc3vD                                                                                                |
| 342484389072093184 | 2013-06-06 12:33:36 | MySQL 5.6.12 has still this bugってPHPから叩くときのこと?

MySQL Bugs: #69027: Default secure_auth value breaking PHP connects http://t.co/yx2f54Viwp             |
| 343936149896720384 | 2013-06-10 12:42:22 | MySQL 5.6 root ログインできない っていう検索トラフィック増えてきてるんですけど。 。orz                                                                               |
+--------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql57 5> SELECT * FROM t1 WHERE text RLIKE 'MySQL\\sEnterprise' LIMIT 3;
Empty set (0.51 sec)

mysql57 5> SELECT * FROM t1 WHERE text RLIKE '^マイ[エ]ス' LIMIT 3;
Empty set (0.06 sec)
新しい正規表現は REGEXP_LIKE関数 で提供されるけれど、今までどおりのRLIKE, REGEXP演算子を使った書き方もできる。その場合でも使う正規表現エンジンはICU版。
mysql80 13>SELECT * FROM t1 WHERE text RLIKE '^MySQL 5.[67]' LIMIT 3;
+--------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id           | timestamp           | text                                                                                                                                                                |
+--------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 252971369963335681 | 2012-10-02 12:20:49 | MySQL 5.6.7のチェンジログにしっかり見つけた。。 http://t.co/fR5yc3vD                                                                                                |
| 342484389072093184 | 2013-06-06 12:33:36 | MySQL 5.6.12 has still this bugってPHPから叩くときのこと?

MySQL Bugs: #69027: Default secure_auth value breaking PHP connects http://t.co/yx2f54Viwp             |
| 343936149896720384 | 2013-06-10 12:42:22 | MySQL 5.6 root ログインできない っていう検索トラフィック増えてきてるんですけど。 。orz                                                                               |
+--------------------+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

mysql80 14> SELECT * FROM t1 WHERE text RLIKE 'MySQL\\sEnterprise' LIMIT 3;
+--------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id           | timestamp           | text                                                                                                                                                                                                                                                  |
+--------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 366900653253070848 | 2013-08-12 21:35:07 | RT @mysql_japan: MySQL Workbench 6.0がGAになりました。UIのデザインが変更となった ほか、新たにMySQL Enterprise Editionの機能のUIも加わっています。 http://t.co/aiQM21dPZ5 #MySQL #my…                                                                   |
| 385784283853365249 | 2013-10-04 00:11:55 | MySQL Enterprise Monitorのバグレポート一気に増えてるねぇ。                                                                                                                                                                                            |
| 404150382252670976 | 2013-11-23 16:32:14 | RT @h141gm: バックアップの方法
  mysqldump
  コールドバックアップ
  スナップショット(→要リカバリ)
  バイナリログ
  MySQL Enterprise Backup (差分取得も可)
  レプリケーション利用
#ost2013                                                           |
+--------------------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql80 13> SELECT * FROM t1 WHERE text RLIKE '^マイ[エ]ス' LIMIT 3;
+--------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id           | timestamp           | text                                                                                                                                                      |
+--------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| 372177762645053440 | 2013-08-27 11:04:28 | マイエスキューエる。                                                                                                                                      |
| 563640699841560576 | 2015-02-06 19:09:49 | マイエス☆キューエル                                                                                                                                       |
| 747379558760931328 | 2016-06-27 19:42:27 | マイエスキューエルファブリック。是非、声に出していただきたい。なんか響きがかっこ いい気がしてくるから。                                                    |
+--------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.05 sec)
いいねいいね。
RLIKE演算子ではなくて REGEXP_LIKE(カラム名, '正規表現文字列', 'マッチタイプ') を使うとIgnore Caseができるようになる。マッチタイプは省略可能。
mysql80 15> SELECT * FROM t1 WHERE REGEXP_LIKE(text, 'mysql', 'i') LIMIT 3;
+--------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tweet_id           | timestamp           | text                                                                                                                                                                                                                             |
+--------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 167925415959199744 | 2012-02-10 19:58:33 | myspiをmysqlに空目… RT @lifehackerjapan: 最新記事: あごを脱力して心の中で「アー」と言う!? 薬なしでも不眠を克服できる簡単なテクニック #myspi http://t.co/6pueAVSf #lh_jp                                                         |
| 188134150971203586 | 2012-04-06 14:20:51 | MySQL DBA試験に向けて追い込み。                                                                                                                                                                                                  |
| 188280387879968768 | 2012-04-07 00:01:57 | MySQL DBA受検の為にLinuxにもMySQL入れとこう⇒VirtualBox使ってCentOS入れる(2日)⇒FEDERATEDとNDB使いたいからソースからコンパイルしよう(1日)⇒試験まであと3日(昨日の夜ここ)                                                            |
+--------------------+---------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
OLTPの中で使うことはないだろうけど、ちょっとした飛び道具にRLIKEはそこそこ好きなのでちょっとだけ嬉しい。


【2018/01/30 16:54】
なお、ドキュメントにはしゃらっと書かれているけれど

Because MySQL uses the C escape syntax in strings (for example, \n to represent the newline character), you must double any \ that you use in your expr and pat arguments.

なので、 '\s' をREGEXP_LIKEに渡す時は '\\s' にしてやらないといけない

2018年1月25日木曜日

MySQL 8.0.4でエラーログのフォーマットが微妙に変わった

TL;DR

  • log_error_verbosity のデフォルトが3(Error + Warning + Note) から 2(Error + Warning)に変更されたよ
  • MySQL 8.0.4からエラーログのフィールドに error_id が追加されたよ
  • “Note”, “Warning”, “Error”の3つだった severity に “System”が追加されたよ
    • “System” は “Error” より強いレベルなので log_error_verbosity が1(最小値)でも出力される
  • ↓こんな出力になるよ

2018-01-25T01:22:56.821986Z 0 [System] [MY-010116] /usr/mysql/8.0.4/bin/mysqld (mysqld 8.0.4-rc) starting as process 9206 ...

Messages written to the error log by the log_sink_internal log writer component now contain an error-ID indicator. This ID has a format of [error_id]. It follows the severity indicator and precedes the message text. For more information, see Error Log Message Format.
ということで、エラーログのフィールドがちょっと変わっている。
5.7.21の起動ログはこんな感じで
2018-01-25T01:24:06.510686Z mysqld_safe Logging to '/usr/mysql/5.7.21/data/error.log'.
2018-01-25T01:24:06.555127Z mysqld_safe Starting mysqld daemon with databases from /usr/mysql/5.7.21/data
2018-01-25T01:24:06.566872Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2018-01-25T01:24:06.567016Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
..
2018-01-25T10:24:07.842676+09:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-01-25T10:24:07.842747+09:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-01-25T10:24:07.867477+09:00 0 [Note] Event Scheduler: Loaded 0 events
2018-01-25T10:24:07.867745+09:00 0 [Note] /usr/mysql/5.7.21/bin/mysqld: ready for connections.
Version: '5.7.21-log'  socket: '/usr/mysql/5.7.21/data/mysql.sock'  port: 64057  Source distribution
2018-01-25T10:24:07.906647+09:00 0 [Note] InnoDB: Buffer pool(s) load completed at 180125 10:24:07
8.0.4はこんな感じ。
2018-01-25T01:24:09.351792Z mysqld_safe Logging to '/usr/mysql/8.0.4/data/error.log'.
2018-01-25T01:24:09.415752Z mysqld_safe Starting mysqld daemon with databases from /usr/mysql/8.0.4/data
2018-01-25T01:24:09.596184Z 0 [Warning] [MY-010139] Changed limits: max_open_files: 1024 (requested 8161)
2018-01-25T01:24:09.596340Z 0 [Warning] [MY-010142] Changed limits: table_open_cache: 431 (requested 4000)2018-01-25T01:24:14.383583Z 0 [Warning] [MY-010323] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
..
2018-01-25T01:24:14.412655Z 0 [Warning] [MY-010330] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-01-25T01:24:14.412724Z 0 [Warning] [MY-010330] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-01-25T01:24:14.437421Z 0 [System] [MY-010931] /usr/mysql/8.0.4/bin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/usr/mysql/8.0.4/data/mysql.sock'  port: 64080  Source distribution.
人間が読む分にはそんなに情報量は変わっていない。エラーIDでのググラビリティが上がるのかなって何この ORA-* 的な MY-* はww
Systemで出力されるログにはどんなのがあるかざっと調べてみる。
$ grep -A1 SYSTEM_LEVEL sql/*.cc | grep ER
sql/mysqld.cc:    LogErr(SYSTEM_LEVEL, ER_SERVER_SHUTDOWN_COMPLETE, my_progname);
sql/mysqld.cc:  LogErr(SYSTEM_LEVEL, ER_STARTING_AS,
sql/mysqld.cc:      LogErr(SYSTEM_LEVEL, ER_NORMAL_SHUTDOWN, my_progname);
sql/mysqld.cc-            .lookup(ER_SERVER_STARTUP_MSG,
sql/rpl_slave.cc:    LogErr(SYSTEM_LEVEL, ER_RPL_SLAVE_DUMP_THREAD_KILLED_BY_MASTER,
sql/rpl_slave.cc-           ER_RPL_SLAVE_CONNECTED_TO_MASTER_REPLICATION_STARTED,
sql/rpl_slave.cc-               ER_RPL_SLAVE_CONNECTED_TO_MASTER_REPLICATION_RESUMED,
sql/rpl_slave.cc:    LogErr(SYSTEM_LEVEL, ER_SLAVE_CHANGE_MASTER_TO_EXECUTED,
sql/sql_parse.cc:      LogErr(SYSTEM_LEVEL, ER_PARSER_TRACE,
sql/sql_parse.cc:        LogErr(SYSTEM_LEVEL, ER_PARSER_TRACE, thd->query().str);
sql/sql_restart_server.cc:  LogErr(SYSTEM_LEVEL, ER_RESTART_RECEIVED_INFO,
sql/sys_vars.cc:  LogErr(SYSTEM_LEVEL, ER_CHANGED_GTID_MODE,
sql/sys_vars.cc:  LogErr(SYSTEM_LEVEL, ER_GTID_PURGED_WAS_UPDATED,
sql/sys_vars.cc:  LogErr(SYSTEM_LEVEL, ER_GTID_EXECUTED_WAS_UPDATED,
sql/xa.cc:    LogErr(SYSTEM_LEVEL, ER_XA_STARTING_RECOVERY);
sql/xa.cc:    LogErr(SYSTEM_LEVEL, ER_XA_RECOVERY_DONE);
全部は見てないけど、ER_SERVER_SHUTDOWN_COMPLETEとかER_NORMAL_SHUTDOWNとかは5.7とそれ以前ではNoteだった( log_error_verbosity < 3に設定すると見えなくなって焦ったやつ ⇒ 日々の覚書: MySQL 5.7では”[Note] mysqld: ready for connections”がどっかいった? (いってなかった )ので、 log_error_verbosity = 2 をデフォルトにしても問題ないように布石を打ったのかしらん。 これは良い感じだ。
なおこの MY-* の数字は perror で引くことができる。
$ /usr/mysql/8.0.4/bin/perror  010116
MySQL error code 10116 (ER_STARTING_AS): %s (mysqld %s) starting as process %lu ...

$ /usr/mysql/8.0.4/bin/perror 010931
MySQL error code 10931 (ER_SERVER_STARTUP_MSG): %s: ready for connections. Version: '%s'  socket: '%s'  port: %d  %s.
大した話ではないけどなかなか面白かった。

2018年1月24日水曜日

MySQL 8.0.4におけるデフォルト認証形式の変更


Incompatible Change: The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is now the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password. This change affects both the server and the libmysqlclient client library:

MySQLはそれぞれのアカウントが「どうやって認証されるべきか」をアカウント情報の中に持っている(5.6とそれ以降) mysql.user テーブルには plugin というカラムがあって、
mysql80 10> SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)
この場合、 mysql.infoschema@localhost, mysql.session@localhost, mysql.sys@localhostmysql_native_password プラグイン、 root@localhostcaching_sha2_password で認証される、という設定になっている。
MySQLの認証はチャレンジ・レスポンス認証なので、クライアントとサーバーで同じ認証形式をサポートしている必要がある。
root@localhost でログインしようとする場合、クライアント側も caching_sha2_password プラグインをサポートしていないといけないが、MySQL 5.7(少なくとも5.7.21)とそれ以前のバージョンには存在しないので転ける。
$ /usr/mysql/5.7.21/bin/mysql -S /usr/mysql/8.0.4/data/mysql.sock -u root
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/mysql/5.7.21/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
それに対して mysql.sys@localhost などはそもそも今までの mysql_native_password プラグインを使おうとするので、5.7とそれ以前のクライアントから接続しようとしても「パスワードが違う」エラーだけで、認証プラグインがどうこうのエラーにはならない。
$ /usr/mysql/5.7.21/bin/mysql -S /usr/mysql/8.0.4/data/mysql.sock -u mysql.sys
ERROR 1045 (28000): Access denied for user 'mysql.sys'@'localhost' (using password: NO)
MySQL 8.0.4とそれ以降は、認証プラグインを指定しないでユーザーを作成した場合のデフォルトが caching_sha2_password になっているので、MySQL 8.0.4とそれ以降にアップグレードしてから(プラグインを指定せずに)新しく作ったユーザーに対して、5.7とそれ以前と caching_sha2_password をサポートしていないライブラリーで接続しようとすると炸裂する。
これを避けるにはサーバー側に default_authentication_plugin を設定しておけば良くて、
$ vim my.cnf
[mysqld]
default_authentication_plugin= mysql_native_password
これで認証プラグイン指定しなかった場合のデフォルトが mysql_native_password に変更できる。再起動が必要なので(少なくともPHPのmysqlndとかその他libmysqlclient使ってるやつでも8.0に追従するまでは)秘伝のタレに入れておいていいかと。
デフォルトは変えずにユーザー単位で指定する場合は、 WITH mysql_native_password を指定すればOK( mysqldumpmysqlpump にはこの WITH 指定が含まれているので、8.0.4とそれ以降に対してこれらをリストアしても大丈夫)

mysql80 13> CREATE USER yoku0825 IDENTIFIED WITH mysql_native_password BY 'hogehoge';
Query OK, 0 rows affected (0.02 sec)
パスワードはハッシュ形式で保管されるので、既に存在していてパスワードを設定してしまったアカウントを ALTER USER で変更してもパスワードハッシュは更新されず…というかそれどころか IDENTIFIED BY '' と同じ扱いになってパスワードがからっぽになる。
mysql80 23> SELECT user, host, plugin, authentication_string FROM user;
+------------------+-----------+-----------------------+-------------------------------------------+
| user             | host      | plugin                | authentication_string                     |
+------------------+-----------+-----------------------+-------------------------------------------+
| yoku0825         | %         | mysql_native_password | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| mysql.infoschema | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.session    | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root             | localhost | mysql_native_password |                                           |
+------------------+-----------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql80 23> ALTER USER yoku0825 IDENTIFIED WITH caching_sha2_password;
Query OK, 0 rows affected (0.00 sec)

mysql80 23> SELECT user, host, plugin, authentication_string FROM user;
+------------------+-----------+-----------------------+-------------------------------------------+
| user             | host      | plugin                | authentication_string                     |
+------------------+-----------+-----------------------+-------------------------------------------+
| yoku0825         | %         | caching_sha2_password |                                           |
| mysql.infoschema | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.session    | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys        | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| root             | localhost | mysql_native_password |                                           |
+------------------+-----------+-----------------------+-------------------------------------------+
5 rows in set (0.00 sec)
これちょっと誤発動すると痛いからパーサーでエラーにしてほしいなぁ…。。。

【2018/01/24 17:22】

IDENTIFIED WITH auth_plugin
..
In addition, the password is marked expired. The user must choose a new one when next connecting.

https://dev.mysql.com/doc/refman/8.0/en/alter-user.html

だそうで。


mysql80 25> CREATE USER yoku0825 IDENTIFIED WITH caching_sha2_password BY 'caching_sha2';
Query OK, 0 rows affected (0.01 sec)

mysql80 25> SELECT user, host, plugin, authentication_string, password_expired FROM user WHERE user = 'yoku0825';
+----------+------+-----------------------+------------------------------------------------------------------------+------------------+
| user     | host | plugin                | authentication_string                                                  | password_expired |
+----------+------+-----------------------+------------------------------------------------------------------------+------------------+
| yoku0825 | %    | caching_sha2_password | $A$005$feA!*,'z
LvMw1OqJfNOBC0z03D4plWGdAtRsSIxpQg7iDE1yowBB0 | N                |
+----------+------+-----------------------+------------------------------------------------------------------------+------------------+
1 row in set (0.00 sec)

mysql80 25> ALTER USER yoku0825 IDENTIFIED WITH mysql_native_password;
Query OK, 0 rows affected (0.01 sec)

mysql80 25> SELECT user, host, plugin, authentication_string, password_expired FROM user WHERE user = 'yoku0825';
+----------+------+-----------------------+-----------------------+------------------+
| user     | host | plugin                | authentication_string | password_expired |
+----------+------+-----------------------+-----------------------+------------------+
| yoku0825 | %    | mysql_native_password |                       | Y                |
+----------+------+-----------------------+-----------------------+------------------+
1 row in set (0.00 sec)

$ mysql80 -uyoku0825
mysql80 26> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.



2018年1月18日木曜日

MySQLのVALUES関数の(今のところ)唯一の使い道

TL;DR

PostgreSQLの VALUES は引数の表リテラル(行リテラルや列リテラルを含む)をテーブルリファレンスにして返してくれる関数だけれど、MySQLの VALUES は残念ながらそんなことはない。

MySQLのINSERTにおけるVALUESはただのキーワードでVALUES関数ではない。
なのでPostgreSQLみたいに表リテラルからテーブルリファレンスを組み立てる用途には使えない(´・ω・`) < よく言われるやーつだ
ところがどっこい、そのVALUESキーワードとは に、VALUES関数 があって、コイツがまたフツーのVALUES関数ではなくて
INSERT … ON DUPLICATE KEY UPDATE ステートメントでは、UPDATE 句の VALUES(col_name) 関数を使用すると、ステートメントの INSERT 部分からカラム値を参照できます。
というニッチな関数になっている。
つまり、
INSERT INTO t1 (num, val, dt) VALUES (1, 'one', '2018-01-18 12:40:00') ON DUPLICATE KEY UPDATE dt = '2018-01-18 12:40:00';
INSERT INTO t1 (num, val, dt) VALUES (1, 'one', '2018-01-18 12:40:00') ON DUPLICATE KEY UPDATE dt = VALUES(dt);
が等価として扱えるというだけの関数である。
もうちょっと他の名前はなかったのか…。

2018年1月10日水曜日

pfs_example_plugin_employee.so is 何

プラグインディレクトリーを覗いてたら pfs_example_plugin_employee.so なるファイルがあって知らないものなので調べてみた。
知らないプラグインを見つけた時の身元調査用に使えるかも知れないのでメモ。

まずはビルドしたディレクトリーの中で find 、MySQL(に限らないのかもだけど)ではコンパイルした時にソースコードのすぐとなりにバイナリーが出来上がるので、変に名前の一部からディレクトリーの名前を類推するよりもこっちの方が楽だったりする。
$ find -name "pfs_example_plugin_employee.so"
./plugin/pfs_table_plugin/CMakeFiles/CMakeRelink.dir/pfs_example_plugin_employee.so
./plugin_output_directory/pfs_example_plugin_employee.so
という訳でソースは plugin/pfs_table_plugin の近くにありそう。
$ ll plugin/pfs_table_plugin/
total 124
drwxrwxr-x 4 yoku0825 yoku0825   125 Jan  9 13:47 CMakeFiles
-rw-rw-r-- 1 yoku0825 yoku0825  2274 Jan  9 13:47 cmake_install.cmake
-rw-r--r-- 1 yoku0825 yoku0825  1175 Sep 19 20:33 CMakeLists.txt
-rw-rw-r-- 1 yoku0825 yoku0825   319 Sep 21 22:29 CTestTestfile.cmake
-rw-rw-r-- 1 yoku0825 yoku0825 15021 Sep 21 22:29 Makefile
-rw-r--r-- 1 yoku0825 yoku0825 13276 Sep 19 20:33 pfs_example_employee_name.cc
-rw-r--r-- 1 yoku0825 yoku0825  5220 Sep 19 20:33 pfs_example_employee_name.h
-rw-r--r-- 1 yoku0825 yoku0825 10242 Sep 19 20:33 pfs_example_employee_salary.cc
-rw-r--r-- 1 yoku0825 yoku0825  4404 Sep 19 20:33 pfs_example_employee_salary.h
-rw-r--r-- 1 yoku0825 yoku0825 10271 Sep 19 20:33 pfs_example_machine.cc
-rw-r--r-- 1 yoku0825 yoku0825  4482 Sep 19 20:33 pfs_example_machine.h
-rw-r--r-- 1 yoku0825 yoku0825  8236 Sep 19 20:33 pfs_example_machines_by_emp_by_mtype.cc
-rw-r--r-- 1 yoku0825 yoku0825  5235 Sep 19 20:33 pfs_example_machines_by_emp_by_mtype.h
-rw-r--r-- 1 yoku0825 yoku0825 11821 Sep 19 20:33 pfs_example_plugin_employee.cc
本体はきっと pfs_example_plugin_employee.cc だなあと思いつつ中身を覗く。
MySQLのプラグインのソースを読むときは mysql_declare_plugin を探すと良い気がしていて、そこに SHOW PLUGINS で表示される情報や INSTALL PLUGIN, UNINSTALL PLUGIN の時に走る関数が指定されている。ちなみに INSTALL PLUGIN で指定するプラグインの名前は mysql_declare_plugin の引数になっている。これ豆。
…限りなく情報はゼロに近かった。 そんなに長くもないコードなのでゆっくり読むけれど、何をどう考えてもPerformance Schemaプラグインのサンプルコード。そもそも名前からそう。しかし MYSQL_DAEMON_PLUGIN なのか。RCだから取り敢えずなのかどうかわからないけれど。
取り敢えず有効化してみようか。
mysql80 7> INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.so';
Query OK, 0 rows affected (0.01 sec)

mysql80 7> SHOW PLUGINS\G
..
*************************** 42. row ***************************
   Name: pfs_example_plugin_employee
 Status: ACTIVE
   Type: DAEMON
Library: pfs_example_plugin_employee.so
License: GPL
できた。 これできっとp_sのテーブルができているはず。
mysql80 7> use performance_schema
Database changed

mysql80 7> SHOW TABLES LIKE '%example%';
+------------------------------------------+
| Tables_in_performance_schema (%example%) |
+------------------------------------------+
| pfs_example_employee_name                |
| pfs_example_employee_salary              |
| pfs_example_machine                      |
| pfs_example_machine_by_employee_by_type  |
+------------------------------------------+
4 rows in set (0.00 sec)

mysql80 7> SHOW CREATE TABLE pfs_example_employee_name\G
*************************** 1. row ***************************
       Table: pfs_example_employee_name
Create Table: CREATE TABLE `pfs_example_employee_name` (
  `EMPLOYEE_NUMBER` int(11) NOT NULL,
  `FIRST_NAME` char(20) DEFAULT NULL,
  `LAST_NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_NUMBER`),
  KEY `FIRST_NAME` (`FIRST_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
☆―(ノ゚Д゚)八(゚Д゚ )ノイエ―イ
しかしこれ
mysql80 7> SELECT * FROM pfs_example_employee_name;
Empty set (0.00 sec)

mysql80 7> SELECT * FROM pfs_example_employee_salary;
Empty set (0.00 sec)

mysql80 7> SELECT * FROM pfs_example_machine;
Empty set (0.00 sec)

mysql80 7> SELECT * FROM pfs_example_machine_by_employee_by_type;
Empty set (0.00 sec)
中身が軒並み空なのは、想定した通りなんだろうか…( INSTALL PLUGIN した時に いかにも固定値で値を詰め込むような処理 を通るんだけれども…)
取り敢えずまあ、information_schemaプラグインのようにPerformance Schemaプラグインを書ける時代が来る鴨ってことですね。
取り敢えずPerformance Schemaプラグインに関する記述は(Daemonプラグインのところにも)まだ記述がない。
リリースノートにはなんか、8.0.2でその辺のインターフェイスが作られたようなことが書いてあった。
To support dynamic Performance Schema table manipulation, a new component service named pfs_table_service is now available.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.2 (2017-07-17, Development Milestone)

【2018/01/10 18:07】
rpmで mysql-community-test を入れるとこの pfs_example_plugin_employee プラグインが入っているわけですが、こっちで試すとちゃんとレコードが入ってた。


$ yum install --enablerepo="mysql80-community" mysql-community-test
$ mysqld --initialize-insecure --user=mysql
$ /etc/init.d/mysqld start

mysql> INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.so';
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT * FROM pfs_example_employee_name;
+-----------------+------------+-----------+
| EMPLOYEE_NUMBER | FIRST_NAME | LAST_NAME |
+-----------------+------------+-----------+
|               1 | foo1       | bar1      |
|               2 | foo2       | bar2      |
|               3 | foo3       | bar3      |
+-----------------+------------+-----------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM pfs_example_employee_salary;
+-----------------+-----------------+---------------+---------------+
| EMPLOYEE_NUMBER | EMPLOYEE_SALARY | DATE_OF_BIRTH | TIME_OF_BIRTH |
+-----------------+-----------------+---------------+---------------+
|               1 |            1000 | 2013-11-12    | 12:02:34      |
|               2 |            2000 | 2016-02-29    | 12:12:30      |
|               3 |            3000 | 2017-03-24    | 11:12:50      |
+-----------------+-----------------+---------------+---------------+
3 rows in set (0.07 sec)

mysql> SELECT * FROM pfs_example_machine;
+-------------------+--------------+--------------+-----------------+
| MACHINE_SL_NUMBER | MACHINE_TYPE | MACHINE_MADE | EMPLOYEE_NUMBER |
+-------------------+--------------+--------------+-----------------+
|                 1 | DESKTOP      | Lenovo       |               1 |
|                 2 | LAPTOP       | Dell         |               2 |
|                 3 | MOBILE       | Apple        |               1 |
|                 4 | MOBILE       | Samsung      |               1 |
|                 5 | LAPTOP       | Lenovo       |               2 |
|                 6 | MOBILE       | Nokia        |               2 |
|                 7 | LAPTOP       | Apple        |               1 |
|                 8 | LAPTOP       | HP           |               3 |
|                 9 | DESKTOP      | Apple        |               3 |
+-------------------+--------------+--------------+-----------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM pfs_example_machine_by_employee_by_type;
+------------+-----------+--------------+-------+
| FIRST_NAME | LAST_NAME | MACHINE_TYPE | COUNT |
+------------+-----------+--------------+-------+
| foo1       | bar1      | LAPTOP       |     1 |
| foo1       | bar1      | DESKTOP      |     1 |
| foo1       | bar1      | MOBILE       |     2 |
| foo2       | bar2      | LAPTOP       |     2 |
| foo2       | bar2      | MOBILE       |     1 |
| foo3       | bar3      | LAPTOP       |     1 |
| foo3       | bar3      | DESKTOP      |     1 |
+------------+-----------+--------------+-------+
7 rows in set (0.00 sec)