2021/10/05

PHPカンファレンス2021に参加しました…のポエム

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


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


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


----


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



俺がしゃべれることといえば当然MySQLの話しかないわけですが、ここしばらく(?)「俺がWebApp開発しているような人に伝えられることって何だろう」と考えて(かつ、現実的に俺がちゃんと資料を作れる範囲で)これにしようと決めました。



ちなみに「もうギリギリ」は応募締め切りギリギリ(アディショナルタイム期間だったので)ってことです。


31もスターをいただけて、無事採択されました。

つけていただいた皆様、ありがとうございます。





ところがどっこい(?) ふと気になるセッションを見つけます。




Σ(゚д゚lll) ダダ被りの予感!?




ちなみに、出来上がったのは発表前日の10/2(土)だったので、まみーさんにはドラフトしか見てもらっていません(それも9/30(木) だった。まみーさんのは先に見せてもらってた…

なお、途中で気が変わった(?)のか、スローログの話は結局しませんでした。でもEXPLAINとインデックスに絞ったおかげで、MySQLとPHPのいい感じの話になれたな、と結果オーライな気がしています。


出来上がりはこんな感じです。



モノは 日々の覚書: サイボウズさんの開運研修(データベース)で話してきました の時の「MySQLとインデックスと私」をもうちょっと(?)カジュアルにしたものです。


カバリングインデックスやネクストキーロックの話題を捨てて、その分「PHPのサンプルコードっぽいものでインデックスの刈り込み方をイメージしてもらえる」ようにしています。


前日までは130ページ以上あった(練習問題があと3パターンくらいあった)んですが、到底時間に収まらなかったので最後のリハーサルで切り詰めました。ぴったり60分でした。よかった。


ご笑覧いただければ幸いです!

2021/10/04

LEFT JOINのONに書くかWHEREに書くかで結果セットが変わるやつのサンプル

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  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- INNER JOIN で ON
SELECT * FROM t1 JOIN t1 AS t2
ON t1.num = t2.num AND t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- LEFT JOIN の右側で WHERE
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num
WHERE t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- LEFT JOIN の右側で ON
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num AND t2.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    2 | NULL |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

-- LEFT JOIN の左側で WHERE
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num
WHERE t1.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

-- LEFT JOIN の左側で ON
SELECT * FROM t1 LEFT JOIN t1 AS t2
ON t1.num = t2.num AND t1.num IN (1, 3);
+------+------+
| num  | num  |
+------+------+
|    1 |    1 |
|    2 | NULL |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

2021/09/24

「MySQLのフェイルオーバーテストをする」と聞いてぼんやり思ったこと

TL;DR

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

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

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

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

スイッチオーバー

たぶんHAソリューションを作る時にちゃんとテストするからこれはそんなに問題にならない気がするけれど、(レプリケーションベースのソリューションの場合)「レプリケーション遅延が起こってる時のスイッチオーバー」で何が起こるかは観測した方が良い気がする。


概ね、「レプリカが追いつくまでスイッチオーバーが遅延する」か「データをロストしながら無理矢理スイッチオーバーしてしまう」のどちらかに属する気がする。

理想は、「一定以上のレプリカ遅延だったら、稼働系が死んでいるわけではないスイッチオーバーは棄却する」のが良いんじゃないかな。

mysqldの正常終了

これもテストされていると思う。HAの監視が綺麗にECONNREFUSED(111)でスパッと転けるし、絶対に旧系に更新は成功しなくなるから大丈夫系。
ただし、 innodb_fast_shutdown = 0だったり、忙しいMySQLだったりでダーティーページが多かったりすると案外正常終了に時間がかかることもある(それでも先に書き込みできなくなるし接続もできなくなるから大丈夫だと思うけれど)


正常終了したmysqldは上位プロセスによって勝手によみがえることは無いので簡単。

mysqldの異常終了、自動再起動ナシ

バグによるsignal 11(コアダンプを伴う), データ破損によるsignal 6(コアダンプを伴う), OOM Killerによるsignal 9(コアダンプはしない)あたりのパターン。再現させるには kill -6 $(pidof mysqld) でいいと思う。


書き込みは失敗するし勝手に蘇生はしてこないけれど、レプリケーション遅延があった場合はそのぶんはおそらくロストする。そして昇格側でロストを許容してサービスを再開した場合、降格側ではそのデータのまま再組み込みはできない(データが新旧でズレるから)、必ずリストアが必要になるはず。


gtid_executedをちゃんと比較する(旧側だけが持っているGTIDが無ければそのまま戻しても良いと考えられる……ちゃんとsync_binlog=1なら)手順を確立しておくこと、旧側のバイナリログ保全の手順を確立しておくこと、あたりがキモだろうか。semisyncを使っている場合、「旧側が受け付けた更新だけど新側にはあって旧側ではクラッシュリカバリでチョップされる」ことがあり得るので注意。


新側が稼働を開始し始めてから旧側のデータを新側にも伝搬するのはなかなか厳しいものがある…というのは、auto_incrementをガンガン使っているテーブルに書き込みながらこれをやると簡単にわかって良い気がする。仮にauto_incrementだけずらして補完しようとしても、ちゃんとユニークキーが振ってないと「本来1人1レコードのはずが何故か2行ある」みたいなことが起こる。ユニークキー大事。

mysqldの異常終了、自動再起動アリ

コアを吐くか吐かないか、コアを吐くにしてもどれくらいのサイズを吐くかでちょっと変わる(コアファイルを吐き切るまではmysqldプロセスは落ち切らず、mysqldプロセスが落ちきらないと上位プロセスからのmysqld再起動は働かない)

上手く(即死してHAがトリガーされる前にmysqldがクラッシュリカバリまで終わらせて正常復帰)すれば、クラッシュリカバリが終わったそのままの状態で系が復帰できるかも知れない(レプリカ遅延していたとしても、蘇ったmysqldが残りをレプリカに再送できる)。


mysqldが死んだだけでOSが元気ならデータロストの可能性は低い…が、semisyncでbinlogが先行でレプリカに行っている可能性があるのは相変わらず。結局gtid_executedをちゃんと比較しないといけないので手間はそんなに変わらない。

コアの書き出しなどに時間がかかってフェイルオーバーが走る場合、その後旧のmysqldがHA上でどう扱われるのかは確認しておいた方が良さげ。

mysqldの異常終了、自動再起動アリ、データ破損のワーストケース

異常終了最悪パターン。ibdファイルの一部が壊れていて、「そのページに触れた時に何度でも異常終了する」みたいなパターン。
下手するといつまでも壊れたibdを抱えたMySQLが稼働系のままになるので死ねる。再起動をカウントして諦めさせる仕組みが…とか考えるくらいだったら再起動しないような設定にする方が素直だと思った。

mysqldのハングアップ

3306でTCPのACKは返すけど、MySQLプロトコルとしては何もしゃべれないパターン。
gdb -p $(pidof mysqld) でアタッチしてほっとくと再現できる。
シンプルな3306/TCPのポーリングだけだとコイツを検出できないので、ちゃんとMySQLプロトコルをしゃべるタイムアウトつきの死活監視をしようね、って思えるやつ(そして書いたやつが これ だ…)


ちゃんとフェイルオーバーが走るかは最低限チェックするとして、kill -9などで止めを刺すか、ハコをヘッドショットするか、頻度は低いはずだから自動化せずに都度人力で解決するか。

カーネルパニック

カーネルパニックでリブートする設定ならほぼほぼここまでの異常系に含められる気がする。リブートしない場合、何故か3306のタイムアウトに想定より時間がかかったりするので確認したい。


再現するには echo c > /proc/sysrq-trigger (rootで

ファイルシステムハング

書き込みやバッファプールからあふれるSELECTは通らないけど、バッファプールに載っていて.ibdにアクセスしなくてよいSELECTは通ったり、それでもスローログに書こうとするとそこで止まったりと兎に角不思議なことが良く起こるやつ。


xfs_freeze とかで再現させている。俺は諦めている(たぶん、ヘッドショットでハコごと落とすのが無難)

電プチ

たぶんフェイルオーバー自体は問題ないが、設定によってはデータをロストするかも知れないのでそのテスト。
ここでクライアントがどこまで書き込みに成功していたかのログが重要になる。成功したものは全て残っていて、失敗したものは全て失われていないといけない。が、semisyncだと略。
ちなみに shutdown -h now とかは生きているプロセスにsignal 15を投げちゃうので、 echo b > /proc/sysrq-trigger とかでブチっとやってる。

2021/09/22

MY-001192 Can't execute the given command because you have active locked tables or an active transaction に出会った

他にも出る要因はあるはずだけど、今回は「トランザクション中に 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を指定したら出た。

2021/09/16

レプリカにだけgenerated columnを追加したらセカンダリインデックスが更新されなかった

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 --how_many_slaves=1 5.7.27

$ ./m

master [localhost] {msandbox} ((none)) > CREATE DATABASE d1;
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} ((none)) > CREATE TABLE d1.t1 (num INT PRIMARY KEY, val varchar(32));
Query OK, 0 rows affected (0.02 sec)

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (1, 'one'), (2, 'two');                                                                     Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.01 sec)

