技術めも

ER master という優れたツールがあります。

https://ermaster.sourceforge.net/index_ja.html

ただ、もう10年前に更新が止まっており、

eclipseプラグインの上、新しいeclipseでは動作しない（？）ものとなっています。

ER master は、データベースの管理はもとより、設計に注力したツールかと認識しており、

未だこれ以上のものは市場にはないような気はしています。

※有償でしか触れないようなものは除く

ずっと、ER master の代替になるようなものを作りたいと思っており、

非常に不十分ではありますが、ちょっとつくってみました。

（ebonyrack）

https://github.com/shigenobu/ebonyrack

https://github.com/shigenobu/ebonyrack/releases/tag/v0.0.1

（最新はこちら）https://github.com/shigenobu/ebonyrack/releases

mariadbの特有機能を盛り込もうかと思ってmariadbと書いてありますが、

特有機能を入れすぎて、複雑になりすぎてしまったため、

結局特有機能は削ぎ落とし、結果としてmysqlでも問題はない形になっています。

現時点では、不具合もあるでしょうし、機能不十分ではありますが、非常に大変でした。

クロスプラットフォーム（WIN、MAC、LINUX）での動作を目標としたため、

JAVAのSWINGで作成してします。

SWINGは基本機能は揃っていますが、

いわゆる気の利いたコンポーネントはないため、かなりコード量が肥大化しました。

よかったら使ってみてください。

今後もバージョンアップは続けますので。

以上

mysqlやmariadbからData Ware House（以下、DWH）へレプリケーションしたいな、って最近思います。

実際のところ、DWHがupdate/deleteといった行操作に弱いので、

なかなかDWHが使えない状況が続いていますが、

最近ではTiDBのTiFlushや、OCIのheatwaveといったもの（行操作も強そうなもの）が出てきているので、

OLTPのmysql/mariadbから、シームレスにレプリケーションいけそうじゃないか？

という感触を持っています。

とはいえ、update/delete問題が解決しても、実際はOLTPのデータベースと、

DWHをつなぐときは、マルチソースレプリケーションである必要があったり、

そもそも各種DDLを解決したりと、なかなか簡単には事が運ばない印象です。

そんなこんなで、Change Data Capture（以下、CDC）が使えるんじゃないかな？

とずっと思っていたのですが、C#にCDCのライブラリがあったので、紹介しておきます。

https://github.com/rusuly/MySqlCdc

まだ、動作検証はしてないですが、かなり色々できそうな気がしてます。

CDCのソフトウェアは有償・無償と色々ありますが、

実際にSQL（イベント）をアプリケーションで捕まえることができるようになるので、

ある程度mysqlのレプリケーションをしっているエンジニアであれば、

かなり面白いことができそうな気はしてます。

以上

mysqlプロトコル対応のwriteスケールアウトのDBについて書いてみる。

最初に言っておく、全部触ったことがない！

（MySQL NDB Cluster）

https://dev.mysql.com/doc/refman/8.0/ja/mysql-cluster.html

ライセンスは、GPLと商用の2種類あるよう。商用はサポートあり。

アーキテクチャは、管理ノード・SQLノード・データノードに分かれるもので、いわゆるshared nothing型。

トランザクション分離レベルは、READ COMMITTEDのみサポート。

（MariaDB Xpand）

https://mariadb.com/ja/products/enterprise/xpand/

ライセンスは商用のみ。

各ノードが管理・SQL・データを兼任し、いわゆるshared nothing型。

トランザクション分離レベルは、REPEATABLE READとREAD COMMIETTEDをサポート（一応SERIALIZEDもOKみたい）。

（TiDB）

https://pingcap.co.jp/tidb-overview/

ライセンスはApache2.0。

アーキテクチャは、管理ノード・SQLノード・データノードに分かれるもので、いわゆるshared nothing型。

v3.0からMySQLに近い「PESSIMISTIC TRANSACTION」をサポートし、v4以降は、READ COMMITTEDのみサポート。

（Vitess）

https://vitess.io/

ライセンスはApache2.0。

kubernatesでの動作が前提（一応、他でも動くみたい）。

