よかろうもん！

■[Rails][MySQL] これを知っておかないと、MySQLサーバの再起動でDBデータの不整合が発生するかもしれません！

Railsに限らず、MySQLを利用したサービスを開発/運用しているなら、これから解説する内容を知っておかないと、予期しないデータ不整合を発生させてしまうかもしれません。

データ不整合が発生してしまったら、本来あるべき状態に戻すのはかなり難易度が高いため、開発/運用をしているエンジニアは、データ不整合を起こさないようにすべきです。

では、どのようなことをすると、データ不整合をいとも簡単に発生させることができるかを解説します。

まずは、何が原因でデータ不整合が発生するかの簡単なモデルを紹介します。

以下のようなUserオブジェクトをcreateししたとします。

User.create(:name => "interu, :age => "27")

すると、Userテーブルにデータが追加されます。

■ Userテーブル

id	name	age
1	user_a	30
2	user_b	28
3	interu	27

※ここでは、下記の表の id = 3 のカラムが追加されたとします。

続いて、先ほど作成したデータを削除したとします。

User.destroy(3)

するとテーブル情報は以下のようになっているはずです。

■ Userテーブル

id	name	age
1	user_a	30
2	user_b	28

で、ここで再度 Userオブジェクトをcreateすると、id = 4 のレコードが新しく作られるはずです。

■ Userテーブル

id	name	age
1	user_a	30
2	user_b	28
4	user_c	29

ですが、上記の INSERT を行う前に、MySQLサーバの再起動を行うと、AUTO INCREMENTのidの値が変化します。

■ Userテーブル

id	name	age
1	user_a	30
2	user_b	28
3	user_c	29

※id = 4ではなく "id = 3"のレコードが追加されます。

これについては、『MySQL 5.1のドキュメント』に詳しく書いてあります。

記載の一部にこのような記述があります。

InnoDBはサーバが起動している限り、メモリ内の自動インクリメントカウンタを利用します。サーバが停止し再起動した時、先ほど説明があったように、InnoDBは、テーブルへの最初の INSERT に対する各テーブルのカウンタを再初期化します。

MySQLサーバの再起動を実施したことで、メモリに保存されていたAUTO INCREMENTの値がリフレッシュされてしまったので、DBに保存されている"idの最大値 + 1"の値が利用され、上記のサンプルのようにidの値が3になります。

では、このMySQLの仕様を知らなかったとすると、どうしてデータ不整合が発生するのでしょうか？

答えのカギは、”関連”です。

Userオブジェクトをcreateした時に、同時にCompanyオブジェクトも生成されたとします。

■ Userテーブル

id	name	age
1	user_a	30
2	user_b	28
3	user_c	29

■ Companyテーブル

id	name	user_id
1	company_a	1
2	company_b	2
3	company_c	3

このあと、Userテーブルのid=3のuserを削除したとして、関連テーブルのデータを消し忘れていたとします。

＃railsでも :dependent => :destroy オプションをきちんと設定しておかないと、このような状況になりますよね。

■ Userテーブル

id	name	age
1	user_a	30
2	user_b	28

■ Companyテーブル

id	name	user_id
1	company_a	1
2	company_b	2
3	company_c	3

すると、Companyテーブルにごみデータが残ります。

このあとMySQLを再起動して、再度Userを追加したとすると

■ Userテーブル

id	name	age
1	user_a	30
2	user_b	28
3	user_d	20

■ Companyテーブル

id	name	user_id
1	company_a	1
2	company_b	2
3	company_c	3
4	company_d	3

※前提として、userは複数の会社に所属OKとします。

すると、id=3 の user_d は、本来 company_d にだけ所属しているはずなのに、 company_c にも所属していることとなります。

以上のことを簡単にまとめると以下のようなシーンでデータ不整合が発生します。

• MySQLサーバの再起動直前に DELETE が発行されている

• レコード削除時に関連レコードを完全に削除できていない

今回のサンプルでは、データ不整合が発生してしまった時の恐さをあまり伝えることができなかたかもしれませんが、発生するシーンによっては、とてつもない問題になりかねません。

このような事態にならないようにするためにも、日頃からごみレコードが残らない様な実装を心がけましょう。

最後に。

私が調査した限りでは見つけることができませんでしたが、もし、AUTO INCREMENTの値がMySQLサーバ再起動時に消えないような設定を知っているという方がいましたら、教えていただきたく。

このような設定項目が存在すれば、MySQLの再起動によって今回説明したようなことは発生しないはずなので。

■参考サイト

• 「MYSQL.InnoDBでAUTO_INCREMENT値はメモリにだけ保存する。」

Permalink | コメント(4) | トラックバック(0) | 19:44