Timestamp 0000-00-00 00:00:00 に対する扱い

MySQL Connector/J

MySQL日本ユーザ会（MyNA)のMLで話題にでていたので調べてみた．

Timestamp型カラムに"0000-00-00 00:00:00"が入っていた場合にConnector/Jを経由して取得した際に，ver3.0系ではnullが返ってきていたがver3.1以降はSQLExceptionがスローされるようになっていて，この挙動にアプリが影響を受けるためにConnector/JのバージョンUPを控えている，という話が発端．

単純なコードを書いて再現してみると確かにver3.0系ではnullが返り，ver3.1以降ではSQLExceptionが返る．最初は状況がよく飲み込めなかったのでバグである疑いも持ちながらいろいろコードを読んだりドキュメントを読んだりしてみたら，どうやらこれは変更しがたいものであることが分かった．

  • ver3.0からver3.1にアップグレードする際の注意点としてドキュメントに記されている
  • SQLExceptionがスローされる挙動の方がJDBCおよびSQL標準の仕様に忠実
  • 接続プロパティで挙動を指定可能．デフォルトはSQLExceptionをスローするだが他にはver3.0と同様にnullを返す，最も近い値である"0001-01-01 00:00:00"を返すなどがある．

とのこと．これをひっくり返すのはかなり厳しい．

この実装が追加されたのはver3.1.4から．

こんな感じで使い分けるしかない．

        String url = "jdbc:mysql:///test";
        Properties props = new Properties();
        //props.put("zeroDateTimeBehavior", "convertToNull");
        //props.put("zeroDateTimeBehavior", "exception");
        props.put("zeroDateTimeBehavior", "round");
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection(url, props);

しかしJDBC仕様／SQL標準に近い挙動をするようになるとは言え，変更は変更なので影響がでる．まあ，ごめんなさいといったところか．


追記：
Connector/J開発者のMark Matthews氏からはこんな話が寄せられた．

  • そもそもこの問題はJava側で"0000-00-00 00:00:00"の値を持てないことに発端している
  • サーバ側の値をクライアント側（Connector/J）で正確に持てない場合、取るべき振る舞いは従来のMySQL流の方法（今の例でいうとnullを返す）よりも標準に乗っ取った方法を優先するように考えている（今の例でいうとSQLException)。
  • nullを返す従来の実装では、もともとサーバ側の値が"null"であった場合との判別を付けることができないため、"0000-00-00"であったという情報が欠落してしまう。
  • "0001-01-01 00:00:00"を返す実装も、やはり同様に情報が欠落する。


参考：
http://dev.mysql.com/doc/mysql/en/cj-upgrading.html#cj-upgrading-3-0-to-3-1

Datetimes with all-zero components ('0000-00-00 ...') - These values can not be represented reliably in Java. Connector/J 3.0.x always converted them to NULL when being read from a ResultSet.

Connector/J 3.1 throws an exception by default when these values are encountered as this is the most correct behavior according to the JDBC and SQL standards. This behavior can be modified using the ' zeroDateTimeBehavior ' configuration property. The allowable values are: 'exception' (the default), which throws a SQLException with a SQLState of 'S1009', 'convertToNull', which returns NULL instead of the date, and 'round', which rounds the date to the nearest closest value which is '0001-01-01'.

Starting with Connector/J 3.1.7, ResultSet.getString() can be decoupled from this behavior via ' noDatetimeStringSync=true ' (the default value is 'false') so that you can get retrieve the unaltered all-zero value as a String. It should be noted that this also precludes using any timezone conversions, therefore the driver will not allow you to enable noDatetimeStringSync and useTimezone at the same time.