データノードは、通常のmysqlなので、InnoDBが利用可能。

多分、トランザクション分離レベルは、READ COMMITTEDをサポート。（よくわからん）

分散DBは、正直使ってみて、

挙動を確かめてみないとわからない。。

よくあるのが、

• SQLの構文がサポートされていなかった
• データタイプがサポートされていなかった
• トランザクションがCOMMIT勝負だった
• ロックの挙動がmysqlと違う
• JOINが結構遅い
• 集約の結果がちょっとずれてる

などがあるのかな〜。

その他には、

• 障害時の復旧手順（復旧順番間違えるとロスト）
• バックアップ時の負荷

など、開発だけでなく、運用時のシュミレーションと検証を結構しておく必要があるかなと。

最近は、DaaS（Database as service）も結構あるので、運用は任せてしまうのも一手かもしれないが。

いずれにせよ、「銀の弾丸」はないわけで、

インフラのエンジニアも、アプリケーションのエンジニアも、

よく検証して、できること・できないこと、どうやったらまずいのかなど、

を整理しておく必要があるのかなと。

以上

アプリケーションで長々SQLを書かなきゃいけないんだけど、

条件によってはSQLを評価しないときってあるかと思います。

たとえば、検索するときの対象テーブルとして、

• 記事
• 動画

の2つがあったとき、条件によっては、記事のみにしたいって場合です。

このとき、アプリケーションのIFで、動画のSQLは流さないようにするってのが普通だと思うんですが、

CTE（WITH）とか使っていると、後続の式（たとえばUNION）で動画の分を抜くとか調整しなくちゃいけなくてめんどくさかったり。。

そんなとき、ちょっと役に立つ技が「LIMIT 0」です。

これは、公式にも書いてあるように、即座に空の結果セットを返すというものです。

https://dev.mysql.com/doc/refman/8.0/ja/limit-optimization.html

explain時のExtraには、「Zero limit」というのが表示されます。

それ以外にも、SQLとして正しいかどうかだけをチェックするときにも使えたりします。

「LIMIT 0」以外にも、自分の小ネタを紹介しておきます。

たとえば、アプリケーションでWHEREを組み立てなきゃいけないとき、

条件がなにもなかったら、WHERE句自体を消すとかもありますよね。

そんなとき、自分は「1 = 1」で初期化しておくみたいなこともやったりします。

そうすると、WHERE句自体は残したままでよくなるので、文字列編集の煩わしさが減ります。

「LIMIT 0」とか「WHERE 1 = 1」とか、はっきいって邪道ですが、

使うと意外にアプリケーションでのSQL構築が楽になったりします。

以上

1対Nのテーブル2つがある場合、N側のテーブルはデータを縦持ちするはず。

しかしながら、1側のテーブルに合わせて、1行で表示したい場合の小ネタ。

せっかくなので、MariaDB10.7の新機能も添えて紹介。

たとえば、こんなテーブルとデータがあります。

