🤢

MySQLのsql_safe_updatesについて分かっておらず軽く事故を起こしたので整理

に公開
MySQL
tech

はじめに

MySQLのsql_safe_updatesはデフォルトではOFFみたいなので、このような事象に遭遇する人はもしかしたら少ないのかもしれません。自分の環境下ではONになっており、sql_safe_updatesの理解が浅かったため挙動について整理しました。

背景

外部キー、および外部キーを含む複合主キーを設定しているテーブルがあったのですが、諸事情により複合主キーをやめて、別でidカラムのようなサロゲートキーを用意することにしました。

起きたこと

順次、ALTER TABLE文を実行していったのですが、複合主キーを外したタイミング以降でとあるSQLがエラーになることを確認しました。実際のMySQL側のエラーログは以下です

internal: Error 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

確認したこと

MySQLには sql_safe_updatesというシステム変数があるらしく、デフォルトはOFFみたいです。

この変数を有効にすると、WHERE 句または LIMIT 句でキーを使用しない UPDATE および DELETE ステートメントでエラーが発生します。 これにより、キーが正しく使用されず、多くの行が変更または削除される可能性がある UPDATE および DELETE ステートメントを捕捉できます。 デフォルト値は OFF です。

https://dev.mysql.com/doc/refman/8.0/ja/server-system-variables.html

今回自分の環境でもONになっておりました。

mysql> SELECT @@sql_safe_updates;
+--------------------+
| @@sql_safe_updates |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.02 sec)

DELETE文でWHERE句で外部キーを指定し、複数レコードを削除するようにアプリケーションを作っており動いていました。

まず最初に複合主キーの削除を試みましたがエラーになり、先に複合主キーに含まれる外部キーを削除する必要があったので、外部キーを削除しました。このタイミングではDELETE文は成功します。

次に複合主キーを削除することで、一時的にkey columnが何も存在しないテーブルに変わってしまい、sql_safe_updatesがONになっていたため、key columnがWHERE句に含まれないUPDATE文やDELETE文が失敗するよう挙動が変わってしまいました。

最終的なテーブル定義としては外部キーや主キーを用意する想定でしたが、
一時的にでもkey columnがなくなる場合にアプリケーション側の処理が失敗するようになってしまうので、作業タイミングや作業手順には、より慎重に気を配り、気をつけたいと思いました。
状況によってはsql_safe_updatesを一時的にOFFすることも検討余地に入るかなと思います。

実際の手順

  • 外部キーと複合主キーを貼っているテーブル
  • 複合主キーに外部キーも含まれていた
  • 先に外部キーを削除しないと、複合主キーの削除はできなかったので、先に外部キーを削除
  • 複合主キーを削除
  • （このタイミングでDELETE, UPDATE文が失敗する）
  • 外部キーを貼り直す
  • (このタイミングで外部キーを戻しているので、DELETE, UPDATE文は成功する)
  • サロゲートキーを用意する

戒め投稿です 🙏🙏

最後に

ナチュラルキーに対して複合主キーを設定すると、業務変更時に影響が及ぶ範囲が大きくなるので、今回のケースだとサロゲートキーを主キーとしつつ、ナチュラルキーに対してユニーク制約を設定するのがよかったのかもしれない。

補足

DB設計指南書でも触れられているが、やはり「ERモデルの手戻り」は代償が高くつく。
サロゲートキー(代理キー)を採用すると主目的とは違う恩恵だが、SQLがシンプルになりはする。ただ、一般的な原則としては、極力のサロゲートキーの使用は避けて、ナチュラルキーによる解決を図るべき。その主な理由は、サロゲートキーがそもそも論理的には不要なキーのため、論理モデルを分かりにくくしてしまうから。（要はバランスなのかなぁ

Discussion