カテゴリー「mariadb」

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は基本機能は揃っていますが、

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

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

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

以上

投稿日時:2023年03月22日 14:51   カテゴリー:java, mariadb, mysql   [コメントがあればどうぞ]

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のレプリケーションをしっているエンジニアであれば、

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

以上

投稿日時:2022年11月29日 17:22   カテゴリー:c#, mariadb, mysql   [コメントがあればどうぞ]

2022年8月にMariaDB10.9が、

2022年12月にMariaDB10.10がリリースされました。

10.9も10.10もShort Term Supportということで、

サポートは最初のGAリリースから1年のようです。

10.6が直近のLTS(サポートは2026年6月まで)なのですが、

どうやら、次の10.11がLTSになるようです。

https://mariadb.org/mariadb-10-11-is-lts/

10.9と10.10の変更点については、リリースノートを参照ください。

なかなか内容の把握が難しい。。

(10.9)

https://mariadb.com/kb/en/changes-improvements-in-mariadb-109/

(10.10)

https://mariadb.com/kb/en/changes-improvements-in-mariadb-1010/

一応、自分の作っているmagentadeskも10.10に合わせて対応済みです。

10.10でinet4型が入ったので、まあそのくらいですが。

https://github.com/shigenobu/magentadesk

以上

投稿日時:2022年11月29日 17:06   カテゴリー:mariadb   [コメントがあればどうぞ]

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)も結構あるので、運用は任せてしまうのも一手かもしれないが。

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

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

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

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

以上

投稿日時:2022年06月21日 23:23   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

MariaDB10.8.3が2022年5月にGAになりました。

https://mariadb.com/kb/en/mariadb-1083-release-notes/

10.7のときに見落としていたのですが、

10.7も10.8もshort term supportのようで、

サポート期間はリリースから1年のようです。

直近では、10.6がLTSという扱いのようです。

機能概要は以下のとおりです。

https://mariadb.com/kb/en/changes-improvements-in-mariadb-108/

個人的に気になった点だけ挙げておきます。


(LAG free ALTER)

レプリケーションをしていると、ALTER TABLEはまずプライマリ(マスター)で実行され、

完了してはじめて、レプリカ(スレーブ)に伝播するというものでした。

そのため、大きなテーブルでALTERを実行すると、レプリカの遅延が激しくなっていました。

(プライマリでALTER開始〜完了)→レプリケーション→(レプリカでALTER開始〜完了)

って感じが従来の流れですね。

で、今回の機能により、

(プライマリでALTER開始)→レプリケーション→(レプリカでALTER開始)→(プライマリ・レプリカでALTER完了)

って感じになるため、レプリカ遅延、つまりラグがおきにくいですよ、って話かと。

昔MySQLで巨大なテーブルにALTERしたら、マスターで2時間、スレーブで2時間、

合計4時間とかになったことがあったので、

これが合わせて2時間で終わる感じになるので、とても素敵な機能かと思います。

(JSON Histgrams)

histgramの結果を格納しているmysql.column_statsテーブルが変更になりました。

10.4

> desc mysql.column_stats;
+---------------+-----------------------------------------+------+-----+---------+-------+
| Field         | Type                                    | Null | Key | Default | Extra |
+---------------+-----------------------------------------+------+-----+---------+-------+
| db_name       | varchar(64)                             | NO   | PRI | NULL    |       |
| table_name    | varchar(64)                             | NO   | PRI | NULL    |       |
| column_name   | varchar(64)                             | NO   | PRI | NULL    |       |
| min_value     | varbinary(255)                          | YES  |     | NULL    |       |
| max_value     | varbinary(255)                          | YES  |     | NULL    |       |
| nulls_ratio   | decimal(12,4)                           | YES  |     | NULL    |       |
| avg_length    | decimal(12,4)                           | YES  |     | NULL    |       |
| avg_frequency | decimal(12,4)                           | YES  |     | NULL    |       |
| hist_size     | tinyint(3) unsigned                     | YES  |     | NULL    |       |
| hist_type     | enum('SINGLE_PREC_HB','DOUBLE_PREC_HB') | YES  |     | NULL    |       |
| histogram     | varbinary(255)                          | YES  |     | NULL    |       |
+---------------+-----------------------------------------+------+-----+---------+-------+

10.8