master [localhost] {msandbox} ((none)) > SELECT @@binlog_row_image;
+--------------------+
| @@binlog_row_image |
+--------------------+
| FULL               |
+--------------------+
1 row in set (0.00 sec)

レプリカの側に、generated columnを作る。binlog_format= ROWでも壊れないようにテーブルの末尾に。generateの式はテキトー。

$ ./s1

slave1 [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.01 sec)

slave1 [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD odd_even ENUM('even', 'odd') AS (CASE num % 2 WHEN 0 THEN 'even' WHEN 1 THEN 'odd' END) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

slave1 [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;                          +-----+------+----------+
| num | val  | odd_even |
+-----+------+----------+
|   1 | one  | odd      |
|   2 | two  | even     |
+-----+------+----------+
2 rows in set (0.01 sec)

slave1 [localhost] {msandbox} ((none)) > SHOW CREATE TABLE d1.t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int(11) NOT NULL,
  `val` varchar(32) DEFAULT NULL,
  `odd_even` enum('even','odd') GENERATED ALWAYS AS ((case (`num` % 2) when 0 then 'even' when 1 then 'odd' end)) VIRTUAL NOT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

これでマスターにINSERTすると

master [localhost] {msandbox} ((none)) > INSERT INTO d1.t1 VALUES (3, 'three');
Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)

レプリカでもちゃんと見える。ここまではOK。

slave1 [localhost] {msandbox} ((none)) > SELECT * FROM d1.t1;
+-----+-------+----------+
| num | val   | odd_even |
+-----+-------+----------+
|   1 | one   | odd      |
|   2 | two   | even     |
|   3 | three | odd      |
+-----+-------+----------+
3 rows in set (0.00 sec)

ではレプリカにこのgenerated columnを使ったインデックスを作る。

slave1 [localhost] {msandbox} ((none)) > ALTER TABLE d1.t1 ADD KEY (odd_even);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

slave1 [localhost] {msandbox} ((none)) > SELECT odd_even, COUNT(*) FROM d1.t1 GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |        1 |
| odd      |        2 |
+----------+----------+
2 rows in set (0.01 sec)

そしておもむろにマスターに100万行くらい突っ込む。

master [localhost] {msandbox} ((none)) > LOAD DATA LOCAL INFILE '/tmp/md5' INTO TABLE d1.t1;
Query OK, 999991 rows affected (4.69 sec)
Records: 999991  Deleted: 0  Skipped: 0  Warnings: 0

master [localhost] {msandbox} ((none)) > SELECT case (`num` % 2) when 0 then 'even' when 1 then 'odd' end AS odd_even, COUNT(*) FROM d1.t1 GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |   499997 |
| odd      |   499997 |
+----------+----------+
2 rows in set (0.68 sec)

レプリカではこう。

slave1 [localhost] {msandbox} ((none)) > SELECT odd_even, COUNT(*) FROM d1.t1 GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |   999992 |
| odd      |        2 |
+----------+----------+
2 rows in set (0.25 sec)

おそらく想像がつくように、このgenerated columnに触らないインデックスを使わせると正しく出る。

slave1 [localhost] {msandbox} ((none)) > SELECT odd_even, COUNT(*) FROM d1.t1 USE INDEX(PRIMARY) GROUP BY odd_even;
+----------+----------+
| odd_even | COUNT(*) |
+----------+----------+
| even     |   499997 |
| odd      |   499997 |
+----------+----------+
2 rows in set (0.80 sec)

STOREDなgenerated columnはトリガーと一緒でマスターでのみ評価されるから、STOREDなgenerated columnをレプリカにだけ追加すると値がおかしくなるのはどっかで見たことがあったんだけど、VIRTUALでもセカンダリーインデックス使ってるとこうなるのね…。残念至極。

2021/09/15

MySQL 5.6とそれ以前は「testデータベースを消せば良いってもんじゃない」というはなし

TL;DR

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

    • MySQL 5.6でもインストール方法によっては存在しないし mysql_secure_installation を使った場合はこの設定は消されるし、MySQL 5.7とそれ以降だろうと5.6からインプレースアップグレードやmysqlスキーマまで含めたフルリストアでアップグレードした場合は設定は残っているだろう
  • 昔のMySQLは test というスキーマを mysql_install_db で作ってしまって、しかもこのスキーマは全アカウントに対して(ストアド以外の)読み書き権限がある

    • GRANT USAGE しかないアカウントでも平気で CREATE TABLEINSERT も一通りできる

    • ついでに言うと test スキーマだけじゃなくて test_1 スキーマにも同じことができる。 test_* のパターンを持つスキーマなら全部そう

  • DROP DATABASE test したところで、誰にでも CREATE DATABASE test する権限があるので、そんなことより権限を剥がす方が先

    • DELETE mysql.db WHERE user = '' AND host = '%'; FLUSH PRIVILEGES; かな

MySQL 5.6は2021/2/1で EOL になっているので、そもそも使わない方が良いだろうし、今日日の5.6とそれ以前限定の話をするのもどうかなとは思っている。が、アップグレードの仕方によってはこの設定は残っているので注意。

取り敢えず、5.6.51の吊るしの mysql_install_db でdatadirを初期化する。


$ ./scripts/mysql_install_db --datadir=/usr/mysql/5.6.51/data

Installing MySQL system tables...2021-09-15 15:11:03 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2021-09-15 15:11:03 0 [Note] ./bin/mysqld (mysqld 5.6.51-log) starting as process 4634 ...

OK

Filling help tables...2021-09-15 15:11:05 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap.

2021-09-15 15:11:05 0 [Note] ./bin/mysqld (mysqld 5.6.51-log) starting as process 4666 ...

OK

..

$ mysql56
mysql56> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

この test スキーマができる。最近5.6とか使わないから久々に見たよ。

mysql56> CREATE USER yoku0825@localhost;
Query OK, 0 rows affected (0.00 sec)

コイツがどう悪いのかを試すために、何も権限を持っていないアカウントを作ってみる。

$ mysql56 -uyoku0825

mysql56> SHOW GRANTS;
+----------------------------------------------+
| Grants for yoku0825@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'yoku0825'@'localhost' |
+----------------------------------------------+
1 row in set (0.01 sec)

mysql56> use test
Database changed

mysql56> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.02 sec)

