DB で誤操作を防ぐために心がけておくべきこと

Question

MySQL にて、条件文（where 句）をつけずに、該当データ以外もupdate してしまうという凡ミスを犯してしまいました。

なぜミスをした？と言われると「動作確認をすぐに行いたく、急いでいた」くらいの理由しかないのですが
このようなミスを防ぐために今後行うことや、みなさんが心がけていることなどありましたら教えていただけますと幸いです。

自分では

• update をする前にdump をとる
• update 文を流す前に一呼吸おいてクエリを再確認する

くらいしか思いつきませんでした。

Answer 1

失敗は付き物なので、教訓として次に生かすしかないですね。
ある程度場数を踏んでくると手順は大体同じになってきます。
個人の経験でしかありませんが、参考になれば。

1.対象データの洗い出し

対象となるデータがどの程度となるか、検証する。
全件リストよりも、パターン表を抽出して件数などを確認する。

2.本番と同等のテスト環境で、どのような結果になるかを確認する。

1.でのパターン表を出力して、変更された件数が意図通りか確認。

3.必ず復元できる手順を準備

失敗した場合の切り戻しについて準備しておかないと深みに嵌ってしまいます。
バックアップは確実ですが、復旧にはシステム停止/再開を伴う上、長時間となる場合が多いので、
最短での復旧も考慮します。

・バックアップ
・対象データのみの退避テーブルを作成する。 (select ・・・ into)
（上記テーブルからの復元用のSQLも合わせて準備)

4.緊張した面持ちで実行

適度な緊張があった方が、失敗は少ない気がします。ガチガチでは駄目ですが。

5.結果確認　

NGだったら、組んだ手順の出番です。（心なしかブルーな気持ち）

---

データパッチはupdateだけじゃなくinsertやdeleteも含めて実行するケースもありますが、
updateの検証はちょっと手間に感じます。

update文での変更は、変更する内容が変更前を元にするような場合があるので、
更新して確認だと元に戻す時間を取られるので、効率良くありません。

set a=b のbをselect文で出力して確認するようにすると手直しの手間が取られません。
また、そのselect文を元にupdate文を組み立てると、
編集内容や条件はselectで確認できているので、誤りが少なくなります。

insertやdeleteなどの更新系は基本selectで対象や内容を確認して、それを元にした方が誤りを防げます。

Answer 2

以前、saziさんの手順と同じような作業をやっていました。

3.必ず復元できる手順を準備

私は、更新後のselectで間違いに気付いたら、すぐにrollbackしていました。

set a=b のbをselect文で出力して確認するようにすると手直しの手間が取られません。

私は、更新しない項目を使って、where句を書いていました。
（更新前のselect、update、更新後のselect、全て同じ条件文にしていました。）

誤った更新内容でcommitしてしまったこともありましたが、where句で範囲を明確にしているので、再度更新することで対応できました。

＜以下は回答に追記すべきだったため、コメントから移動させました＞
あともう1つありましたので補足します。
where句は、基本的に「データの増減があり得ない内容」にしていました。

・事前に確認した件数
・更新前のselect時の件数
・更新後のselect時の件数

これらが全て同じ件数であることも、commitする条件の1つにしていました。

Comments

[miyu21]

（コメントに書いていた内容は、回答内に移しました。）

Answer 3

コマンド投入時のケアレスミスを防ぐ方法としては、色々な方法が実施されています。

実施できることの制限

まず実施されるのが、実行できることを制限したインターフェイスを作成し、それを利用するという方法です。
制限と共に、入力値に対しての検証も行うのが一般的です。

事前確認

これもよくあると思いますが、ステージング環境で影響範囲を確認します。
その際、使用コマンドを保存しておき、本番にも同じコマンドを投入することで、問題の発生を防ぎます。
また、事後確認用の項目もここで洗い出します。

コマンド投入時の確認

非常にアナログな手法をとります。
・複数人による目視確認後の投入
・口頭でのコマンド復唱
・指差し確認
アナログだけど、かなり効果を発揮します。

事後確認

想定されるステータスになっているか確認します。

実務では、これらを実施計画書として作成し、時間やチェック項目、チェックした人のサイン等を入れられるようにしたものを傍らにおいて、コマンド投入します。

多分に過剰な工数がかかる手法ですが、参考まで＾＾

Answer 4

私は定常化されていないupdateを実行する場合、数件であっても下記の手順をとっていました。（なれると手間はそれほどでもない。）
これで完璧というつもりもないですが、体験した通りデータが飛ぶとかなり面倒なことになるので、この程度は必要と思っています。

