SQLAlchemyの外部キー削除漏れが、SQLiteのテストをすり抜けた話

当ページのリンクには広告が含まれています。
SQLAlchemyの外部キー削除漏れが、SQLiteのテストをすり抜けた話
この記事の結論
プロフィール削除の前に依存レコードを手動で列挙して消す実装は、テーブルが増えるたびに追記が必要になり、いつか必ず漏れます。しかもテスト用DBがFK制約を強制しない設定だと、その漏れはテストをすり抜けて本番でだけ露見します。今回は、削除対象をモデル定義から機械的に導く形に直した話です。

お疲れ様です!IT業界で働くアライグマです!

個人開発している「AgentPick」で、プロフィール削除機能が一部のユーザーだけ失敗する不具合がありました。原因は、依存レコードの削除処理をコードに直接手で列挙していたことです。今回はこの不具合の中身と、再発しない形に直すまでの経緯を書きます。

目次

プロフィール削除が、使い方によって失敗する

AgentPickには、利用者がいつでも自分のプロフィールを削除できる機能があります。削除リクエストが来たら、まず紐づく利用履歴のレコードを先に消してから、プロフィール本体を消す、という流れです。ここで消す対象は、いいねした記事の記録、既読記録、おすすめスコアのキャッシュなど、プロフィールに外部キーで紐づく複数のテーブルです。

この「先に依存レコードを消す」処理を、コードの中で対象テーブルを1つずつ手書きで列挙する形で実装していました。実装した当初はそれで問題なく動いていたのですが、後から「興味なし」ボタンの記録用テーブルと、記事メモの記録用テーブルが新しく追加されました。どちらもプロフィールへの外部キーを持つテーブルです。

問題は、この2つの新しいテーブルが、削除処理の手書きリストに追記されないまま残ってしまったことです。結果として、興味なしボタンを1回でも押した、またはメモを1件でも書いたユーザーがプロフィール削除を実行すると、本体を消す直前で外部キー制約違反となり、削除APIがエラーで失敗するようになっていました。サービスを実際によく使っているユーザーほど、確実にこの不具合を踏む状態です。

IT女子 アラ美IT女子 アラ美
使い込んでる人ほど消せないって、けっこう洒落にならないやつじゃない。

ITアライグマITアライグマ
はい。データ削除の要求に応えられていない状態なので、重要度は高めに見ていました。

なぜテストは通っていたのか ─ テストDBはFK制約を強制しない

厄介だったのは、この不具合がテストでは検知できていなかったことです。

削除処理を実装した当時のテストは、「アクティビティがある状態のユーザーを削除して、削除APIが正常に完了すること」を確認するものでした。しかし確認していたのは「削除リクエストが正常応答を返すこと」だけで、「依存レコードが実際に0件になっているか」までは見ていませんでした。

さらに厄介なのが、テスト実行に使っているDBの設定です。本番で使っているMySQLは外部キー制約を強制しますが、テストで使っているSQLiteは、接続ごとに外部キー制約を有効にする設定をしない限り、制約を強制しません。つまり、依存レコードの削除漏れがあっても、テスト環境ではエラーにならずにそのまま通ってしまい、本番の環境でだけ制約違反として顕在化する、という差があったわけです。テストを手軽に回すためにSQLiteを選んでいたぶん、制約の強さが本番と違うことまでは意識できていませんでした。

テストが緑のまま「削除できているつもり」で運用を続け、実際にはユーザーが使うたびに静かに壊れていた、という構図でした。

以前、DBバックアップに自動リストア検証を入れた話でも、テストは書いてあるのに、比較の誤りを捕まえられないデータでしか確かめていませんでした。テストが通ることと、間違いを検出できることは別だと、ここでも同じ形で突き付けられました。

IT女子 アラ美IT女子 アラ美
テストが通ってるのに本番だけ落ちるの、地味に一番タチが悪いやつよね。

ITアライグマITアライグマ
環境ごとの制約の強さが違うと、こういうすり抜け方をするんだと実感しました。

原因は手動列挙リストの追記漏れ(テーブルが増えるたびに再発していた)

改めて経緯を追うと、削除対象のテーブルを手で列挙するコード自体には「依存レコードを先に削除する(制約違反を避けるため)」という意図を示すコメントがちゃんと書かれていました。つまり設計判断自体は最初から正しかったのですが、実装の形が「後から増えたテーブルを、人が思い出して追記する」という前提に依存していたのが問題でした。

