Edited at

MySQLでINSERTができなくなった?

More than 1 year has passed since last update.


経緯

筆者はDBAではなく、多少RDBを触るWEBデベロッパ。

100GB程度のデータを持つDBサーバのリプレイスのため、新規にMySQLをセッティングし、mysqldumpで取得したデータをリストア。データ量が多いのでなんとか少しでも早めようとmy.cnfに設定を多少追加。

リストア自体は完了。

その後、テスト用のアプリケーションをつなぎこんでテスト運用開始。データが追加されず。

データが追加されず?


エラーがない。

データが追加されないとうことは、一般的には「エラー」があるために処理が停止している可能性が高い。

基本通りにアプリケーションのログ、MySQLのログを確認するもエラーはない。

兎に角、エラーと確認できるログはない。

手詰まり。

仕方なく、コンソールにてINSERT文を発行してみる。

データが追加されない

データが追加されない!INSETできない!

しかし、これもエラーにならない。

しかも、同じコンソールでSELECTすると、なんとデータがある!!

他のコンソールからは見えないのに?


???

DBAでない筆者は若干パニックになったので、喫煙者の特権である休憩をとることにした(VAPEだが)。

データはINSERTできているように見えて、実際にはINSERTできていない。

そもそもINSERTってなに?と、余計方向性が怪しくなりゲシュタルト崩壊気味。

commitしてないのでは?と、はたと気づいた。

今までそれほど一生懸命はMySQLを勉強していなかったのを反省。


原因

弱小システム屋には大きめのデータ100GBのDBをリストアするため、多少でも時間を節約できればとGoogle検索。行き着いた先の設定が下記。


my.cnf

[mysqld]

init_connect='SET AUTOCOMMIT=0'

自動的にcommitしない、という設定。

詳細はこちら → MySQL 5.6 リファレンスマニュアル / START TRANSACTION、COMMIT、および ROLLBACK 構文)

(なお、MySQL5.5.8以降では、my.cnfにautocommit=0で設定できる)

mysqldumpでバックアップをとりリストアする際に、SQL文の行数が多いと、その都度commitが発行されるので遅くなると。

そして、これを一回のcommitで処理するために上記の設定を行うと、かなり速度が改善されると。

結局、リストアしたデータのカラムに巨大なデータを含むものがあるからか、100万行なぞ大した量でないのか、たいした速度の変化は感じられなかった。

思うに、tableが大量にある場合などには効果的なんだと思う。

で、上記の設定を放置。

そう、commitしていないから。データ登録されてないのね。

commitって要るんだね。


対応

上記の設定を削除してMySQLを再起動。無事データがINSERTできました。