MySQL4.0からのバージョンアップ時のハマリ点〜不正な日付の扱い〜

　とっくに EOL となっている MySQL 4.0 を未だに使い続けている会社もそれなりにまだあるようです。MySQLはバージョン 4.0 とバージョン 4.1 の間に文字コードの取り扱い変更を含む大きな変更が為されているために、バージョンアップに二の足を踏んでいるという心理があるのでしょう。4.0のままでもちゃんと動いているし！

　で、実際にバージョンアップの際に文字コード（文字化け）についてはこれまで多くの書籍や記事などでも喧伝されてきたので、上手にバージョンアップするノウハウも溜まっていると思いますが、もうひとつ意外なハマりどころに「日付の扱い」があります。

　（１）MySQL 4.0 では日付のチェックがちょっとルーズ
　（２）MySQL 5.1 (たぶん 4.1以降）ではちゃんとチェック

MySQL 4.0 での日付チェックについてもう少し詳しく説明すると、MySQL 4.0 では
　　「月部分が 1〜12」
　　「日部分が 1〜31」
というチェックしかしていないのです。（なんと！）

おためしコード：

CREATE TABLE dttest (a int auto_increment NOT NULL PRIMARY KEY, b datetime) ENGINE=InnoDB;
INSERT INTO dttest (b) VALUES ("2009-11-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-09-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-06-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-04-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-02-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-02-30 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-02-29 00:00:00");

　この結果が、

mysql> SELECT * FROM dttest;
+---+---------------------+
| a | b                   |
+---+---------------------+
| 1 | 2009-11-31 00:00:00 |
| 2 | 2009-09-31 00:00:00 |
| 3 | 2009-06-31 00:00:00 |
| 4 | 2009-04-31 00:00:00 |
| 5 | 2009-02-31 00:00:00 |
| 6 | 2009-02-30 00:00:00 |
| 7 | 2009-02-29 00:00:00 |
+---+---------------------+
7 rows in set (0.00 sec)

　わはは。豪快です。「西向く侍」全部31日OKです！

　さて、これを MySQL 5.1 につっこむとどうなるでしょうか。

mysql> INSERT INTO dttest (b) VALUES ("2009-09-31 00:00:00");
Query OK, 1 row affected, 1 warning (0.05 sec)

　warning が出ました。見てみましょう。

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'b' at row 1 | 
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

　日付カラムでの「範囲外」ということで怒られていますね。でもエラーじゃありません。データはどうなっているのでしょうか？

mysql> SELECT * FROM dttest;
+---+---------------------+
| a | b                   |
+---+---------------------+
| 1 | 0000-00-00 00:00:00 | 
+---+---------------------+
1 rows in set (0.00 sec)

　おぅ(爆笑)。

　範囲外の場合はその型のデフォルト値にされてしまうのでしたね。
　これ、実はすごく危険なことで、
MySQL 4.0上でこのような不正な日付を含むテーブルを mysqldump して
　MySQL 5.1 に何も考えずにつっこむと、warningが出ていることに気づかずに、
　一見正常終了している、でも値は上記のように変わってしまう、ということが起こります。怖い。
　strictモード（set sql_mode=strict_all_tables;）で実行したいものですね。

　以上、MySQL 4.0 -> 5.1 ちょっと怖い話、でした。

追記(6/24)：
　私が師匠と仰ぐマイミクさんより、MySQL 5.1 でもこのような日付を認めさせる方法を教えてもらいました。
SQL_MODE として ALLOW_INVALID_DATES を指定するのです。
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_allow_invalid_dates