2018/03/30

wslbridgeを使ってCygtermからWSLを扱うときに ALT + Dとかがアレになるやつの対策

/etc/bash.bashrc に↓を書き加えてもう二度とCygwin bashをまともに起動しないことにした。


exec /cygdrive/c/Users/yoku0825/wslbridge-0.2.4-cygwin64/wslbridge.exe -C ~

2018/03/27

#MANABIYA に行ってきました

3/23に開催された MANABIYA に行ってきました。

1日目の3/23しか行けなかったけれどかなり豪華なコンテンツで、澤田さんのPostgreSQLの今とこれから、自分のセッション、間に職員室なるものをはさんで澤田さん, 木下さん , そーだいさんと謎のCrossSession、 kazeburoさんのマイクロサービス on マルチクラウドと詰め込んだ1日でした。

モリスさんの分散処理とコンテナ化インフラの面白い関係も聞きたかったしJavaのカルチャーとグロースもすごく評判が良かったけれど自分のセッションとカブったのだ…。
どのセッションを聞こうかと悩むのはマルチトラックの楽しいところでもあるけれど、しゃべる側としては悩ましいところでもあり。

わたしのセッションコミュニティー版ユーザーの視点からMySQLのこれまでを振り返るではなんかゆるくMySQLのこれまでの機能追加についてなぞってみました。

改めて振り返ってみると、MySQLってここ数年で随分便利になったんですね。花マルをあげたい。

2018/03/12

#phperkaigi 2018 がとても楽しかった

PHPerKaigi 2018 に参加してきました。

まずはスポンサーのみなさん、いろいろ美味しゅうございましたありがとうございました。スタッフのみなさん、いろいろフォローありがとうございました。特に @uessy_akr さんがセッションの前後色々話しかけたりフォローしたりしてくれて助かりました。ありがとうございました！！

あとは初めて物理 @cakephper さんに会えました。実物は @soudai1025 とちゃんと区別がつきました。青くなかった！

PHPerKaigiのすごかったところ。

ラウンジに “Ask the speaker” というテーブルがあって、しゃべったあとのスピーカーはそこに誘導される
- 登壇後ぼっちスキルの高い俺でも話しかけてもらえた！母さん！今夜（？）は赤飯だ！
LT開始時に既に人間をダメにするドリンクが配布されている
- わかってらっしゃる…
- どうやら前夜祭からそうだったらしい
  - 参加したかった
  入り口でビールが配られるイベントだった #PHPerKaigi
  — どぅーあき (@do_aki) 2018年3月9日
安定のアンカンファレンス職人 @soudai1025 さんとキャッキャウフフ
- アンカンファレンススペースはやっぱり素敵ですね（ただし俺一人だとぼっちになる）
- @soudai1025 ++ => soudai1026？
アンカンファレンスでRDBのキャッキャウフフを @yoku0825 さんとやるぞ！！ #phperkaigi
— そーだい@初代ALF (@soudai1025) 2018年3月10日

RDB相談会！！@soudai1025 さんと @yoku0825 さんがキャッキャウフフしながらDBの悩みに答えてくれるよ！！wヾ(:3ﾉｼヾ)ﾉｼ#phperkaigi #b pic.twitter.com/XZoUWwWerY
— うえしー (@uessy_akr) 2018年3月10日
人生初 #phpstudy 参加
- 出張版PHP勉強会＠東京 in PHPerKaigi 2018 - PHP勉強会@東京 | Doorkeeper
- いつも近場で開催されてるのを横目にしつつ参加したことはなかったので！
- 自己紹介のGM〇ちほー率…
豪華すぎるビール
- わかってらっしゃる…！！１
- ビールスポンサーのDMM.comラボさんありがとうございます…！
本日のビールスポンサー。ありがとうございます。 #phperkaigi pic.twitter.com/yba2W9zvlu
— cakephper (@cakephper) 2018年3月10日

