Note 1: I know this sounds too weird to be true, but I saw it consistently.
Note 2: Yes, I'm aware that there are other ways to add an hour to a datetime interval, it's a long story, see prev post.
I had a table with two TIMESTAMP fields. Adding an hour's worth of seconds to one field would reset the OTHER field to the current date and time.
So I'd do a select on start_time and end_time from my log table. Both are fine.
Then I would do:
mysql> update log_table set end_time = from_unixtime( unix_timestamp( end_time
) + 3600 );
You'll notice I'm just changing the end_time field.
Then I'd do a select of both values and it would show that START time was now all set to the current date and time. I recreated this a bunch of times. I also dropped and recreated the table and indexes. I also dropped and recreated with no index for either field, just in case it was something weird with indexing.
Changing the field from type TIMESTAMP to type DATETIME fixed the problem. All of my problems vanished with making that one change.
I really think this is a bug. I'm running 5.0.51a-community-nt MySQL Community Edition. I suspect it has something to do with TIMESTAMP's defined behavior when it gets a null value, to return the current date and time. But it doesn't explain the "cross talk" between fields - I'm guessing that stems from some type of query evaluator/executor and maybe cached/uncached garbage from somewhere... but who know!
Workaround: DATETIME = good, TIMESTAMP = bad (at least when using multiple date / time fields in the same talbe)
Comments