> desc mysql.column_stats;
+---------------+---------------------------------------------------+------+-----+---------+-------+
| Field         | Type                                              | Null | Key | Default | Extra |
+---------------+---------------------------------------------------+------+-----+---------+-------+
| db_name       | varchar(64)                                       | NO   | PRI | NULL    |       |
| table_name    | varchar(64)                                       | NO   | PRI | NULL    |       |
| column_name   | varchar(64)                                       | NO   | PRI | NULL    |       |
| min_value     | varbinary(255)                                    | YES  |     | NULL    |       |
| max_value     | varbinary(255)                                    | YES  |     | NULL    |       |
| nulls_ratio   | decimal(12,4)                                     | YES  |     | NULL    |       |
| avg_length    | decimal(12,4)                                     | YES  |     | NULL    |       |
| avg_frequency | decimal(12,4)                                     | YES  |     | NULL    |       |
| hist_size     | tinyint(3) unsigned                               | YES  |     | NULL    |       |
| hist_type     | enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB') | YES  |     | NULL    |       |
| histogram     | longblob                                          | YES  |     | NULL    |       |
+---------------+---------------------------------------------------+------+-----+---------+-------+

hist_typeに「JSON_HB」というものが増えてます。

これにより、「histgram」カラムの値に、JSONとして結果を格納することができるようになります。

で、hist_typeのデフォルトは、「DOUBLE_PREC_HB」のようなので、

「JSON_HB」を使うには以下のようにするようです。

MariaDB [test]> select @@histogram_type;
+------------------+
| @@histogram_type |
+------------------+
| DOUBLE_PREC_HB   |
+------------------+
1 row in set (0.001 sec)

MariaDB [test]> set histogram_type = 'JSON_HB';
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> select @@histogram_type;
+------------------+
| @@histogram_type |
+------------------+
| JSON_HB          |
+------------------+
1 row in set (0.000 sec)

MariaDB [test]> analyze table user persistent for all;
:
(省略)

そうすると、mysql.column_statsのhistgramカラムにJSONが入るようになります。

長いので一部にしますが、以下のようなJSONが取得入るかと。

