MySQL Bugs: #32401: Replication generates error 1292 Incorrect datetime value

Bug #32401	Replication generates error 1292 Incorrect datetime value
Submitted:	14 Nov 2007 23:51	Modified:	16 Nov 2007 19:15
Reporter:	Tom Ward	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.11	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
My master server is v4.1.7 replicating to v5.1.11.  When starting the slave, I get an error when the replication server tries to insert a record that is valid in the master.  The error is:

Error 'Incorrect datetime value: '' for column 
'DateSentToClient' at row 1' on query. Default database: 'qcdtracker_v2'. Query: 
'insert into qcdt_transmittals 
(TransmittalId,ProjectId,CategoryId,JobId, TransmittalCreateUserId, 
DateCreated, DateSentToClient, DateReceivedFromClient, TransmittalPostedUserId, 
DatePosted, TransmittalType, TransmittalDetail, TransmittalImage, TransmittalResponse) 
VALUES 
(0,35,2350,92569,555,
'2007-11-14 10:10:37','','',0,
'',1249,'Fix it','','')'

When I run the insert stmt on the slave using NULL for the columns that contain the word 'Date', it works.  They are all datetime datatypes and it matched the master exactly.  Is there a way to tell the replication to use NULL instead of an empty string?  Is this a version difference?

How to repeat:
Create this table in your master then add the row above.  Keys and constraints have been removed.

DROP TABLE IF EXISTS `qcdtracker_v2`.`qcdt_transmittals`;
CREATE TABLE  `qcdtracker_v2`.`qcdt_transmittals` (
  `TransmittalId` int(10) unsigned NOT NULL auto_increment,
  `ProjectId` int(10) unsigned NOT NULL default '0',
  `CategoryId` int(10) unsigned default NULL,
  `JobId` int(10) unsigned default NULL,
  `TransmittalCreateUserId` int(10) unsigned NOT NULL default '0',
  `DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `DateSentToClient` datetime default NULL,
  `DateReceivedFromClient` datetime default NULL,
  `TransmittalPostedUserId` int(10) unsigned default NULL,
  `DatePosted` datetime default NULL,
  `TransmittalNumber` int(10) unsigned NOT NULL default '0',
  `TransmittalType` int(10) unsigned NOT NULL default '0',
  `TransmittalDetail` longtext NOT NULL,
  `TransmittalImage` varchar(255) default NULL,
  `TransmittalResponse` longtext,
  `Enabled` tinyint(1) NOT NULL default '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thank you for the report.

But version 5.1.11 is old. Please upgrade to current version 5.1.22-rc, try with it and inform us if the problem still exists.

Corrected MySQL server version from 5.1.11 to 5.0.37.

Since the replication server is backing up a production database, I prefer not to use a release candidate as per your release notes.  Is there another version of 5.1.x that would correct the issue I'm having?

Thank you for the feedback.

But version 5.0.37 is quite old too. Please upgrade to version 5.0.45 and if you can repeat the problem provide accurate table definition with all keys.

Resolved by changing the replication server to V4.1.22.