AWS RDS MySQL のタイムゾーンが UTC の状態で DATETIME フィールドに JST で入れてしまった場合の復旧

アプリは JST でいきたい場合。

結論

アプリで、MySQLの現在時刻取得を使っているか調査。NOW(), SYSDATE() など

使ってないなら、おそらく AWS - RDS(MySQL)でJSTを使う たった１つの冴えたやり方 - Qiita この対応でOK。RDSの再起動不要。

使っているなら、UTC で評価されているのが意図したものか調査。

→ JST で処理されていると勘違いしているなら、上記対応の実施でOK 。(今回の僕のケース)

→ 意図していたなら、アプリのコードの修正必要。

経緯

Amazon Web Service の RDS で MySQL を使っています。

MySQL 内で、NOW() を実行した時に UTC で返ってきてたので怪しく思い、タイムゾーンを確認。

mysql> SHOW VARIABLES LIKE '%time_zone%';

Variable_name	Value
system_time_zone	UTC
time_zone	UTC

UTC で使ってしまってました。タイムゾーンが UTC にもかかわらず、DATETIMEフィールドに JST で入れてました。あうあう。

現状、どうなっているかというと

INSERT

アプリ	→	MySQL
JST の DATETIME	→	UTC のDATETIME だと思って格納

SELECT

MySQL	→	アプリ
UTC の DATETIME	→	JST の DATETIME だと思って処理

MySQL と、アプリ間の通信ではタイムゾーン情報は含まれていないため、お互いに自分のタイムゾーンだと思って処理しており、不整合はありません。日付型に値を入れる際は、NOW() などは使わず、アプリ(フレームワーク,WAF)側で日付文字列を作成して INSERT していましたので、事故にはならなかったのですね。

ただし、SQL に NOW() とか SYSDATE() を書いてしまうと、ひどい目にあいます。

このままだと後々トラブルにもなりそうなので、修正をしてみます。

修正方針

MySQL の DATETIME フィールドは、タイムゾーンまでは格納しないようです。

つまり、記録内容の変更はせず、接続時のタイムゾーンだけ JST にしておけば、NOW() などが日本時間で動くように思います。おそらく、実施内容としては 接続時のタイムゾーンを JST にする。これだけ。

調査

まず、MySQL に UTC のまま接続して SELECT。

mysql> SELECT id, last_login, SYSDATE(), NOW(), TIMEDIFF(NOW(), last_login)
FROM user_user WHERE id = 8;

id	last_login	SYSDATE()	NOW()	TIMEDIFF(NOW(), last_login)
8	2015-12-15 13:08:35	2015-12-16 03:04:41	2015-12-16 03:04:41	13:56:06
次に、セッションのタイムゾーンを JST にして、同じ SQL で SELECT してみます。

mysql> SET SESSION time_zone = 'Asia/Tokyo';
mysql> SELECT id, last_login, SYSDATE(), NOW(), TIMEDIFF(NOW(), last_login)
FROM user_user WHERE id = 8;

id	last_login	SYSDATE()	NOW()	TIMEDIFF(NOW(), last_login)
8	2015-12-15 13:08:35	2015-12-16 12:04:42	2015-12-16 12:04:42	22:56:07

※ 上の、 UTC での SELECT 結果の 1秒後の 結果です。

タイムゾーンを変えたため、 NOW() の結果は変わっていますが、 user_user テーブルの last_login の結果は変わりません。MySQL の DATETIME は、タイムゾーンを格納していないためです。

設定

AWS の場合、セッション確立時に自動的に SET SESSION time_zone = 'Asia/Tokyo'; 相当のSQLを発行するのが定番のようです。(システムのタイムゾーンの変更は実質不可能なため)

AWS - RDS(MySQL)でJSTを使う たった１つの冴えたやり方 - Qiita

こちらの記事が冴えてると思いましたので、採用します。

AWS の RDSダッシュボードより、 パラメータグループ → (選択) → パラメータの編集 → init_connect でブラウザを検索。

このテキストエリアに

SET SESSION time_zone = CASE WHEN POSITION('rds' IN CURRENT_USER()) = 1 THEN 'UTC' ELSE 'Asia/Tokyo' END;

を貼り付け、「変更の保存」をクリック。

(一度、貼り付ける前に間違いがないか確認するため、SQLコンソールなどで実行してみると良いでしょう。)

サービスがオンラインで、RDSもガンガン使われている状態でしたが、メンテするほどでもないと思ったのでえいやっと保存ボタンを押しました。

設定は、RDS の再起動なく即時反映されるため、すぐクライアントを接続しなおして動作確認。

mysql> SHOW VARIABLES LIKE '%time_zone%';

Variable_name	Value
system_time_zone	UTC
time_zone	Asia/Tokyo

mysql> SELECT NOW();

(結果省略)

良いですね!

これによって、SQL の結果が変化するのは NOW() などの現在時刻関数だけなので、それを使ってない場合アプリは継続して動作します。例えば Django は、通常 MySQL サーバ側の時刻は使用せず、アプリサーバ内で日付文字列を作り、SQL として実行しています。

まとめ

NOW() 使わないなら MySQLのタイムゾーンはあんまり気にしなくていい