mysql56> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)

フツーに CREATE TABLE できてしまった。これはいけない?
というか何の権限もないのに、この test スキーマをDROPすることももう一度CREATEすることもできる。

mysql56> DROP DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql56> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql56> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

それどころか、 test_ で始まるスキーマ名なら好きなようにできる。

mysql56> CREATE DATABASE test_yoku0825;
Query OK, 1 row affected (0.00 sec)

mysql56> ALTER DATABASE test_yoku0825 CHARSET utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql56> SHOW CREATE DATABASE test_yoku0825;
+---------------+---------------------------------------------------------------------------+
| Database      | Create Database                                                           |
+---------------+---------------------------------------------------------------------------+
| test_yoku0825 | CREATE DATABASE `test_yoku0825` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+---------------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql56> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| test_yoku0825      |
+--------------------+
3 rows in set (0.00 sec)

まれにだがよく(?) 「 test スキーマあるのいけないんで、DROPしてください」みたいな文面があるが、こんなもの消しても何の意味もなくて、この「SHOW GRANTSでは何も見えない何の権限も持っていないはずのアカウントが test スキーマを好きなようにできる」設定の方を何とかしないといけない。

ちなみにこの権限の根源は mysql.db テーブルにある。rootでログインしなおしてクエリーたたいてみる。

