こんにちは！
ポーラ・オルビスホールディングスのITプロダクト開発チームでスクラムマスターをしている川田です。
みなさんはDBを操作する際に、何かしらのツールを利用されているでしょうか。コマンドラインでSQLをガリガリ書くのもよいですが、簡単な操作でDBを管理できるのは魅力的ですよね。
私たちの開発チームではDB操作にDBeaverを利用しています。先日、チーム内でDBeaverのUI上から開発環境のレコードを更新しようとした際に想定外のレコードまで変更してしまいました。今回の記事では、反省と備忘を兼ねて振り返ってみようと思います。本当に本番環境じゃなくてよかった・・・
何をしたのか
気付いたきっかけは自動テストがNGになったことでした。テスト用のデータが変更されており、誰かが誤って変更したか、開発中の内容による一時的な影響かと思っていたのですが心当たりのあるメンバーがおらず、何か怪しいな・・・
と感じ調べ始めました。
調査の結果、テスト用のレコードだけではなく他のレコードも複数同時に変更されていることがわかりました。変更したメンバーに手順を確認したところ、DBeaverで以下の操作を行っていました。（テーブルの内容は説明用に適当な内容にしています）
①以下のような test_data テーブルから、変更したいレコードの対象カラムのみを取り出します。
| id [pk] | start_date | end_date |
|---|---|---|
| 1 | 2025-02-01 | 2025-02-28 |
| 2 | 2025-03-01 | 2025-03-31 |
| 3 | 2025-03-01 | 2025-04-30 |
| ... |
例えば id = 2 の start_date を変更するとして、一度以下のようなSELECT文で対象のデータのみを取り出します。
SELECT start_date FROM test_data WHERE id = 2
DBeaverでは、以下のようなイメージで対象のデータのみが表示されます。
| start_date |
|---|
| 2025-03-01 |
②DBeaverのUIで、start_date を 2025-03-01 から 2025-03-15 に変えます。
③Saveボタンを押すとNo unique keyダイアログ（内容は公式ドキュメントを参照）が表示されるので、Use All Columnsをクリックします。
④test_data テーブルを確認すると想定通り id = 2 のデータは変更されていますが、一緒に id = 3 のデータも変更されています。
| id [pk] | start_date | end_date |
|---|---|---|
| 1 | 2025-02-01 | 2025-02-28 |
| 2 | 2025-03-15 | 2025-03-31 |
| 3 | 2025-03-15 | 2025-04-30 |
| ... |
何が起きたのか
ポイントはNo unique keyダイアログの選択です。Use All Columnsを選択した際に、DBeaverが内部的にどのようなSQLで更新しているか確認してみます。
Saveボタンのすぐ右にある▼の部分をクリックし、表示されるメニューから「スクリプトを生成する」をクリックすると、以下のようなSQLが表示されました。
UPDATE test_data x
SET x.start_date = '2025-03-15'
WHERE x.start_date = '2025-03-01'
見てわかる通り、最初にSELECTした際のWHERE句の条件 id = 2 が無くなっており、代わりに変更前の値である x.start_date='2025-03-01' が条件として設定されています。意図しないレコードが修正された原因はこれでした。
あらためてダイアログを確認すると、ちゃんと書いてありますね・・・
DBeaver can use all columns as a unique key, but this can lead to multiple rows modification.
DBeaverは全てのカラムをユニークキーとして使用することができますが、これは複数行の変更につながる可能性があります。
先ほどの公式ドキュメントにも注意書きがありますね。確認大事です。
どうすればよかったか
原因がわかったので、対策を考えます。DBeaverを利用する上での対策と、ツールに関わらない対策の両輪で考えます。
データをSELECTする際に主キーも一緒に取り出す
最初に更新対象のデータをSELECTする際に、WHERE句で条件を絞ってから対象のカラムのみ取り出していました。
SELECT start_date FROM test_data WHERE id = 2
この際に、主キーも一緒にSELECT対象とすることで回避することが可能です。
SELECT id, start_date FROM test_data WHERE id = 2
上記で取り出したstart_dateを更新してSaveする際には、DBeaverは以下のようなSQLを生成します。意図通りですね！
UPDATE test_data x
SET x.start_date = '2025-03-15'
WHERE x.id = 2
Auto commitをOFFにする
今回の作業ではAuto commitがONになっていたため、Saveボタンを押した時点で即座に変更が反映されました。根本的な対策ではありませんがデータ更新の際にはAuto CommitはOFFにして、変更内容をチェックしてから手動でコミットする運用としておけば意図しない変更に気付けた可能性があったかもしれません。
DBeaverには、接続設定で常にAuto commitをOFFにする設定が存在します。デフォルトはAuto commitがONになっているので、あらかじめこちらをOFFにしておくのも良いと思います。
（少なくとも本番環境は）UIからのデータ更新は避ける
人間が操作する以上は、ミスは必ず発生します。ツールを使用することによる利便性を損なわない程度に、UIからのデータ更新はできるだけ避けることを検討したいところです。また、同じ手順でも使用するツールによって挙動に差がある可能性もあるので、そういった不確定要素を排除することも目的の一つです。
私たちの運用においても、本番環境で何かしらのデータパッチが必要となった場合には事前にSQLを作成し、チームでレビューして、開発環境と検証環境でテストしてから本番適用しています。本番適用の際にDBeaverは利用していますが、SQLを実行するためのツールとしての利用にとどめており、UI上からのデータ変更は行わないようにしています。さらなる対策としてスクリプトファイル化する、Flyway等のツールを利用することでミスを減らせるとよいと思います。
さいごに
便利なツールは積極的に利用すべきだと思いますが、いかにして操作ミスを減らしていくか、運用面とセットで考えることが大事だとあらためて痛感した出来事でした。
私たちのやらかしの内容が、DBeaverを使っている方はもちろん、それ以外のツールを使っている方にとっても気付きのヒントとしてお役に立てれば嬉しいです