プロフィールに外部キーで紐づくテーブルは、機能追加のたびに増えていく類のものです。新しい機能を作るたびに「削除処理のこの部分も直さないといけない」と覚えておく必要がある実装は、機能追加のたびにその場ではチェックリストとして意識されても、時間が経つと忘れられます。実際、今回は2つの機能追加を経て、どちらのタイミングでも削除処理への追記が漏れていました。1回の漏れなら偶然で済みますが、2回続けて同じ種類の漏れが起きたということは、個人のうっかりではなく、実装の構造そのものに再発しやすさが組み込まれていたと考えるべきです。

「手で列挙する」という実装方針そのものが、将来のテーブル追加を必ず取りこぼす設計になっていた、というのが根本の原因でした。

観点 修正前(手動列挙) 修正後(モデル定義から導出)
削除対象の決め方 開発者がテーブル名を手書きリストへ追記 SQLAlchemyのBase.registry.mappersからFK参照を自動収集
テーブル追加時 追記漏れで本番MySQLの外部キー制約違反が発生 モデル定義にFKがあれば自動で削除対象に入る
テストでの検証 削除APIの正常応答コードのみを確認(SQLiteはFK未強制) 削除前後で全依存テーブルのレコードが0件になったことを検証

IT女子 アラ美IT女子 アラ美
1回目はうっかりでも、2回目は仕組みの問題だって言われると納得しかないわね。

ITアライグマITアライグマ
同じ種類のミスが繰り返されたら、直すべきは注意力じゃなくて構造だと考えるようにしています。

モデル定義から依存テーブルを機械的に導く設計に変える

対処として、削除対象のテーブルを手で列挙するのをやめ、モデルの定義そのものから機械的に導き出す関数を新しく用意しました。

考え方としては、ORM(Object-Relational Mapping)が持っているモデル定義の一覧を全部たどり、その中から「プロフィールのテーブルを外部キーとして参照しているカラム」を持つモデルだけを拾い集める、というものです。単純化した例で書くと、次のような処理になります。

# 単純化した例:プロフィールを外部キー参照している全モデルとカラムを集める
def list_profile_dependent_models():
    dependents = []
    for mapper in Base.registry.mappers:
        if mapper.class_ is Profile:
            continue
        for column in mapper.local_table.columns:
            if any(fk.column is Profile.__table__.c.id for fk in column.foreign_keys):
                dependents.append((mapper.class_, column))
                break
    # 実行のたびに順序が変わらないよう、テーブル名でソートしておく
    return sorted(dependents, key=lambda d: d[0].__tablename__)

削除処理側は、この関数が返した一覧をループで回すだけの形に置き換えました。

# 単純化した例:削除対象はモデル定義から機械的に取得する
for model, owner_column in list_profile_dependent_models():
    db.query(model).filter(owner_column == profile_id).delete()
db.delete(profile)
db.commit()

こうしておけば、プロフィールに外部キーで紐づく新しいテーブルを追加しても、削除処理のコード自体には一切手を加える必要がなくなります。「消し忘れるかもしれない実装」から、「そもそも消し忘れようがない実装」に構造を変えた、というのがポイントです。

別のサービスで保存エラーの再試行が保存の取り消しになっていた問題を直したときも、送ってよいかを判定する処理を足すより、判定しなくて済むAPIの形へ変えるほうを選びました。注意や確認を足すより、間違えようのない構造へ寄せるという点で、今回と同じ判断です。

IT女子 アラ美IT女子 アラ美
覚えて追記するんじゃなくて、そもそも追記が要らない形にしちゃうってことね。

ITアライグマITアライグマ
はい。人の記憶に頼る仕組みは、いつか必ず抜け落ちると考えた方が安全です。

再発防止テスト ─ 依存レコードが本当に0件になったことまで確認する

実装を直しただけでは、また同じ種類の見落としが起きる可能性が残ります。そこでテストも合わせて見直しました。

まず、削除前の準備段階で、いいね・興味なし・既読・メモ・おすすめスコアのキャッシュといった依存レコードを一通り実際に作成します。そのうえで、さきほどの「モデル定義から依存テーブルを導く関数」を使い、削除前の時点で「実際にレコードが存在する依存テーブルの一覧」を取得し、期待するテーブルの集合と一致しているかを確認するようにしました。これにより、テストの準備自体が想定通りに依存レコードを作れているかどうかも同時に検証できます。

