Bug #43613 Got "zero" timestamp value instead of current_timestamp
Submitted: 12 Mar 2009 21:53 Modified: 15 Mar 2009 15:20
Reporter: Pavel Bazanov Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:6.0.2 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[12 Mar 2009 21:53] Pavel Bazanov
Description:
Hello,
We experienced a very strange behavior a few days ago.

We have the following table:

CREATE TABLE `boxes` (
  `ID` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `BoxN` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  `Length` INTEGER(11) DEFAULT '0',
  `Width` INTEGER(11) DEFAULT '0',
  `Height` INTEGER(11) DEFAULT '0',
  `ShipmentType` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  `CustomerID` INTEGER(11) DEFAULT '0',
  `PackPrice` INTEGER(11) DEFAULT '0',
  `Weight` DOUBLE DEFAULT '0',
  `TS` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `InternalBoxN` INTEGER(11) DEFAULT '0',
  `STModified` BIT(1) DEFAULT 0,
  `BoxTypeID` INTEGER(11) DEFAULT '0',
  `MaterialID` INTEGER(11) DEFAULT '0',
  `TSCreated` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ID`),
  KEY `InternalBoxN` (`InternalBoxN`)
)ENGINE=InnoDB
AUTO_INCREMENT=23145 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='InnoDB free: 347136 kB';

Take a look at TSCreated column.

And we have the only query that changes TSCreated column:

"INSERT INTO boxes SET ..., TSCreated = NOW(), ... "

It was written a couple of years ago and always worked fine. But a few days ago several rows in the table got "zero" timestamp ('0000-00-00 00:00:00') in TSCreated column. The rows with zero timestamp were all added in the same day. We fixed them manually and the problem didn't repeat anymore.

Do you have any ideas why it could happen?

How to repeat:
Don't know.
[12 Mar 2009 22:59] MySQL Verification Team
Thank you for the bug report. The only guess I could say you is to verify if that day regarding Daylight Saving Time in your timezone could caused invalid timestamps values in your query.
[15 Mar 2009 15:20] Pavel Bazanov
But how it could cause invalid timestamps?
How could NOW() return zero timestamp?