2018年2月23日金曜日

MySQL Router 2.1.5経由でのMySQLへの接続に失敗する

TL;DR


ことの起こりは単なるError: 2013(実際はコマンドラインクライアントじゃなくて、 レプリケーションのI/Oスレッドが起こしたんだけど
$ mysql -h127.0.0.1 -P13306
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

$ sudo less /var/log/mysqlrouter/mysqlrouter.log
2018-02-21 12:27:25 INFO    [7fe221c14700] [routing:test] started: listening on 127.0.0.1:13306; read-write
2018-02-21 12:27:39 WARNING [7fe213fff700] Timeout reached trying to connect to MySQL Server xxxx:3306: Connection timed out
2018-02-21 12:27:48 INFO    [7fe213fff700] [routing:test] fd=5 Pre-auth socket failure 127.0.0.1: client auth timed out
こればっかり出て、destinationに全然つながらなくなった。
隣の同僚 こと 角煮の深町 が「2.1.4では問題なくて2.1.5にバージョンアップしてから発生する」というところまで切り分けてくれたので、そのへんを重点的に調べることに。
取り敢えずリリースノートにはそんな変なことは書いてない。
とはいえタイムアウトって書いてあるからタイムアウトなんだろうけどなんか変わったのかな? って調べたらごっついコミットが出てきた。
select から poll にまるっと書き換わってる。だが8か月前。だったら2.1.5関係ないかと思ったら、2.1.4が2017/07のリリースだから8か月前でも2.1.5が初出だった。マジか。
で、 バグレポート に書いたとおり、以前は select のタイムアウト timeval型の tv_secにそのまま connect_timeout を渡していたのでタイムアウトまでの単位は秒だったが、 poll のタイムアウト int型のtimeout_msに connect_timeout1000倍しなければならないのにそれをせずに そのまま渡しているので、タイムアウトまでの単位がミリ秒になってしまった。
mysqlrouter.conf の [routing:xx] セクションに明示的に connect_timeout = 今までの値の1000倍 を書けば取り敢えず回避可能なので踏み抜いた際にはお試しください。

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);
が等価として扱えるというだけの関数である。
もうちょっと他の名前はなかったのか…。