MySQL Bugs: #8247: inserting NULL values into a timestamp field produce the wrong data

Bug #8247	inserting NULL values into a timestamp field produce the wrong data
Submitted:	1 Feb 2005 20:55	Modified:	2 Feb 2005 8:55
Reporter:	Robby Bankston	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	Ver 3.23.54 for redhat-linux-gnu on i386	OS:	Linux (Red Hat Linux release 9 (Shrike))
Assigned to:		CPU Architecture:	Any

Description:
I know this is a slightly older version of mysql (3.23) but since the current is 4.1 and is within 2 releases, I thought some others might benefit from this.

Assume the following table definition.
+-----------+---------------------+------+-----+---------+----------------+
| Field         | Type                      | Null    | Key | Default   | Extra               |
+-----------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned       |         | PRI  | NULL      | auto_increment |
| username  | varchar(64)             |          |       |             |                       |
| timestamp | timestamp(14)          | YES   |       | NULL      |                       | 
+-----------+---------------------+------+-----+---------+----------------+

Now examine the following rows which were entered with the following SQL:
insert into Responses values (NULL,'someusername',NULL)

+------+------------+----------------+
| id      | username   | timestamp         |
+------+------------+----------------+
| 1796 | bob            |20050102230200 |
| 1797 | rocky         |20050201084220 |
| 1798 | gino           |20050103085536 |
+------+------------+----------------+

You can see from the primary key id that the 3 selected rows are in 
sequence and the timestamps for id 1796 and 1798 seem to be consistent
(January) but the row between shows the date as February.  All 3 rows were entered in January yet the timestamp for 1797 is clearly wrong.  I'm not positive but I believe this generally occurs near the beginning of the month as we have seen this last year and in other months other than January.

How to repeat:
Insert NULL in the timestamp field for MySQL 3.23 during the 1st or 2nd of each month.  Table type is MyISAM.

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

MySQL uses system clock for TIMESTAMP values as well as for NOW() and CURDATE() functions. Values are as consistant as the system clock is.

Thank you Alexander for your information.  You may indeed be correct.  It may not be a bug.  I can't see how that is the case though.  If it were something wrong with the system time on the server, this same behavior would also appear in our logs (sendmail, apache, etc.) and it does not.  Therefore I do not feel it is as a result of the system time being wrong.

We are not explicity inserting the time as a string either.  We are using NULL for that field (and according to the documentation that should be OK) and letting MySQL determine the correct time.  Now if there was something with the table definition or table type that was incorrect, it would make more sense but I can't see how that would only affect it less than 1% of the time.

I'll followup in a support forum but I still feel this is a MySQL bug.

Thank you.

Robby