本番は日時のdumpを持っているものとして、
以下をテスト環境（本番データを移したもの）で実行

• Selectを作成して対象データを確認し、Where句が対象外のデータを変更していないか確認
• 問題なければ、update句を作成実行。件数がSelectと同じか確認。
• updateしたレコードをselectするSQLを作成。テスト。(update日時などがテーブルにあれば、update時に更新しておくと楽。)
• Selectで該当テーブルおよび、戻しに必要な他テーブルのデータをCSVなどで出力するSQLを作成。テスト。（あまりに対象が多い場合は、テーブルのdumpに変える)
• 必要とあればテーブルロックのSQLも作成テスト。
• テーブルロック・バックアップ・Select(実行前)・Update・Select(実行後)をテキストファイルに張り付け
• テスト環境でリハーサル（ログを出力）

本番環境でSQLのテキストファイルを開いて下記を実行

• 上から順にコピペ実行(ログを出力)
• テスト環境のログと比較して、違和感があったら即終了
• 実行前・実行後のSelectを比較して、違和感があったら即終了
• 件数などの問題がなければ、commit。

あと、急いでいるときに慌ててしまうことは誰にでもあることです。しかし、意識付けと訓練で自制することが大事です。

慌てていると不安を感じなくなります。不安は結構大事なシグナルです。

―――
ちなみにやってみるとわかりますが、SQLは一度作ると再利用しやすいですし、メンテ依頼が頻出する場合はスクリプト化して３０秒で完了させたりできます。

Answer 5

手元に、テスト環境を用意しておいて先にそちらで動作確認を行う。
テスト環境で確認できたSQL文は、本番環境では直接入力しないでコピー＆ペーストで本番に適用するか、スクリプトファイルとして流す。とかかなぁ

Answer 6

私もDB運用をやっていますが、大きなミスをする人は、”依頼された通りに正確に”と思っている人が多いようです。
ヒューマンエラーは仕方がない面もあるのですが、優秀な人はうっかりTRUNCATEのような凡ミスは犯しません。
それは、どんなに急いでいても、作業の意味は結果も含めて理解した上で、実行しているからですね。

依頼された通りにやるだけでは、最高でも100点で、減点方式になりますが、
意味を理解した上で、依頼側のミスも含めて包括するつもりでやれば、基礎点が120点で、
少しミスしても100点です。詭弁に聞こえますかね。

時間が無いからよく確認出来ませんでした、と言うのは言い訳で、
それでも瞬時に判断出来る人はいるはずです。そうなりましょう。

Answer 7

ヒューマンエラーはなんともし難いですね
deleteやdropなどはphpMyAdminなどでもアラートをだしてくれますけど
とくにupdateは更新したいからするものですからね
トランザクションをしようがなんだろうが更新されます

基本は毎日バックアップをとることと
復旧ポイントからバイナリログを適用させることくらでしょう

Answer 8

人間が作業にあたっている以上ミスが発生するのは当たり前のことなので、こういった事故は
作業者ではなく反映周りの運用フローの設計ミスだと思っています。

技術的なことは他の方が言っている通りですが、既に運用が可動している環境に対し
何かしらの変更を加えるような操作を行う場合はSQLに限らず

反映手順書を作成
↓
先輩エンジニアや上司にレビューをもらう
↓
レビュー内容に従い手順書を修正
↓
再度レビュー
↓
問題ないことを確認
↓
手順書に従い反映作業開始

実際の反映作業自体も作業者と確認者の二人以上であたることが望ましいです。

このように手順に対しレビューのフローを設けることでこういった事故は減らせると思います。
何より、なにかやらかしてしまったとしても『作業者としてはちゃんとしてましたよ』と言い訳ができます。

Answer 9

みなさまご回答いただきありがとうございました。

自分でも色々調べていく中で、MySQL にはAUTOCOMMIT という機能があることを恥ずかしながら、今更知りました。。
今回操作を失敗してしまったDB がAUTOCOMMIT = 1 の状態であったため、
AUTOCOMMIT = 0 にしておくのも一つの改善策かなと思いました。

みなさまのご回答を参考にし、今後に生かします。

Answer 10

create table aaa_20180409 as select * from aaa

てな感じでバックアップとってから変更してます。

Answer 11

SQLの使い方を熟練する。

SELECT文のFROM句にJOINで複数のテーブルやビューが使える、ってレベルのことさえ知らないで、とんでもないコードを書いていて、パフォーマンスが落ちているから調べてくれ、って呼ばれるのも困りものです。