【爆笑必須】エンジニアのやらかしをまとめました2020年アドベントカレンダー編

本番でTableを1つDeleteしてしまいON DELETE CASCADEでさらに4つTable dataが消えた話　@nobu0605

起きた事

本番環境のデータ調査の依頼を受けた。その調査を受ける前に、それとは別で不要データを本番DBから削除する作業をMySQL Workbenchで行っていた。

本番DBで、データ調査を行う際にMySQL WorkbenchでSQLのselectと間違えてdeleteを実行してしまい、Tableを1つ丸ごとDeleteしてしまった。

DELETE FROM posts;

ON DELETE CASCADEが親テーブルに設定されてしまっていたため、さらに4つのTable dataが芋づる式に消えてしまった。

テーブルの構成(テーブル名などは例として挙げていて、実際のものとは多少異なります)

正しい設定

usersテーブルでuserを削除した時に、そのuser_idに紐づいている子テーブルのレコードも一緒に削除されるという設定です。

comments table

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

likes table

CONSTRAINT `likes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

points table

CONSTRAINT `points_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

posts table

CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

今回の間違った設定

usersテーブルの子テーブルposts tableを丸ごと一つ削除してしまった時に、post_idが紐づいているusersのレコードも一緒に削除される。

さらに、そのuser_idに紐づいている子テーブルのレコードも芋づる式に一緒に削除されてしまうという設定。

users table

CONSTRAINT `users_ibfk_1` FOREIGN KEY (`comment_id`) REFERENCES `comments` (`id`) ON DELETE CASCADECONSTRAINT `users_ibfk_2` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADECONSTRAINT `users_ibfk_3` FOREIGN KEY (`like_id`) REFERENCES `likes` (`id`) ON DELETE CASCADECONSTRAINT `users_ibfk_4` FOREIGN KEY (`point_id`) REFERENCES `points` (`id`) ON DELETE CASCADE

comments table

CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

likes table

CONSTRAINT `likes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

points table

CONSTRAINT `points_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

posts table

CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE

問題の対処

今回はRDSのバックアップを毎日取ってあったので、それを復元する事で元の状態に戻す事ができました。

その日の朝10:45位に問題が発生し、RDSを復元できたのは13:00位でした。データ量によって復元時間が変動するかもしれないです。

ミーティング中に起きたので、すぐに問題の報告ができました。

バックアップは大事です。。

再発防止策

• 本番環境のDBはマネージャー以上のメンバーのみが編集権限(deleteなど)を持ち、それ以外のメンバーは読み取り専用権限にした。
• そもそも親テーブルにON DELETE CASCADEが設定されているのが、おかしいのでON DELETE CASCADEを親テーブルから取り除いた。

実装された経緯は不明だが、明らかにアンチパターンなので、取り除いた。

感想

エンジニアになってから、今回の一件が起こるまでは一度も大きなミスを起こしたことが無かったので、だいぶ気が緩んでいたと思います。

また、ミーティング中にデータの調査が必要になり、話しながら本番DBを見てしまったのもよくなかったです。

DELETEを実行してしまった時は、頭が真っ白になってしまいましたが、

その後に同僚から「自分もそういう経験はたくさんしてきた。気にしなくていいよ」と言ってもらい、励ましてもらえたのは嬉しかったです。

こうやって失敗もしつつエンジニアとして一歩ずつ成長していくのかなと思うので、今回の失敗を糧に自分の成長に繋げて行けたらと思います！

引用元：https://qiita.com/nobu0605/items/75793a4db539f054b28d

単語メモ

• CASCADE（カスケード）：連なった小さな滝のことで、同じものがいくつも数珠つなぎに連結された構造や段階的に物事が生じる様子のことです。
• MySQL（マイ・エスキューエル）：世界中の多くの企業が使用しているデーターベース管理システムです。大容量のデータも高速に動作を行えるので、レンタルサーバー・検索エンジンでも使用されることがあります。
• MySQL Workbench：データーベースアーキテクト、開発者、DBAのための統合ビジュアルツールです。データ、モデリング、開発、サーバー設定、ユーザー管理、バックアップなどの包括的な管理ツールを提供しています。
• SQL：データベース言語（RDBMS)データベースにデータを挿入したり、検索する時に使います。効率的に操作ができます。
• select（文）：データの検索する命令文です。データ操作言語（DML/Data Manipulation Language）で、利用頻度が高いものになります。
• delete（文）：テーブルに格納されているデータを削除します。WHERE句（どこ）を指定しなければテーブルに格納されているすべてのデータが削除されてしまいます。
• テーブル：シートに相当ものです。データの種類やプログラムの利便性を考慮して複数のテーブルをもつことが多く、Excelブックのような構造になっています。
• レコード：データが保管される場所のことを現わしていた語に対して指すデータそのもののことを指します。
• usersテーブル：ユーザー情報を保存しておくデーターベースのテーブルのことです。
• RDS（Remote Desktop Services）：仮想サーバーをユーザーが共有する方式で、サーバーOSだけで複数人が使えるものです。