mysql56> SELECT * FROM mysql.db;
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host | Db      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %    | test    |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
| %    | test\_% |      | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | N                  | N            | Y          | Y            |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.00 sec)

空白のuserは「任意のuser」、hostの ‘%’ は任意のホスト。
この2行の設定により、 test スキーマと test_ で始まる任意のスキーマはほとんどのスキーマレベル権限を全アカウントに与えている。

mysql_secure_installation はこの2行を消してから FLUSH PRIVILEGES することでこの権限を消している。

https://github.com/mysql/mysql-server/blob/mysql-5.6.51/scripts/mysql_secure_installation.pl.in#L253

rootで消してから FLUSH PRIVILEGES

mysql56> DELETE FROM mysql.db WHERE user = '' AND host = '%';
Query OK, 2 rows affected (0.00 sec)

mysql56> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

これで期待通りの動きになる。

$ mysql56 -uyoku0825

mysql56> SHOW GRANTS;
+----------------------------------------------+
| Grants for yoku0825@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'yoku0825'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql56> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql56> use test;
ERROR 1044 (42000): Access denied for user 'yoku0825'@'localhost' to database 'test'

ただし、既存のコネクションは use でカレントデータベースを変えるか再接続しないと権限の変更は有効にはならないので、万全を期すならいったん再起動しておくと完璧だと思う。

2021/09/11

pt-online-schema-changeがColumn 'xx' cannot be nullで止まる

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 IGNORE INTO を使ってるはずなのになんでこのメッセージでエラるんだろ? と思ったら、 ↑の裏切りを防ぐために明示的にワーニングを拾ってエラーにエスカレーションしているのであった。

