目次

• 目次
• とある日
• 重要な変更
• 検証
  • MySQL 8.0.26
  • MySQL 8.0.27
• 解説
  • …

PHP Conference Japan 2021 に参加しました。

参加といっても家族との兼ね合いだったりで、正直「自分のセッション以外はほぼほぼ不参加」でした(´･ω･｀)

「オンラインxx」の「xx」が終わった瞬間に現実(?)に引き戻される感があるので、来年はオフラインでやれる状態になっていると良いですね。。

----

最初は応募しよう応募しようと思いつつ、 転職 もしたばっかで案外忙しい…と諦めていたんですが、アディショナルタイムのツイートを見ていてもたってもいられなくなり、結局応募しました。

…

TL;DR

• リテラルと比較する条件のこと
  • INNER JOINならONでもWHEREでもどっちでも同じ結果になる、マッチしない行は影も形も出てこない
  • OUTER JOINならONに書いた時は「それにマッチしなければNULL詰めの行が返る」、WHEREに書いた場合は「それにマッチしない行は表示されない(= INNER JOINと一緒)」
• LEFT JOINの左右どちらのテーブルでもこうなるのはちょっと面白い(?)
  • ゆっくり考えればわかるんだけど、2度見してしまった。。

CREATE TABLE t1 (num int);
INSERT INTO t1 VALUES (1), (2), (3);

-- INNER JOIN で WHERE
SELECT * FROM t1 JOIN t1 AS t2
ON t1.num = t2.num
WHERE t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
| …

TL;DR

• 負荷をかけながらフェイルオーバーテストをするなら、負荷クライアント側で「どの書き込みが成功したのか」のログは必ず取っておく
  • でないと、フェイルオーバー起因でデータロストが発生するのかしないのかのチェックができない

フェイルオーバーシナリオ

スイッチオーバー(手動での切り替え)を含めてざっと思いつくのはこれくらい。

1. スイッチオーバー
2. mysqldの正常終了
3. mysqldの異常終了、特に、mysqld_safeやsystemdがmysqldを再起動させてしまう環境
4. mysqldのハングアップ
5. カーネルパニック
6. ファイルシステムのハングアップ
7. 電プチ

スイッチオーバー

…

他にも出る要因はあるはずだけど、今回は「トランザクション中に SET GLOBAL read_only = 1 」をやろうとして出た。
珍しかったので記念パピコ。

mysql80 25> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql80 25> SET GLOBAL read_only = 1;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

実際には BEGIN で始めたわけじゃなくて、迂闊に autocommit = 0を指定したら出た。

​

TL;DR

• レプリカにだけgenerated columnを足す構成 + binlog_format = ‘ROW’ かつgenerated columnを使ったインデックスがあると、そのインデックスの変更はレプリカでリプレイされない(インデックス上はgenerated columnのデータ型の暗黙のデフォルトになったり、リーフの数がそもそもおかしくなったり)

• インデックス再作成、 ALTER TABLE .. ENGINE = InnoDB でも望む状態にはならなかった。

  • generated columnを削除して再作成も絡めないとダメ

5.7.27のレプリケーション構成を作って、マスターにテキトーなテーブルを作る。 binlog_format, binlog_row_image はデフォルトのまま。

$ make_replication_sandbox …

TL;DR

• MySQL 5.6とそれ以前のはなし。5.7とそれ以降はこの初期設定は入っていない。

  • MySQL 5.6でもインストール方法によっては存在しないし mysql_secure_installation を使った場合はこの設定は消されるし、MySQL 5.7とそれ以降だろうと5.6からインプレースアップグレードやmysqlスキーマまで含めたフルリストアでアップグレードした場合は設定は残っているだろう

• 昔のMySQLは test というスキーマを mysql_install_db で作ってしまって、しかもこのスキーマは全アカウントに対して(ストアド以外の)読み書き権限がある

  • …

TL;DR

• NULLが入っているカラムを後から NOT NULL にしようとすると出る
• 何故出るかというと、 INSERT IGNORE INTOがNOT NULL DEFAULTを裏切る から敢えて SHOW WARNINGS を拾ってエラーにしている
  • 裏切られるのを承知の上で強行するなら pt-online-schema-change --null-to-not-null でいける
• UPDATE t1 SET val = ? WHERE val IS NULL でNULLを排除してからpt-online-schema-changeすればいいんじゃないかな

日々の覚書: INSERT IGNORE INTOがNOT NULL DEFAULTを裏切る の続き。

pt-oscは INSERT …

MySQL で max_allowed_packet を超過した場合にどうなるんだっけ…と思って試してみた結果。

サーバーの max_allowed_packet をクエリが超過した場合

MySQL 5.6

サーバー起動

% docker run --name mysql56 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql:5.6 --max-allowed-packet=100000

クライアントは原因がわからないけど切断される

% ruby -e 'puts "set @a="+"1"*1000000' | docker exec -i mysql56 mysql
ERROR 2006 (HY000) at line 1: MySQL server has gone away

サーバー側はログ出力なし

% docker logs mysql56
...

MySQL 5.7

サーバー起動

% docker run --name mysql57 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d mysql:5.7 --max-allowed-packet=100000

クライアントは原因がわからないけど切断される

% ruby -e 'puts "set @a="+"1"*1000000' | docker exec -i …

とりあえず8.0.26で実験する。

INSERT IGNORE INTO といえば、キー重複エラーを握りつぶして複数のINSERTを「先勝ち」であるかのように振る舞わせるのに使われることが多いやつ。

mysql80 10> CREATE TABLE t2 (pk int PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)

mysql80 10> INSERT INTO t2 VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql80 10> INSERT INTO t2 VALUES (1);
ERROR 1062 (23000): Duplicate entry '1' for key 't2.PRIMARY'

mysql80 10> INSERT IGNORE INTO t2 VALUES (1);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql80 10> SELECT * FROM t2;
+----+
| pk |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

あるいは、あたかも sql_mode= STRICT_TRANS_TABLES …