IT系のめもを蓄積していこうかと
RCリリースですが、0.0.3をリリースしました。
https://github.com/shigenobu/ebonyrack/releases/tag/v0.0.3
（最新はこちら）https://github.com/shigenobu/ebonyrack/releases
を前回書きましたが、やはりIE記法は難しく、
なんとか数字で表示する形で対応しました。
残りの課題は、折を見てやろうかな。。
あとはちょいちょい修正などをして、
次でGAリリースしたいかなと。
以上
ebonyrackの0.0.2をリリースしました。
https://github.com/shigenobu/ebonyrack/releases/tag/v0.0.2
（最新はこちら）https://github.com/shigenobu/ebonyrack/releases
まだ、RCですが、ちょちょい修正しています。
あと1回くらいRCだして、GAリリースにしようかなと。
かなり課題があるので、取り組めたら以下やろうかなと。
とはいえ、全体的な作りもイマイチなので、どうにか見直したいと思うこのごろ。。。
以上
2023年2月にMariaDB 10.11がリリースされました。
https://mariadb.com/kb/en/changes-improvements-in-mariadb-1011/
LTSリリースということで、2028年2月までのサポートとなります。
基本的には、リリースノートを見ていただければとは思いますが、
個人的には system versioning テーブルの dump/restore サポートがついに入ったか〜という感じです。
たとえば、こんな感じで、テーブルつくって、データ操作します。
create table t_system_versioning (
id int not null,
name varchar(32) not null,
primary key (id)
) with system versioning;
insert into t_system_versioning values (1, 'n1');
update t_system_versioning set name = 'n2' where id = 1;
delete from t_system_versioning where id = 1;
このあとの段階では、当然以下のようになります。
MariaDB [test]> select * from t_system_versioning;
Empty set (0.000 sec)
履歴をすべてを出力すると、以下のようになります。
MariaDB [test]> select *, row_start, row_end from t_system_versioning for system_time all;
+----+------+----------------------------+----------------------------+
| id | name | row_start | row_end |
+----+------+----------------------------+----------------------------+
| 1 | n1 | 2023-03-22 16:56:29.376669 | 2023-03-22 16:56:53.845864 |
| 1 | n2 | 2023-03-22 16:56:53.845864 | 2023-03-22 16:57:08.520850 |
+----+------+----------------------------+----------------------------+
2 rows in set (0.000 sec)
これまでは、これらの履歴データが dump で取得できませんでした。
10.11で追加されたオプション「–dump-history」を使うことで、履歴が dump に載るようになります。
$ mariadb-dump --databases test --tables t_system_versioning --dump-history
:
:
/*!101100 SET @old_system_versioning_insert_history=@@session.system_versioning_insert_history, @@session.system_versioning_insert_history=1 */;
LOCK TABLES `t_system_versioning` WRITE;
/*!40000 ALTER TABLE `t_system_versioning` DISABLE KEYS */;
INSERT INTO `t_system_versioning` (`id`, `name`, row_start, row_end) VALUES (1,'n1','2023-03-22 07:56:29.376669','2023-03-22 07:56:53.845864'),
(1,'n2','2023-03-22 07:56:53.845864','2023-03-22 07:57:08.520850');
/*!40000 ALTER TABLE `t_system_versioning` ENABLE KEYS */;
UNLOCK TABLES;
:
:
これで、やっと system versioning も使い易くなった感じです。
デフォルトの挙動としてもいい気がしますが。。
その他として、GPGキーが2023年から変わっています。
https://mariadb.com/kb/en/gpg/
バージョンアップの際は、上記を参考にGPGキーを更新する必要があるので、注意が必要です。
また、自分が作成している magentadesk も10.11 に対応しました。
https://github.com/shigenobu/magentadesk
v0.4.9 が 10.11 の対応版となります。
以上
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のレプリケーションをしっているエンジニアであれば、
かなり面白いことができそうな気はしてます。
以上
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
以上
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）
ライセンスはApache2.0。
kubernatesでの動作が前提（一応、他でも動くみたい）。
データノードは、通常のmysqlなので、InnoDBが利用可能。
多分、トランザクション分離レベルは、READ COMMITTEDをサポート。（よくわからん）
分散DBは、正直使ってみて、
挙動を確かめてみないとわからない。。
よくあるのが、
などがあるのかな〜。
その他には、
など、開発だけでなく、運用時のシュミレーションと検証を結構しておく必要があるかなと。
最近は、DaaS（Database as service）も結構あるので、運用は任せてしまうのも一手かもしれないが。
いずれにせよ、「銀の弾丸」はないわけで、
インフラのエンジニアも、アプリケーションのエンジニアも、
よく検証して、できること・できないこと、どうやったらまずいのかなど、
を整理しておく必要があるのかなと。
以上
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つのシステム変数として定義できるようになったみたいです。
従来のようにコメントでもできるようです。
これは後日試してみようかと。
（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
以上
アプリケーションで長々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から値を取り出す感じですかね。
以上