2018年2月26日月曜日

MySQL 8.0.4の SHOW GRANTS の結果が想像したのとちょっと違う

MySQL 8.0.4にroot@localhostでログインして SHOW GRANTS を実行したらこうなった。
mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `root`@`localhost` WITH GRANT OPTION |
| GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION                                                                                                  |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
よく訓練されたMySQLerにはお分かりいただけると思うが、MySQL 5.7とそれ以前であればこんな感じだった。
mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
ALL修飾子(と呼ぶのか?)がなくなって、更に、SUPER権限の代替になる(らしい) *_ADMIN* 的なやつら(ADMINって入ってないやつもあるけど)が列挙されている。
見た目はかなり違うけど、与えられている権限自体は変わらない(そりゃそうだ)
テーブル的には mysql.user (今までと同じ)と mysql.global_grants (SUPER権限を分割したやつら)にそれぞれレコードがあった。
mysql80 7> SELECT * FROM mysql.user WHERE user= 'root'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: caching_sha2_password
 authentication_string:
      password_expired: N
 password_last_changed: 2018-01-30 11:01:30
     password_lifetime: NULL
        account_locked: N
      Create_role_priv: Y
        Drop_role_priv: Y
Password_reuse_history: NULL
   Password_reuse_time: NULL
1 row in set (0.00 sec)

mysql80 7> SELECT * FROM mysql.global_grants\G
*************************** 1. row ***************************
             USER: root
             HOST: localhost
             PRIV: BACKUP_ADMIN
WITH_GRANT_OPTION: Y
*************************** 2. row ***************************
             USER: root
             HOST: localhost
             PRIV: BINLOG_ADMIN
WITH_GRANT_OPTION: Y
*************************** 3. row ***************************
             USER: root
             HOST: localhost
             PRIV: CONNECTION_ADMIN
WITH_GRANT_OPTION: Y
*************************** 4. row ***************************
             USER: root
             HOST: localhost
             PRIV: ENCRYPTION_KEY_ADMIN
WITH_GRANT_OPTION: Y
*************************** 5. row ***************************
             USER: root
             HOST: localhost
             PRIV: GROUP_REPLICATION_ADMIN
WITH_GRANT_OPTION: Y
*************************** 6. row ***************************
             USER: root
             HOST: localhost
             PRIV: PERSIST_RO_VARIABLES_ADMIN
WITH_GRANT_OPTION: Y
*************************** 7. row ***************************
             USER: root
             HOST: localhost
             PRIV: REPLICATION_SLAVE_ADMIN
WITH_GRANT_OPTION: Y
*************************** 8. row ***************************
             USER: root
             HOST: localhost
             PRIV: RESOURCE_GROUP_ADMIN
WITH_GRANT_OPTION: Y
*************************** 9. row ***************************
             USER: root
             HOST: localhost
             PRIV: RESOURCE_GROUP_USER
WITH_GRANT_OPTION: Y
*************************** 10. row ***************************
             USER: root
             HOST: localhost
             PRIV: ROLE_ADMIN
WITH_GRANT_OPTION: Y
*************************** 11. row ***************************
             USER: root
             HOST: localhost
             PRIV: SET_USER_ID
WITH_GRANT_OPTION: Y
*************************** 12. row ***************************
             USER: root
             HOST: localhost
             PRIV: SYSTEM_VARIABLES_ADMIN
WITH_GRANT_OPTION: Y
*************************** 13. row ***************************
             USER: root
             HOST: localhost
             PRIV: XA_RECOVER_ADMIN
WITH_GRANT_OPTION: Y
13 rows in set (0.00 sec)
ちなみに、 GRANT SUPER しようとしたらワーニングになった。
mysql80 7> GRANT SUPER ON *.* TO root@127.0.0.1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql80 7> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1287 | The SUPER privilege identifier is deprecated |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

mysql80 7> SELECT * FROM global_grants WHERE user= 'root' AND host= '127.0.0.1'\G
Empty set (0.00 sec)

mysql80 7> SHOW GRANTS FOR root@127.0.0.1;
+------------------------------------------+
| Grants for root@127.0.0.1                |
+------------------------------------------+
| GRANT SUPER ON *.* TO `root`@`127.0.0.1` |
+------------------------------------------+
1 row in set (0.00 sec)
まだSUPER権限でも管理系の権限は振るえる(今後はわからないけれども9.0かしらね)
mysql80 9> SHOW GRANTS;
+------------------------------------------+
| Grants for root@127.0.0.1                |
+------------------------------------------+
| GRANT SUPER ON *.* TO `root`@`127.0.0.1` |
+------------------------------------------+
1 row in set (0.00 sec)

mysql80 9> SHOW MASTER LOGS;
+------------+-----------+
| Log_name   | File_size |
+------------+-----------+
| bin.000001 |       170 |
| bin.000002 |  39229419 |
| bin.000003 |       854 |
| bin.000004 |       791 |
+------------+-----------+
4 rows in set (0.01 sec)

mysql80 9> SET GLOBAL max_connections= 100;
Query OK, 0 rows affected (0.00 sec)
んー、変わっていくなあ。

2018年2月23日金曜日

MySQL Router 2.1.5経由でのMySQLへの接続に失敗する(MySQL Router 2.1.6で直るらしい)

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でもこういうことができるようになったかとちょっと感慨深い。