foreign_key_checksの挙動を知る

この記事はMySQL Advent Calendar 2020 - Qiitaの8日目の記事です。昨日はmita2さんの MySQL 8.0 でも utf8mb4_general_ci を使い続けたい僕らは - mita2 database lifeでした。

実は今まで、外部キー制約をガッツリ使ったサービスを担当したことなくて、「親子関係のテーブルだ」くらいにしか思ってなかったんですが、いざ触ってみるといろいろ知らないことがあったのでこれを機会に調べてみました。調べているとsysytem変数にforeign_key_checksというものがあるらしく、これを無効にすると、全体的に外部キー制約が無効になるらしいですね。なのでforeign_key_checksをON/OFFしながら色々実行してみます。環境はCentOS7、MySQL8.0.21でInnoDB前提です。なお利用するテーブルは下記

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `user_id` int NOT NULL,
  `name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `user_id` int DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `t1_ibfk_1` (`user_id`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

SET GLOBAL/SESSION

foreign_key_checksはGLOBAL/SESSIONともに利用できるようです。 foreign_key_checks=0をSESSIONで実行したときはそのセッションのみが外部キー参照を無効にできます。

mysql1> SELECT @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)
mysql1> insert into t1 values (1,1, now());
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`reference_test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

mysql2> SET SESSION foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql2> insert into t1 values (1,1, now());
Query OK, 1 row affected (0.01 sec)

親テーブルでトランザクションを開始してSELECTすると子テーブルがDROPできない

たとえ、foreign_key_checks=0にしてもトランザクションを開始して親テーブル(user)を参照している状態でもう一方のコネクションで子テーブル（t1）を削除しようとしても待たされます。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
//親テーブルを参照
mysql> SELECT * FROM user;
Empty set (0.00 sec)

mysql> SET SESSION foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE t1;
## ここで待たされる

SHOW PROCESSLISTで確認するとメタデータロック待ちっぽいですね。もちろん親テーブル側のトランザクションが終了すると削除されるます。

> show processlist;
+--------+-----------------+-----------+----------------+---------+--------+---------------------------------+------------------+
| Id     | User            | Host      | db             | Command | Time   | State                           | Info             |
+--------+-----------------+-----------+----------------+---------+--------+---------------------------------+------------------+
|      5 | event_scheduler | localhost | NULL           | Daemon  | 530894 | Waiting on empty queue          | NULL             |
| 100172 | root            | localhost | reference_test | Sleep   |    391 |                                 | NULL             |
| 100173 | root            | localhost | reference_test | Query   |     34 | Waiting for table metadata lock | DROP TABLE t1    |
| 100178 | root            | localhost | NULL           | Query   |      0 | starting                        | show processlist |
+--------+-----------------+-----------+----------------+---------+--------+---------------------------------+------------------+
4 rows in set (0.00 sec)

なお子テーブルにINDEXの追加やtruncateは問題なくできました。

子テーブルでトランザクションが開始されていると親テーブルにINDEXが追加できない

foreign_key_checks=0で子テーブル(t1)をトランザクションを開始して参照している状態で親テーブル(user)に外部キー参照をされていないカラムをINDEX追加しようとしてもMDLになります。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
// 子テーブルを参照
mysql> SELECT * FROM t1;
+----+---------+---------------------+
| id | user_id | updated             |
+----+---------+---------------------+
|  1 |       1 | 2020-12-08 01:55:52 |
+----+---------+---------------------+
1 row in set (0.00 sec)

mysql> SET SESSION foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE user ADD index idx_name(name);
ここで待たされる

ちなみにuserテーブルに対してALTER TABLE/DROP TABLEともにできなかったのですが、TURNCATEはできました。この挙動、実は以前は出くわしてこれをきっかけに障害を起こしてしまったことあります…関係ないカラムなのに…と思い、何が起きたのかわからなくてyoku0825さんに助けてもらったのはいい思い出です。

まとめ

foreign_key_checksは外部キーを無効/有効にすることができるシステム変数ですが、そういうもんだと思って利用すると意外なところではまったりするかもしれないですね。このあたり挙動を理解して運用していく必要がありそうです。

参考： https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_foreign_key_checks

日々の覚書: MySQL 8.0 vs 外部キー制約 vs ALTER TABLEでメタデータロック待ちになったら疑うこと

そろばんのしょ（第3版第2刷）

雑魚い見習いDBAの日々学んだことや、どーでもいいことを。基本は自分の備忘録。

foreign_key_checksの挙動を知る

foreign_key_checksの挙動を知る

SET GLOBAL/SESSION

親テーブルでトランザクションを開始してSELECTすると子テーブルがDROPできない

子テーブルでトランザクションが開始されていると親テーブルにINDEXが追加できない

まとめ