https://github.com/percona/percona-toolkit/blob/v3.3.1/bin/pt-online-schema-change#L11791-L11828

mysql80 121> CREATE TABLE t1 (num int PRIMARY KEY, val varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql80 121> INSERT INTO t1 VALUES (1, 'one'), (2, NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql80 121> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql80 123> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

sql_modeに関係ないことを確認するために、グローバルSQL_MODEを空っぽにしておく。

$ pt-online-schema-change -S /usr/mysql/8.0.26/data/mysql.sock -uroot --alter "MODIFY val varchar(32) NOT NULL DEFAULT 'z'" D=d2,t=t1 --execute
No slaves found.  See --recursion-method if host 150-95-141-50 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `d2`.`t1`...
Creating new table...
Created new table d2._t1_new OK.
Altering new table...
Altered `d2`.`_t1_new` OK.
2021-09-11T00:11:48 Creating triggers...
2021-09-11T00:11:48 Created triggers OK.
2021-09-11T00:11:48 Copying approximately 2 rows...
2021-09-11T00:11:48 Dropping triggers...
2021-09-11T00:11:48 Dropped triggers OK.
2021-09-11T00:11:48 Dropping new table...
2021-09-11T00:11:48 Dropped new table OK.
`d2`.`t1` was not altered.
        (in cleanup) 2021-09-11T00:11:48 Error copying rows from `d2`.`t1` to `d2`.`_t1_new`: 2021-09-11T00:11:48 Copying rows caused a MySQL error 1048:
    Level: Warning
     Code: 1048
  Message: Column 'val' cannot be null
    Query: INSERT LOW_PRIORITY IGNORE INTO `d2`.`_t1_new` (`num`, `val`) SELECT `num`, `val` FROM `d2`.`t1` LOCK IN SHARE MODE /*pt-online-schema-change 5706 copy table*/
2021-09-11T00:11:48 Dropping triggers...
2021-09-11T00:11:48 Dropped triggers OK.
`d2`.`t1` was not altered.

とまあ転けて終わる。
…と、ジェネラルログをよく見てたら、元のsql_modeに関わらずいったんsql_modeを全OFFしていた。

2021-09-11T00:11:48.055972+09:00          132 Query     /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */

まあいっか。

—null-to-not-null オプションをつけると、通る。ただし、DEFAULTに裏切られるので暗黙のフォールバックを受ける。

mysql80 136> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` int NOT NULL,
  `val` varchar(32) NOT NULL DEFAULT 'z',
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql80 136> SELECT * FROM t1;
+-----+-----+
| num | val |
+-----+-----+
|   1 | one |
|   2 |     |
+-----+-----+
2 rows in set (0.00 sec)

これが嫌な場合、 先んじて UPDATE t1 SET val = 'z' WHERE val IS NULL とでもしてNULLをつぶしておくのが正しいと思う。実際にNULLに当たらなければ、 --null-to-not-null をつけなくてもpt-oscは完走する。

ちなみにMySQLのNative ALTER TABLEはsql_modeに影響を受ける。STRICTな時はエラーになるし

mysql80 136> SELECT @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 136> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql80 136> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL DEFAULT 'z';
ERROR 1138 (22004): Invalid use of NULL value

Non-Strictな場合はDEFAULTを裏切ってALTERが走る。

mysql80 136> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql80 136> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL DEFAULT 'z';
Query OK, 2 rows affected, 1 warning (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql80 136> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'val' at row 2 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql80 136> SELECT * FROM t1;
+-----+-----+
| num | val |
+-----+-----+
|   1 | one |
|   2 |     |
+-----+-----+
2 rows in set (0.00 sec)

こっちももちろんNULLに当たらなければSTRICT + ALTER TABLEでもエラーにはならないので、「NULLABLEからNOT NULLにする時はUPDATEでまずNULLを排除しましょう」という極めてフツーの感じになるのであった。

mysql80 136> SELECT @@session.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql80 136> CREATE TABLE t1 (num int PRIMARY KEY, val varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql80 136> INSERT INTO t1 VALUES (1, 'one'), (2, NULL);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql80 136> UPDATE t1 SET val = 'z' WHERE val IS NULL;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql80 136> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | z    |
+-----+------+
2 rows in set (0.00 sec)

mysql80 136> ALTER TABLE t1 MODIFY val varchar(32) NOT NULL DEFAULT 'z';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql80 136> SELECT * FROM t1;
+-----+-----+
| num | val |
+-----+-----+
|   1 | one |
|   2 | z   |
+-----+-----+
2 rows in set (0.00 sec)