そのうえで削除APIを呼び、削除後にもう一度同じ関数を使って「まだレコードが残っている依存テーブルの一覧」を取得し、それが空であることを確認します。削除APIの応答コードだけでなく、依存レコードが本当に0件になったかどうかまで見るようにしたのが、これまでとの違いです。

さらに、「プロフィールを参照している既知のテーブルが、削除対象の一覧にちゃんと含まれているか」だけを確認する専用のテストも追加しました。これがあれば、今後また依存テーブルが増えたときに、削除処理側の実装を直し忘れていないかをテストレベルで検知できます。

IT女子 アラ美IT女子 アラ美
応答コードだけ見て安心してたの、まさに今回やられたところじゃない。

ITアライグマITアライグマ
はい。今回の教訓を踏まえて、結果の中身まで見るテストに直しました。

よくある質問

Q. テストがすべて通っていたのに、本番のMySQLでだけ外部キー制約違反が起きたのはなぜか?

本番のMySQLは外部キー(FK)制約を強制するのに対し、テストに使っていたSQLiteは接続ごとにFK制約を有効にする設定をしない限り制約を強制しないためです。さらに当時のテストは削除APIが正常応答を返すことだけを確認しており、依存テーブルのレコードが実際に消えたかまでは検証していませんでした。

Q. モデル定義から依存テーブルを自動導出する仕組みに変えても、テストは必要か?

はい、必要です。修正後は、依存レコードを作成した状態で削除を実行し、削除後にすべての依存テーブルのレコードが0件になっていることを確認するテストと、既知の依存テーブルが削除対象一覧に含まれているかを確かめるテストを追加しました。

Q. SQLiteのテストで外部キー(FK)制約を有効にしないまま運用していた理由は何か?

SQLiteは接続ごとに明示的にFK制約を有効化しないと制約チェックが動かない仕様になっており、初期のテスト環境ではそのまま使われていたためです。テーブル追加時にテストをすり抜ける根本原因を断つため、テストDBの設定見直しに加えて削除後のレコード0件検証を必須にしました。

IT女子 アラ美IT女子 アラ美
よくある疑問をQ&Aで整理しておくと、後から見返したときも経緯が追いやすくて助かるわね。

ITアライグマITアライグマ
はい。経緯や判断の理由をQ&Aの形でも残しておきました。

まとめ

今回の不具合は、削除処理の設計判断自体は最初から正しく、コメントにもその意図がちゃんと書かれていたにもかかわらず、「将来のテーブル追加を人が覚えて追記する」という前提に頼っていたために起きたものでした。加えて、テスト環境と本番環境でDBの制約の強さが違うという条件が重なり、不具合はテストをすり抜けたまま、興味なしボタンを追加してから約1週間気づけませんでした。

削除対象をモデル定義から機械的に導く形に変えたことで、今後テーブルが増えても削除処理側の実装を意識する必要はなくなりました。人の記憶や注意力に依存する箇所は、機能が増えるほど取りこぼしやすくなります。今回のように「同じ種類の漏れが繰り返し起きていないか」を振り返ることが、構造そのものを見直すきっかけになるのだと感じました。なお、DBスキーマの変更を仕組みで防ぐ取り組みはAlembic Multiple headsを防ぐ並行PRのマイグレーション対策でも実践しています。また、テストや特定条件の境界で起きる不具合の再発防止については、Expoのローカル通知が日付をまたぐと届かない原因と修正やExpoでActivityResultLauncherエラーが出る原因と対策でも書いています。

IT女子 アラ美IT女子 アラ美
正しい判断してたのに漏れるって、仕組み側の問題ってほんとそうだと思う。

ITアライグマITアライグマ
はい。同じミスが繰り返されたときは、実装の前提から疑うようにしています。

この記事をシェアする
  • URLをコピーしました!
  • URLをコピーしました!

この記事を書いた人

ITアライグマのアバター ITアライグマ ITエンジニア / PM

都内の事業会社で働くPM兼Webエンジニアです(IT業界10年以上、既婚・子持ち)。
スマホや決済アプリ、Wi-Fi、スマートスピーカーなど、身近なITの「困った」を、公式のヘルプやサポート情報で確かめたうえで、原因の切り分けから直し方までまとめています。設定で直らないときは、回線やサービスの見直しも含めて紹介しています。

目次