Author Archive

アプリケーションで長々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   [コメントがあればどうぞ]

ubuntu22.04LTSがリリースされ、

私が使っているpoposも追従して22.04が発表されたので、

アップデートしてみました。

(popos)

https://pop.system76.com/

しかし、必ず悲劇はあるもので、今の所直面した悲劇を2つご紹介します。

①VPNがつながらない

私が努めている会社では、

  • YAMAHAルータのVPN(IPsec)
  • LINUXサーバのVPN(xl2tpd+IPsec)
  • OpenVPN

の3つを用意しているのですが、22.04へのアップデート直後は、

OpenVPN以外は接続できないという状態となりました。(poposがVPNクライアントです)

IPsecがつながらない原因を調査していくと、どうやら ubuntuのバグにも多数報告が上がっていました。

https://bugs.launchpad.net/ubuntu/+source/xl2tpd/+bug/1951832

ずっとたどっていくと、

「xl2tpd – 1.3.16-1ubuntu0.1」というバージョンが急遽リリースされたようで、

これをインストールすることで、

  • LINUXサーバのVPN(xl2tpd+IPsec)

はなんとかつながるようになりました。

しかし、YAMAHAルータの方は、接続はできても、その後以下のようなエラーが発生し、切断されてしまいます。※経路の確立ができていない感じですかね。

xl2tpd[10265]: check_control: Received out of order control packet on tunnel 37328 (got 2, expected 3)
xl2tpd[10265]: handle_control: bad control packet!

これについては、ちょっとわかならい。。認証か暗号化の段階なのか。。

SSHがつながらない

というかつながるHOSTとつながらないHOSTが発生した。

つながらないHOSTをよくみたら、

sign_and_send_pubkey: no mutual signature supported

というエラーが発生していた。

これは、

20.04のときのOpenSSHクライアントが8.2で、

22.04になったらOpenSSHクライアント8.9になっており、

間の8.3リリースで、「ssh-rsa」が禁止されているためであった。

とりあえず、~/.ssh/configに以下の記載を追加して対応。

Host *
    HostKeyAlgorithms +ssh-rsa
    PubkeyAcceptedKeyTypes +ssh-rsa

途中の非LTSを飛ばしたので、なかなか大変。。

というわけで、アップデートは計画的に。

以上

投稿日時:2022年05月09日 23:30   カテゴリー:popos, ubuntu   [コメントがあればどうぞ]

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   [コメントがあればどうぞ]

ArmadaSuitさんが、fluentd / fluent-bit にfowardするC#クライアントを作成してくれました。

(Pigeon)

https://www.nuget.org/packages/Pigeon/

シンプルなライブラリではありますが、fluentdの4modeをサポートしています。

そのため、受け手側に合わせて柔軟な変更が対応が可能というものです。

基本的には、MessageModeで十分ですが、FluentNettingのようなリモートのサーバと直通信する場合は、

CompressedPackedForward Modeを使うことで、サイズを圧縮できるなどのメリットがあるかと思います。

ぜひ、FluentNettingと合わせておつかいください。

(FluentNetting)

https://www.nuget.org/packages/FluentNetting/

以上

投稿日時:2022年01月27日 19:12   カテゴリー:c#, fluentd   [コメントがあればどうぞ]

表題のとおり、fluentd / fluent-bitのoutput forwardを受信するC#のサーバライブラリを、

nugetにリリースしました。

(FluentNetting)

https://www.nuget.org/packages/FluentNetting/

これは、javaで作られているinfluentというライブラリをC#に移植したような感じのものです。

(influent)

https://github.com/okumin/influent

※私が作ったのではありません。すばらしいライブラリです。

で、FluentNettingでは、fluentdのv1プロトコルがサポートする4つのmodeに対応しつつ、

hostname / shared_key による認証までを対応しました。

さらにいうと、fluent-bitもテスト済みでございます。

このFluentNettingを使うといいことは、「遅延処理が自由自在」ということです。

アプリケーション → fluend / fluent-bit → FluentNettingを使った受信サーバ