CREATE TABLE `question` (
  `question_id` int(11) NOT NULL COMMENT '問題ID',
  `question_text` text NOT NULL COMMENT '本文',
  PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='問題'

> select * from question;
+-------------+--------------------------------------------------------------------------------------------------------------+
| question_id | question_text                                                                                                |
+-------------+--------------------------------------------------------------------------------------------------------------+
|           1 | しゃっくりはある調味料をなめると止まります。ある調味料とはなんでしょう？                                                 |
+-------------+--------------------------------------------------------------------------------------------------------------+

CREATE TABLE `question_select` (
  `question_id` int(11) NOT NULL COMMENT '問題ID',
  `select_id` int(11) NOT NULL COMMENT '選択肢ID',
  `select_text` text NOT NULL COMMENT '選択肢内容',
  PRIMARY KEY (`question_id`,`select_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='選択肢'

> select * from question_select;
+-------------+-----------+-------------+
| question_id | select_id | select_text |
+-------------+-----------+-------------+
|           1 |         1 | お酢        |
|           1 |         2 | 砂糖        |
|           1 |         3 | 醤油        |
|           1 |         4 | 塩          |
+-------------+-----------+-------------+

これを1行で表示したい場合、以下のようにやる。

> with 
 t as (
   select
     t1.question_id,
     t1.question_text,
     sformat('[{}]', group_concat(distinct json_object('select_id', t2.select_id, 'select_text', t2.select_text) order by t2.select_id)) as select_list
   from
     question as t1
     join
     question_select as t2
     on t1.question_id = t2.question_id
   group by
     t1.question_id, t1.question_text
 )
 select
   question_id,
   question_text,
   json_value(select_list, '$[0].select_id') as select_id_1,
   json_value(select_list, '$[0].select_text') as select_text_1,
   json_value(select_list, '$[1].select_id') as select_id_2,
   json_value(select_list, '$[1].select_text') as select_text_2,
   json_value(select_list, '$[2].select_id') as select_id_3,
   json_value(select_list, '$[2].select_text') as select_text_3,
   json_value(select_list, '$[3].select_id') as select_id_4,
   json_value(select_list, '$[3].select_text') as select_text_4
 from
   t
 ;

+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+
| question_id | question_text                                                                                                | select_id_1 | select_text_1 | select_id_2 | select_text_2 | select_id_3 | select_text_3 | select_id_4 | select_text_4 |
+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+
|           1 | しゃっくりはある調味料をなめると止まります。ある調味料とはなんでしょう？                                                 | 1           | お酢          | 2           | 砂糖          | 3           | 醤油          | 4           | 塩            |
+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+

ポイントはgroup_concatで横持ちに変換する際に、JSON配列にしてしまうこと。

集計するときに結構使える技。

今回はMariaDBの10.7新機能であるsformat関数を使ってみましたが、concat関数でも代用可能です。

MySQLの場合は、「->>」とかで、JSONから値を取り出す感じですかね。

以上

またMySQLのUDFをつくってみた。

https://github.com/shigenobu/mysql_ws_neologd_normalize

今回は有名なneologdの標準化アルゴリズムに、CR・LF・タブ・水平タブを削除するものを追加したUDFです。

（参考 neologdの標準化アルゴリズム）

https://github.com/neologd/mecab-ipadic-neologd/wiki/Regexp.ja

なぜこれを作ったかというと、すべては検索のためですね。

入力も、対象データも、そして辞書すらも、すべて一定のアルゴリズムで標準化されることで、

正しく検索が機能するためです。

今回のUDFは、対象データの部分に該当します。

入力の部分は、C#とPHPでも同様のアルゴリズムを実装していますが、

そこまで大したものではないので、特に公開はしてないです。

※さらにいえば、入力の部分では、対象データと同じアルゴリズムの形態素解析も必要かなと思います。

だいぶ、cgoでMySQLのUDFを作ってきた（現在5つをgithubに公開）のですが、

今後はrustとかに手を出して、redisモジュールなんかもつくってみたいかと（自分の中で需要がないが。。）。

今回のUDFは、すでに長いこと実践投入しているので、多分大丈夫だと思います。

いや、他のUDFが不具合ありって、いうわけじゃないですけど。

以上

Mysql/MariaDBのsubstring関数は、マルチバイトセーフと書いてあります。

しかしながら、日本語の長文を扱っている際、JDBCおよびADO.NETからsubstringするとなぜか、

Incorrect string value:

のエラーが出てしまいました。

文字コードや各接続はutf8mb4で統一しており、どうもおかしいと思い、原因は不明なままでしたが、

例によってcgoで作ってみました。

https://github.com/shigenobu/mysql_ws_substring

この関数を通すことで、上記エラーは発生しなくなりました。

あまりに長文だったのですが、utf8の不正なバイトシーケンスが入っていたのでは？と思っていますが、

さすがに根気がなくなり調査断念しました。（それに急ぎだったので）

まあ、なんでこんな関数つくったのかというと、mroongaを使っていた際、

mecab_sparse_tostr() failed len=xxxxx err=too long sentence

のエラーが出てしまい、自作のwordwrap関数でもこの問題は解決せず、

「あれ、ひょっとして文書が長過ぎる？」と思い、データを見てみたところ、

どうも256KiBを超えたあたりが怪しい感じがしました。

というわけで、10万文字でsubstringしてINSERTできればOKじゃね？と思い、

この関数を作ってみました。

結果として、10万文字に収めたところ、上記エラーはでなくなりました。

mroongaの公式サイトには、

GRN_MECAB_CHUNKED_TOKENIZE_ENABLED=yes

の環境変数を利用することで、解決するかもと書いてあって試してみたものの、

当該カラムの文書量が多いせいか、2時間以上たっても、FULL TEXT インデックスが構築完了しませんでした。

※文書が少ないカラムはこちらの環境変数を設定していても即座にインデックスの構築は完了しました。

一度でも登録したらエラーとなっているので、今回の関数でいかざるを得なかった感じです。

時間があったら、mecabのソースなどを調査してみようと思います。

以上

PHPのwordwrap関数のマルチバイト対応版をMySQLのUDFとしてつくりました。

いつものようにcgoで作ってます。

https://github.com/shigenobu/mysql_ws_wordwrap

PHPのwordwrap関数は以下のリンクとなります。

（PHPのwordwrap）

https://www.php.net/manual/ja/function.wordwrap.php

今回作ったUDFのポイントは、なるべくPHPの関数の第4引数（cut_long_words）をtrueの状態にしたものとなります。

で、このPHPのwordwrap関数の特徴として、何度やっても同じになる（微妙な表現ですが）になるってことなのかと思います。

php > $input = 'aa<br>abbbccc<br>dddee<br>e';
php > $dst1 = wordwrap($input, 3, '<br>', true);
php > echo $dst1;
aa<br>abb<br>bcc<br>c<br>ddd<br>ee<br>e
php > $dst2 = wordwrap($dst1, 3, '<br>', true);
php > echo $dst2;
aa<br>abb<br>bcc<br>c<br>ddd<br>ee<br>e
php > echo (int) $dst1 == $dst2;
1

つまり、breakワードが分割位置に含まれていたら、そこで分割せずに、breakワードを残しつつ、次以降の文字列の評価を行うものなのかと。

※なんていうアルゴリズムなのかわからなかったので、正解ではないかもしれません。

作り始めた当初は、breakワードを一旦消してから、指定文字数でbreakワードいれればいける？と思っていたのですが、

もとの文章にbreakワードが入っている場合、それを消してしまうことになるので、それはちょっと違うかなと思い、

breakワードの位置を探しつつ、構築する形にしてみました。

なんでこんな関数を作ったのかというと、mroongaを使っていた際、

mecab_sparse_tostr() failed len=xxxxx err=too long sentence

というエラーがでたので、これってmecabのストップワードがないから？と思ってしまい、

それなら強制的にwordwrapさせればいける？と思い、作りました。

実際には、文書自体が長過ぎるとエラーになるようで、この関数では解決しなかったんですが。。

本来ならストップワードを指定して分割する形も考えたのですが、急ぎで対応する必要があったので、

今後ヒマがあったら考えみようかと思います。

以上

またしてもcgoで作りました。

https://github.com/shigenobu/mysql_ws_split

前回作成した、ws_parse_url関数との違いは、

引数が2つあることですが、意外とこれがはまりました。

MySQLって、splitする関数がなくて（まあ、あったとしてもどうするんだという話）、

splitして、JSON配列に展開しちゃえば、そこから加工しやすくなるだろうという算段です。

以上

PHPのparse_url関数のような動作をするMySQLのUDFをcgoで作成しました。

https://github.com/shigenobu/mysql_ws_parse_url

（参考）PHPのparse_url関数

https://www.php.net/manual/ja/function.parse-url.php

この関数のポイントは、クエリー形式のパラメータをキー名でとれるところです。

最近はアクセスログをDBに保存して、そのまま分析に使うなどをすることもあり、MySQL側で一気に加工できれば楽だなと思いつくりました。

これ以外にも作成したものがいくつかあるので、そのうち公開しようと思います。

なお、動作確認したのはMariaDB10.4のみなので、もしお使いになる場合は自身の環境での動作確認をお願いします。

以上