We got a new Continuous Integration box from Pivotal Labs yesterday and I ran into some weird MySQL time based issues when trying to get our tests to pass on it. It turned out that the CI box was running MySQL v 3.0.38, but all of our dev boxen were running v 3.0.45. Here's some difference between these two versions:
v.45 will happily take a clause of the format SELECT * FROM a_table WHERE end_date < '07-12-31' while v.38 isn't so happy with it. In this case, I have to hand it to v.38, because that date was in YY-MM-DD format which is about the most retarded date format I've seen in production code. I changed the Date#to_mysql method to use YYYY-MM-DD format and all was well.
The second issue was more odd. In v.45 if you compare a date column value with a time, the date is treated as a time value set to 00:00:00 of the day for the comparison. In other words, if the value in the date column is 2006-07-05 and you compare that with '2006-07-05 00:00:00' they are equal. Not so in v.38:
mysql> create table delme ( a date );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into delme (a) values ('2006-07-05');
Query OK, 1 row affected (0.01 sec)
mysql> select * from delme where a < '2006-07-05';
Empty set (0.00 sec)
mysql> select * from delme where a < '2006-07-05 00:00:00';
+------------+
| a |
+------------+
| 2006-07-05 |
+------------+
1 row in set (0.00 sec)
I found out that the production box is running v3.0.51 so we've now upgraded all of our workstations and deployment/testing machines to that.
Comments