のようにして、fluentd / fluent-bit でバッファリングしておけば特に良いのかなと思います。

メッセージキューイング系を使うのと同じじゃね?と思うかもしれませんが、

fluend / fluent-bit はアプリケーションと同一ホスト(k8sならサイドカー)にする形が多いので、

アプリケーションからの書き込みタイムが少なくできる利点があります。

プラグイン使えば良くない?と思うかもしれませんが、

プラグインはメンテナンスされていないものもあり、

場合によってはアプリケーションエンジニアが柔軟に変更するのが難しいケースがあります。

という事情を踏まえると、

fluentd / fluent-bit は forwardさえやればよく(設定が簡単)、

プラグインに悩まされずにデータを処理できる、

という利点があるのかと感じています。


各言語の対応ライブラリですが、基本的には公式が出しているものを使えば利用できます。

(fluentdのライブラリ)

https://docs.fluentd.org/language-bindings

ただし、確認した限りでは、PHPのライブラリはパーサがデフォルトJSONになっているので、

msgpackに変更する必要があります。

C#については、

(FluentdClient.Sharp)

https://www.nuget.org/packages/FluentdClient.Sharp/

というものがあるのですが、EventTimeがdouble型になっているようで、

fluent-bitではエラーとなってしまうようです。(fluentdでは大丈夫のようです)

各言語とFluentNettingの対応状況は後々確認していこうとおもいます。


最後になりましたが、今回ArmadaSuitさんにパーサ部分に尽力いただきました。

彼のおかげで、今回リリースまでこぎつけられました。感謝です。

その彼が、C#のクライアントライブラリを作成してくれているので、

近々nugetにアップされるのではないかと期待しています。

以上

投稿日時:2022年01月19日 17:57   カテゴリー:c#, fluentd   [コメントがあればどうぞ]

TCPに引き続き、UDPを作ってみた。

https://github.com/shigenobu/OrangeCabinet

一応、nugetに公開済みです。

これも、JAVA版があるのですが。。一応紹介。

https://github.com/shigenobu/blueshelf

一応、mavenで公開済みです。

この、C#のUDPライブラリの特徴は、以下となります。

  • 例によって、ハーフクローズを検知用に、最終受信からN秒でタイムアウト(UDPはそんなもんないけど)を処理
  • クライアントでもbindする

一番目は、送信元が同じ限り、タイムアウトするまでは独自のオブジェクトで任意変数を格納できる。

(セッションみたいなイメージ)

二番目は、クライアントでも予めbindすることで、ポートを決め打ちできる。

connectだと、ポートが決め打ちできないので、あえてbindを使う形にしています。

なんでこんなもんをつくっているのかというと、

実は、TCP/UDPのサーバ機能を必要とするあるものを作りたいと考えているからです。

うまくできたら、nugetに公開しようかと考えています。

今回ちょっとハマったのは、BeginXXXとEndXXXは対になっているらしく、

BeginReceiveFrom

EndReceiveFrom

はセットで使う必要があるということでした。

ただ、BeginReceiveFromの部分でも、使わないremote endpointインスタンスを生成しないといけないのは、ちょっと微妙な気がした。

完了時に、remote endpointが取れればいいと思うのだが。。

また、システムコールとしては、epoll_createは使われていたが、UDPなんでepollである必要もないのかなとは思った。

JAVAだと、UDPに対するepollの実装はないので(正確にはJAVA7でお蔵入りしたようだが)。

概ね問題なさそうだが、一時オブジェクトの生成が少し多い気がしている。

IAsyncResultオブジェクトを毎回作っているような感じなので、そこは調整できればとは思う。

(ただ、毎回作らないと受信バッファの読み込みがうまくいかないので、簡単にはいかない気がしている。)

以上

投稿日時:2021年12月13日 22:24   カテゴリー:c#, java, network   [コメントがあればどうぞ]

Javaで以前作ったTCPサーバ・クライアントみたいな感じで、

C#の非同期・ノンブロッキングTCPサーバ・クライアントを作ってみた。

https://github.com/shigenobu/PurpleSofa

