日々の覚書

• 日々の覚書: MySQL Shellのプロンプトをゼロからカスタマイズしてみんとす(classとsegment) の続き
• segmentsの中にclassを置くときになぜか配列型にするけど、それはCOALESCEみたいな感じで「最初に存在したクラスを適用する」みたいな動き

謎だったのだ。パッケージに一緒についてくる /usr/share/mysqlsh/prompt/ あたりに転がっているprompt.jsonのこのへんの記述。

  "segments": [
..
    {
      "classes": ["noschema%schema%", "schema"],
      "bg": 242,
      "fg": 15,
      "shrink": "ellipsize",
      "weight": -1,
      "padding" : 1
    },
..

https://github.com/mysql/mysql-shell/blob/8.0.28/samples/prompt/prompt_256pl.json#L103

↓こんな風に、スキーマがセットされてる時は灰色の、そうでない時はそもそもこのラベルそのものが表示されないような感じ。

しかし noschema と schema はクラスとして定義されているが、 noschemad1 なんてクラスは定義されていないし、とするとこのクラス名の中の %schema% はスキーマ名として展開される組み込み変数とは違うのか…？ とか思ってたら、READMEに書いてあった。

README最高だな ドキュメントに書けよ 。

https://github.com/mysql/mysql-shell/blob/master/samples/prompt/README.prompt#L94-L96

クラスのリスト（たとえこの空振りを利用した打ち分けをしない場合でも、リストに入れないといけない）は、最初に存在した1つが採用される。

さっきのスキーマの例でいくと、d1スキーマがカレントスキーマの間は noschemad1 クラスを探して存在せず、 schema クラスにフォールバックする。
カレントスキーマが存在しない間は noschema クラスが存在するのでそっちを採用する。

というわけで、prompt.jsonをこんな風にしてみた。

{
  "classes":
  {
    "schema_mysql":
    {
      "text": "[[ !!SYSTEM_SCHEMA!! ]]",
      "fg": "white",
      "bg": "red"
    },
    "noschema":
    {
      "text": "[[ no database selected ]]"
    },
    "default_schema":
    {
      "text": "[[ %schema% ]]",
      "fg": "#b0c4de"
    }
  },
  "segments":
  [
    { "classes": ["noschema%schema%", "schema_%schema%", "default_schema"] }
  ],
  "prompt": { "text": "> " }
}

まあ取り敢えず、思ったようには動いているようだ( %schema% が空の時は “noschema” にマッチ、 mysqlの時は “schema_mysql” にマッチ、それ以外の時は “default_schema” にフォールバック）

variablesが match, if_true, if_false の2分割しかできないから、3つ以上の分岐をしたい時にはこっちのが良いのかな、と思いつつ。

• 1つのテーブルをdb1からdb2に移動する
  • テーブルは数百GB～1桁TB、「メンテに入れてエクスポート」の手が取れないくらいを想定
  • またこのテーブルをレプリケーションする手も取れないとする
• テーブルにはauto_incrementなPrimary Key、それとは別にUnique Keyがあるが、Foreign Keyはないものとする
  • FKあるとだいたい外に括り出すわけにいかないし
• テーブルにはINSERTもUPDATEもDELETEも来るが、updated_at的なものはない

まずdb2に、オリジナルのテーブルと同じ空っぽのテーブルを作る。

CREATE TABLE `some_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `something_hash` varchar(32) COLLATE utf8mb4_ja_0900_as_cs NOT NULL,
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `something_hash` (`something_hash`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_cs;

アプリのこのテーブルからの検索部分を「db2から引く、なければdb1から引く」に変更。

my $dbh_old= DBI->connect("dbi:mysql:;host=db1");
my $dbh_new= DBI->connect("dbi:mysql:;host=db2");

my $ret= $dbh_new->selectrow_hashref("SELECT .. FROM some_table WHERE ..");
$ret= $dbh_old->selectrow_hashref("SELECT ..") if (!($ret));

この時点ではdb2のテーブルは空なので、常にdb1から引く。結果はかわらない。

次にINSERTを切り替える。書くときは両方に書く（でないとUNIQUE制約が満たせるかどうかがわからない）

$dbh_new->begin_work;
$dbh_old->begin_work;

eval
{
  $dbh_new->do("INSERT INTO some_table ..");
  $dbn_old->do("INSERT INTO some_table ..");
};

if ($@)
{
  ### 新側でユニーク制約違反、または旧側でユニーク制約違反になればロールバックして、普段のユニーク制約違反の時の処理に突入すれば良い。
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレる
$dbh_old->commit;

コミットする順番は重要で、先にコミットが終わってから次をコミットするまでの間にアプリケーションに何かあるとズレる。
そのため、先にdb2の方をコミットしておくようにすると、「ズレたとしてもdb2の方が正しいはず、そして読むのはdb2が先で、無かった時だけdb1を読みにいく」ようになる。

（ただし後者のコミットが失敗した時点でユーザーにはエラー画面が返りそうなので、エラーになったのに更新されているという嫌な状態であることには間違いはない）

この状態だと、新たにINSERTされたレコードはdb2からだけ読まれるようになるはず。

DELETEの切り替え。これもINSERTと同じように両方にかける。

$dbh_new->begin_work;
$dbh_old->begin_work;

eval
{
  $dbh_new->do("DELETE FROM some_table ..");
  $dbn_old->do("DELETE FROM some_table ..");
};

if ($@)
{
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレる
$dbh_old->commit;

これはnew, oldどっちを先に消しても、「何か起こるとズレる」ポイントで何か起こると消えてないように見える。まあ、ユーザーにはエラー画面が返っているはずなので自然ではある。よし。
（どちらか片方でも残っていれば、db2を読んで返すかdb2を空振りしてdb1を読んで返すかしちゃうから）

UPDATE。もともとのUPDATE文から INSERT INTO .. VALUES .. ON DUPLICATE KEY UPDATE .. に書き換える。

後から「ゆっくり更新されなかったデータをマージする」の処理（後述）が必要になるけれど、その時単に両方にUPDATEをかけるだけだと、「既に移動済みとマークされたレコードの重複判定が効かない」から。

mysql80 16> SELECT * FROM some_table;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  1 |       1 | b              | 2022-03-13 15:56:42 |
|  2 |       2 | c              | 2022-03-13 15:57:35 |
+----+---------+----------------+---------------------+
2 rows in set (0.00 sec)

1インスタンスにある間、このレコードに対する UPDATE some_table SET something_hash = 'b' WHERE user_id = 2 は転けるので、id = 1がdb1, id = 2がdb2と分かれてしまった時でもコイツを保証してやりたい。

INSERT some_table VALUES(NULL, 2, 'b', NOW()) ON DUPLICATE KEY UPDATE something_hash = 'b' なら

### db1側
mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  1 |       1 | b              | 2022-03-13 15:56:42 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

mysql80 16> INSERT t2 VALUES(NULL, 2, 'b', NOW()) ON DUPLICATE KEY UPDATE something_hash = 'b';
Query OK, 0 rows affected (0.00 sec)    <-- エラーにはならないけど 0 rows affectedで成功しなかったことはわかる。

mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  1 |       1 | b              | 2022-03-13 15:56:42 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

### db2側
mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  2 |       2 | c              | 2022-03-13 15:57:35 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

mysql80 16> INSERT t2 VALUES(NULL, 2, 'b', NOW()) ON DUPLICATE KEY UPDATE something_hash = 'b';
Query OK, 2 rows affected (0.00 sec)   <-- non-zero rows affected

mysql80 16> SELECT * FROM t2;
+----+---------+----------------+---------------------+
| id | user_id | something_hash | create_at           |
+----+---------+----------------+---------------------+
|  2 |       2 | b              | 2022-03-13 15:57:35 |
+----+---------+----------------+---------------------+
1 row in set (0.00 sec)

とまだ区別がつく。
UPDATE2つだと、db1側は空振りするだけで「カブったからダメ」なのかどうかの区別がつかないのでこの INSERT INTO .. ON DUPLICATE KEY UPDATE .. が良いんじゃないかなと思う。

ちなみに REPLACE INTO .. に書き換えようとすると、複数のユニーク制約に同時にカブった時に容赦なく消される（ INSERT INTO .. ON DUPLICATE KEY UPDATE .. は複数のユニーク制約に同時にカブるとエラーになる）ので期待と違う動きになるはず。

$dbh_new->begin_work;
$dbh_old->begin_work;
my ($new_affected_rows, $old_affectd_rows);

eval
{
  $new_affected_rows= $dbh_new->do("INSERT INTO some_table .. ON DUPLICATE KEY UPDATE ..");
  $old_affected_rows= $dbn_old->do("INSERT INTO some_table .. ON DUPLICATE KEY UPDATE ..");
};

### affected_rowsが0だったらユニーク制約違反だと思うのでロールバック
if ($@ || $new_affected_rows == 0 || $old_affected_rows == 0)
{
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレる
$dbh_old->commit;

このパターンで「何か起こるとズレる」ポイントはあまり放置できない。ユニークな要素が書き変わっている可能性があるので、db1とdb2の間で上手くリカバってやらないとおかしなことになるはず。

色々考えてみたけれど、「commitの代わりにkafkaに積む」くらいしか思いつかなかった。

最後に、INSERTもUPDATEもDELETEもされずにそのまま放っておくとdb1にしかレコードがないやつをdb2に動かすバッチ。
これは単に INSERT INTO .. (NOT IGNORE)とかで移して、移したあとに消せば良いと思う。

$dbh_new->begin_work;
$dbh_old->begin_work;

my $buff= $dbh_old->fetchall_arrayref("SELECT * FROM some_table WHERE user_id BETWEEN ? AND ?");

eval
{
  $dbh_new->do("INSERT INTO some_table ..");
  $dbn_old->do("DELETE FROM some_table ..");
};

if ($@ && $dbh_new->{mysqld_errno} == 1062 && $dbh_new->{mysql_error} =~ /for key 'PRIMARY'/)   ### ここのエラーメッセージは実物と違うかも
{
  ### Error: 1062がどのインデックスでぶつかってエラーになったのかはハンドリングが必要だと思う。
  $dbh_new->rollback;
  $dbh_old->rollback;
}

$dbh_new->commit;
### ここでAppに何か起こるとズレるが、次に同じ処理をした時にdb2を優先して転ぶだけなのでリトライすれば済むはず
$dbh_old->commit;

• db2にINSERTしたやつはdb1で消す、SELECTはdb2で読めれば良いので問題ない。
• db2にレコードがあってdb1にもレコードがあったやつは、INSERTかUPDATEでdb2にもINSERTされているやつなので、db2のレコードを優先してやれば良い
  • UPDATEから書き換えたINSERT .. ON DUPLICATE KEY UPDATEに限っては、「何か起こるとズレる」ポイントで何か起こっていた場合「ユニーク制約に引っかかってエラーになる」可能性があるので、IGNOREはせずに丁寧にハンドリングしてやった方が良さげ
• db1にレコードがないやつはdb2からもDELETEで消えているはず、消えていないにしてもユーザーには「エラーが返って消えていない」なので、db2にあってdb1に無いパターンは気にしない。
• UPDATEをINSERT INTO .. ON DUPLICATE KEY UPDATEに書き換えている関係で、この移動バッチが終わってもdb1側にレコードが残っていることは予想できる。
  • とはいえそのuser_idを処理し終えていることが確実なら無視して良いレコードなので、バッチがクラッシュせずに完走すれば無視して良い。

とか考えたんですがどうでしょう？