mysqldumpとtimezoneと4.0

mysqldump の使い方が分かった気になってたら、大きな落とし穴がありました。

mysql4.0 -> 4.1/5.0 の大きな違いとしてcharの扱い、とくに文字数/バイト数の数え方の話があります。たとえば…、varchar(100)がvarchar(33)になっちゃうのでそのままだと文字数が足りない。なのでdump&importし直しでvarchar(100)にした。そしたら今度はkey lengthが1000byte超えちゃった、とかですね。

けど、もうひとつ、タイムゾーンの取扱いもおおきな変化なのでした。

具体的には、mysql4.0でtimestampにJSTな値を入れていたときに、それを4.1以降のmysqldumpでdumpしてimportしなおすと9時間ずれる、という問題が潜んでいました。

mysqldump が出力するヘッダみたいなの

前回に書いたように、mysql5.0付属のmysqldump(MySQL dump 10.10とか)は、CREATE TABLEやINSERTのまえに変なヘッダみたいなのを出力してくれます。これはmysql4.0のmysqldump(MySQL dump 9.11とか)にはなくて、こいつが何をするのかをもうちょい注意深く見ておいたほうが良さそうです。

  1 -- MySQL dump 10.10
  2 --
  3 -- Host: localhost    Database: hoge
  4 -- ------------------------------------------------------
  5 -- Server version   4.0.xx
  6 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
  7 /*!40103 SET TIME_ZONE='+00:00' */;
  8 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  9 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 10 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 11 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
...

いまの話に関係あるのは以下の通り。

  • 1. mysqldump のバージョン
  • 5. dump したサーバの mysqld のバージョン
  • 6. import先のサーバが 4.1.3 以降ならば、(いまの接続の) タイムゾーンを @OLD_TIMEZONE に一旦退避
  • 7. import先のサーバが 4.1.3 以降ならば、タイムゾーンUTC に設定

9時間ずれる仕組み

運用の仕方によりますが、mysql4.0 で運用しているテーブルの timestamp カラムには、タイムゾーンという概念がないので (たとえば) JST な timestamp がそのまま突っ込んであったりします。

それをいったんdumpして4.1以降にimportするときに、そこに書いてあるタイムゾーンがどこなのか分からない、というより UTC だと信じるしかないわけです。

それを明示しているのが7行目のところで、これからINSERTするタイムスタンプUTCなんだと宣言しているわけですね。

ややこしいことに、4.0付属のmysqldumpだと上に書いたヘッダみたいなのは出てこないので、clientの設定(my.cnf)がうまいことなってると、この問題は表にでなかったりします。6行目が、うまいことなってる設定を(言いようによっては)台無しにしてしまうわけです。

どうしましょうね?

なにが悪かったのか、なにがmysqlさんの意図してないことだったのか、というと、たぶんタイムゾーンという概念がないのにtimestampにUTCでないものを入れてた、ってことなんだと思います。

でもそれは仕方ないので、素直に7行目を手で書き換えてあげるのがいいんじゃないかと思いました。そのためにも、5.0に突っ込むなら5.0のmysqldumpを使ってあげるのがいいのかな。

あ、もちろんviで書き換えるんじゃなくて、

 % sed -i -e "/^\/\*\!40103 SET TIME_ZONE='+00:00' \*\/;/s/00:00/09:00/" hoge.sql

みたいなかんじですね。*1

*1:"!"はシェルのヒストリに食われないように