一応、nuget.orgには登録してあります。

ちなみにJavaのもの。

https://github.com/shigenobu/redchest

一応、mavenには登録済みです。

以前、C#のTCP実装では、

  • BeginXXXのメソッドを使うパターン ※async/awaitではない
  • SocketAsyncEventArgsクラスを使うパターン

があるようだと書いたのですが、

「SocketAsyncEventArgsクラスを使うパターン」では、どう頑張っても接続ごとにスレッドを割り当てて、

受信を待機する形にせざるを得ない感じでした。

「SocketAsyncEventArgsクラスを使うパターン」は、少ないクライアントで大量のやりとりをするケースに向いているのかな?

と感じています。

そのため、汎用的な側面から、今回は「BeginXXXのメソッドを使うパターン」のパターンで実装しました。

C#版もJava版と同様、

  • openハンドラ(acceptもしくはconnect時に1回呼び出される)
  • messageハンドラ(read時にN回呼び出される)
  • closeハンドラ(close時に1回呼び出される)

という形となっています。

C#版もJava版同様、

  • 最終受信からN秒(デフォルト60秒)経過で、タイムアウト切断します。
  • 切断処理は、キューを経由して非同期で行われます。
  • 接続(セッション)のオブジェクト内に、任意の値を入れられます。

という実装になっています。(Java版は上記に加えて、シャットダウンハンドラーの実装もあります。)

このライブラリの最大の特徴は、「最終受信からN秒経過でタイムアウト切断」というところでしょうか。

なんでこんなことしているかっていうと、keep aliveをしようがなにしようが、

この方法でしか結果的に”ハーフクローズ”を検知できないからです。(経験的に)

C#版を作るにあたって、ちょっと気になった点として、

連続的にメッセージを送信すると、まれに受信時に2つのメッセージを同時に取得してしまうケースがあるようです。

Javaのときはなかったのですが、どんなに排他制御をしても無理な感じでした。(受信時の問題っぽいので)

そのため、実際に利用するときは、プロトコロル(というかメッセージフォーマット)をよく決めて、

アプリケーションでメッセージを分割できるようにする必要があるのかと感じます。

また、デフォルトで、バッファサイズを2048byteとっていますが、

広域通信だと、MTU(通常1500byteくらい)より小さい値で分割受信するケースもあるので、

このあたりもアプリケーションで吸収する必要があるかと思います。

以上

投稿日時:2021年12月07日 22:52   カテゴリー:c#, java, network   [コメントがあればどうぞ]

個人でこっそり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   [コメントがあればどうぞ]

C#で、非同期ソケット(TCP)を扱う場合、

  • BeginXXXのメソッドを使うパターン
  • SocketAsyncEventArgsクラスを使うパターン

の2つがあるようです。

使われているシステムコールに差異があるのかと思い、linux上の.NET5で試してみたところ、

straceのログには、両方ともepollのシステムコールが使われていました。

というわけで、両方とも、linux上で使う場合は、IO性能の差はほぼなさそうです。

では、何が違うのかというと、

BeginXXXのメソッドのコールバックでは、

IAsyncResultオブジェクトのアロケーションが、ソケット操作のたびに行われるのに対し、

SocketAsyncEventArgsは、接続ごとにアロケーションをする(つまり1クライアント1オブジェクト)形にすることで、

処理軽減を図っているのではないかと思われます。

(SocketAsyncEventArgsの公式ドキュメント)

https://docs.microsoft.com/ja-jp/dotnet/api/system.net.sockets.socketasynceventargs?redirectedfrom=MSDN&view=net-6.0

IAsyncResultオブジェクトで、接続先クライアントの情報もあれもこれも含めて渡す方法は悪くないが、

accept/readのインターフェースを統一しようとしていたのか、やはり無駄なように思える。

とはいえ、SocketAsyncEventArgsも初見では理解が難しい気もする。

なので、このあたりをラップしたライブラリを作っておいたほうがいい気がしている。

以上

投稿日時:2021年11月25日 14:29   カテゴリー:c#   [コメントがあればどうぞ]