わたしのセッションは「サーバーが完膚なきまでに死んでもMySQLのデータを失わないための表技」でした。

サーバーが完膚なきまでに死んでもMySQLのデータを失わないための表技 from yoku0825

スライド内にときどき出てくる --master-info=2 はたぶん全部 --master-data=2 ですよね？？
— Ryuta Kamizono (@kamipo) 2018年3月12日

SlideShareがスライド修正できなくなってもう一度消してアップロードはあまりにもアレだから…もうSlideShareは使えない(つд｀)

スライド中にも言及がありますが、お題箱の障害報告を読んでいて（最初は ConoHa だったのか、という文脈で回ってきたのですよ）「みんなにもっとバックアップの方法を知ってもらった方が良いのでは！」とCfPにしました。

くどいようですが、お題箱がMySQLを使っていたかどうかは知りません。ごめんなさい。これで少しでも世界からバックアップが上手く取れてないケースが減るといいな！

1/6 19時頃から発生していた障害と投稿データの消失について – お題箱ブログ

大変楽しかったです。来年も開催されることを期待してます！

2018/02/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/02/23

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

TL;DR

MySQL Router 2.1.5からおもむろに connect_timeout がミリ秒単位になった

2.1.4とそれ以前では単位は秒だった
多分バグ
- MySQL Bugs: #89740: “Timeout reached trying to connect to MySQL Server” occurs too frequently
暗黙のデフォルトは1
1ミリ秒以上時間がかかる（たとえばインターネット越しの）destinationに接続できないエラーが出る…

【2018/02/28 23:24】バグ認定されて、MySQL Router 2.1.6 で直るらしい とのこと

ことの起こりは単なる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にバージョンアップしてから発生する」というところまで切り分けてくれたので、そのへんを重点的に調べることに。

取り敢えずリリースノートにはそんな変なことは書いてない。

MySQL :: MySQL Router Release Notes :: Changes in MySQL Router 2.1.5 (2018-02-02, General Availability)

とはいえタイムアウトって書いてあるからタイムアウトなんだろうけどなんか変わったのかな？って調べたらごっついコミットが出てきた。

replace select() by poll()/WSAPoll() · mysql/mysql-router@276abd2

select から poll にまるっと書き換わってる。だが8か月前。だったら2.1.5関係ないかと思ったら、2.1.4が2017/07のリリースだから8か月前でも2.1.5が初出だった。マジか。

MySQL :: MySQL Router Release Notes :: Changes in Release 2.1

で、バグレポートに書いたとおり、以前は select のタイムアウト timeval型の tv_secにそのまま connect_timeout を渡していたのでタイムアウトまでの単位は秒だったが、 poll のタイムアウト int型のtimeout_msに connect_timeout を 1000倍しなければならないのにそれをせずに そのまま渡しているので、タイムアウトまでの単位がミリ秒になってしまった。

mysqlrouter.conf の [routing:xx] セクションに明示的に connect_timeout = 今までの値の1000倍 を書けば取り敢えず回避可能なので踏み抜いた際にはお試しください。

MySQL Bugs: #89740: “Timeout reached trying to connect to MySQL Server” occurs too frequently

2018/02/08

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)

こんな感じになる。

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

MySQLのLOAD DATA INFILEでint DEFAULT NULL列にcsv上で空列なデータを投入しようとするとワーニングになる件、回避方法がわからないまま、本日は一旦終了。
— atsuizo (@atsuizo) 2018年2月6日

2018/02/05

MySQL 8.0.4の正規表現で更に遊ぶ（REGEXP_SUBSTR, REGEXP_INSTR）

MySQL 8.0.4で新たに追加された関数として、 REGEXP_SUBSTR と REGEXP_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でもこういうことができるようになったかとちょっと感慨深い。

登録: 投稿 ( Atom )

GA

2018/03/30

2018/03/27

2018/03/12

2018/02/26

2018/02/23

TL;DR

2018/02/08

TL;DR

2018/02/05