MariaDB [test]> select * from mysql.column_stats;
:
{
  "target_histogram_size": 254,
  "collected_at": "2022-06-17 23:23:19",
  "collected_by": "10.8.3-MariaDB-log",
  "histogram_hb": [
    {
      "start": "1",
      "size": 0.00394246,
      "ndv": 689
    },
    {
      "start": "2047",
      "size": 0.00394246,
      "ndv": 689
    },
:
(省略)

なにかに使えるかもしれない。。

(Spider Storage Engine Improvements)

Spider Engineは10.4でプラグイン化しました。

https://mariadb.com/kb/en/spider-installation/

で、今まではSpider用の設定がコメントであったのですが、

これを、以下の3つのシステム変数として定義できるようになったみたいです。

  • REMOTE_SERVER
  • REMOTE_DATABASE
  • REMOTE_TABLE

従来のようにコメントでもできるようです。

これは後日試してみようかと。

(mysqlbinlog GTID support)

「–start-position」と「–stop-position」にGTIDが指定できるようになったようです。

これは嬉しい。

今まで「binlog_gtid_pos」関数使って、ファイルとポジションからGTIDを確認してましたが、

わざわざそんなことをしなくていいとなると、細かいようですが、かなりいいかなと。

ちなみに、MariaDBはGTIDを使ってレプリケーションを組むと、crash safeです。

ファイルとポジションはcrash unsafeです。

MySQLでは、

relay_log_info_repository = table

とすることで、crash safeにしていたかと。(MySQL8で、この辺もInnoDBになったはず)


という感じですかね。

Spiderとmariadb-binlog(mysql-binlog)は、後日検証しようかと思います。

また、私が作っているmagentadeskも、v0.4.5で10.8に対応しました。

https://github.com/shigenobu/magentadesk

以上

投稿日時:2022年06月17日 23:52   カテゴリー:mariadb   [コメントがあればどうぞ]

アプリケーションで長々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構築が楽になったりします。

以上

投稿日時:2022年05月10日 00:07   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

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から値を取り出す感じですかね。

以上

投稿日時:2022年02月22日 18:33   カテゴリー:mariadb, mysql   [コメントがあればどうぞ]

MariaDB10.7が2022年2月にGAとなりました。10.6から8ヶ月スパンでのリリースです。

https://mariadb.com/kb/en/changes-improvements-in-mariadb-107/

10.7.2はmysql_upgradeに不具合があり、すぐにリリースが取り消され、

10.7.3が初回GAとなったようです。

公式に書いてありますが、個人的に気になった点をまとめておきます。

  • UUID型(UUID DATA TYPE)の追加および、UUID系関数追加
  • JSON関数追加
  • order by時のオプションとして、「natural_sort_key」が追加
  • optimizer switchに「reorder」追加(デフォルトOFFのようです)
  • エンジン非依存の圧縮機能をプラグインとして追加(デフォルトのzlib以外はmysql_upgradeのときは注意が必要)
  • sformat関数追加(pythonのformat関数みたい)
  • mariadb-dumpの際、–as-ofオプションで、特定の瞬間のsystem versionedテーブルの状態を取得できる(全履歴じゃないので要注意)

あとは公式に書いてあるとおりです。

個人的には、sformat関数は嬉しいかなと。

結構、concat関数をつなげてることが多く、どうも見づらいなーと思っていたので。

あと、自分が作っているmagentadeskも、

UUID型と10.5で追加されたINET6型の対応も含めて、

10.7対応版としてv0.4.4をリリースしました。

https://github.com/shigenobu/magentadesk

10.8もすでにRCが動いているようで、10.8が早く来そうです。

10.8の対応内容は結構すごい感じを受けます。期待ですね。

以上

投稿日時:2022年02月22日 18:01   カテゴリー:mariadb   [コメントがあればどうぞ]

個人でこっそりgithubで作っているMariaDBの差分検出・反映ツールである「magentadesk」に、

API機能とHTML機能を追加しました。(0.4.0からHTML機能がはいってます)

(magentadesk)

https://github.com/shigenobu/magentadesk

今まではcli版しかなったのですが、web版も追加されています。

web版でしていることは、cli版の処理を呼び出しているに過ぎないのですが、

HTML機能を使うことで、各種jsonデータをsqliteに保存しておけるようになり、ボタン一つで差分検出・反映が可能となります。

API機能は、cli版の単なるHTTPインターフェースでしかないのですが、

HTML機能で追加したrelationという設定は、テーブルの子・親関係を登録しておくことで、

差分検出時にチェックボックスが連動するというものとなります。

magantadeskでは、外部キーが設定されているものは、

反映対象外としている(これは反映順番の整合性を保つのが難しいため)のですが、

それだと、差分確認時に、親子関係がわかりくいということから、このような機能を作ってみました。

で、API・HTML機能を作るに当たり、苦労した点があったので書いておきます。


(HTTPサーバにcom.sun.net.httpserver.HttpServerを利用)

デフォルトで同胞されているHTTPサーバなのですが、これが非常にくせのあるものでした。

例外などの際、ようは最後の最後のタイミングで、ただしくHTTPレスポンスを構築してくれないので、

最後の最後で、レスポンスを構築するように工夫しました。

(HTML機能の情報を蓄積するDBとして、sqliteを利用)

READ-COMMITEDをサポートしていないので、SERIALIZABLEの分離レベルを選択。

そのうえで、ファイルのため、WRITEブロックをすることは明白なので、

とにかくsqliteに接続している時間を各処理で短くするよう調整しました。

(テンプレートエンジンとしてvelocityを利用)

え?veloctiy?と思うかもしれませんが、2.3というバージョンが出ています。

当方は、velocity-toolは使わず、

event_handler.reference_insertion.class(旧eventhandler.referenceinsertion.class)

のプロパティを設定して、独自クラスでテンプレート関数を追加しています。

テンプレート上で、NULLと空文字の判定がデフォルトではできなかったので、

独自関数で対応しました。


という具合に、地道な苦労を重ねました。

magentadeskは主にwebサービスでの本番反映を想定して、汎用化を試みたものですが、

ニーズは少なそうだなと感じています。。

今後は、国際化対応(日本語化)を入れていきたいかなと考えていますが、

JAVAではなく、jqueryでやろうかなとか考え中です。

以上

投稿日時:2021年11月25日 15:04   カテゴリー:java, mariadb   [コメントがあればどうぞ]

また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が不具合ありって、いうわけじゃないですけど。

以上

投稿日時:2021年10月05日 23:40   カテゴリー:go, mariadb, mysql   [コメントがあればどうぞ]