MySQL Forums
Forum List  »  PHP

Unexpected Data truncated for Date
Posted by: James Cobban
Date: June 12, 2019 02:08PM

I do not understand what MySQL is objecting to in the following:

Fatal error: Uncaught Exception: Death::save: 'UPDATE Deaths SET `d_originalvolume`='', `d_originalpage`='', `d_originalitem`=NULL, `d_calcdate`='1872-07-31', `d_idir`=131443, `d_calcbirth`='1872-02-29', `d_mothername`='[Elizabeth Griffiths]',D_ChangedBy='jcobban',D_ChangeDate=CURDATE() WHERE D_RegDomain='CAON' AND D_RegYear=1872 AND D_RegNum=25219', Array ( [0] => 01000 [1] => 1265 [2] => Data truncated for column 'd_calcdate' at row 1 )

July always has 31 days so the date it is objecting to, '1982-07-31', is valid. The server didn't complain about the value assigned to d_calcbirth, although it may just not have gotten to it yet, which is also valid because 1872 was a leap year and therefore February had 29 days. Furthermore "truncated" is not how I would interpret the action taken by MySQL in this case since it completely discards the supplied value and replaces it with a value that retains nothing from the supplied value. For example "truncated" would be appropriate if the server discarded just part of the date, just as with a string field a value which exceeds the maximum length has the excess portion discarded.

The command is being issued using PHP PDO with a prepared statement. The diagnostic message has the values from the parameter passed to $pdostmt->execute substituted for clarity

CREATE TABLE `Deaths` (
`D_RegDomain` VARCHAR(4) NOT NULL DEFAULT 'CAON',
`D_RegYear` INT(11) NOT NULL DEFAULT '2009',
`D_RegNum` INT(11) NOT NULL DEFAULT '0',
`D_RegCounty` CHAR(3) DEFAULT NULL,
`D_RegTownship` VARCHAR(64) DEFAULT NULL,
`D_MsVol` INT(4) DEFAULT '0',
`D_Surname` VARCHAR(32) DEFAULT NULL,
`D_SurnameSoundex` CHAR(4) DEFAULT NULL,
`D_GivenNames` VARCHAR(48) DEFAULT NULL,
`D_Sex` CHAR(1) DEFAULT NULL,
`D_Place` VARCHAR(128) DEFAULT NULL,
`D_Date` VARCHAR(32) DEFAULT NULL,
`d_calcDATE` INT(10) DEFAULT NULL,
`D_Age` VARCHAR(16) DEFAULT NULL,
`D_BirthDate` VARCHAR(32) DEFAULT NULL,
`d_calcbirth` INT(10) DEFAULT NULL,
`D_Occupation` VARCHAR(64) DEFAULT NULL,
`D_MarStat` CHAR(1) DEFAULT NULL,
`D_BirthPlace` VARCHAR(128) DEFAULT NULL,
`D_ResPlace` VARCHAR(16) DEFAULT NULL,
`D_ResOnt` VARCHAR(16) DEFAULT NULL,
`D_ResCan` VARCHAR(16) DEFAULT NULL,
`D_Cause` VARCHAR(255) DEFAULT NULL,
`D_Duration` VARCHAR(64) DEFAULT NULL,
`D_Phys` VARCHAR(48) DEFAULT NULL,
`D_PhysAddr` VARCHAR(128) DEFAULT NULL,
`D_Informant` VARCHAR(48) DEFAULT NULL,
`D_InfRel` VARCHAR(16) DEFAULT NULL,
`D_InfOcc` VARCHAR(32) DEFAULT NULL,
`D_InfRes` VARCHAR(128) DEFAULT NULL,
`D_Religion` VARCHAR(32) DEFAULT NULL,
`D_FatherName` VARCHAR(48) DEFAULT NULL,
`D_FatherBPlce` VARCHAR(128) DEFAULT NULL,
`D_MotherName` VARCHAR(48) DEFAULT NULL,
`D_MotherBPlce` VARCHAR(128) DEFAULT NULL,
`D_HusbandName` VARCHAR(48) DEFAULT NULL,
`D_Remarks` LONGTEXT,
`D_BurPlace` VARCHAR(128) DEFAULT NULL,
`D_BurDate` VARCHAR(48) DEFAULT NULL,
`D_Undertkr` VARCHAR(48) DEFAULT NULL,
`D_UndertkrAddr` VARCHAR(128) DEFAULT NULL,
`D_RegDate` VARCHAR(16) DEFAULT NULL,
`D_Registrar` VARCHAR(128) DEFAULT NULL,
`D_RecordedBy` VARCHAR(64) DEFAULT NULL,
`D_Image` VARCHAR(256) NOT NULL DEFAULT '',
`D_IDIR` INT(10) UNSIGNED DEFAULT NULL,
`D_ChangedBy` VARCHAR(64) NOT NULL DEFAULT '',
`D_ChangeDate` DATE DEFAULT NULL,
`D_OriginalVolume` VARCHAR(16) DEFAULT NULL,
`D_OriginalPage` VARCHAR(11) DEFAULT NULL,
`D_OriginalItem` INT(11) DEFAULT NULL,
PRIMARY KEY (`D_RegDomain`,
`D_RegYear`,
`D_RegNum`),
KEY `D_Surname` (`D_Surname`),
KEY `D_SurnameSoundex` (`D_SurnameSoundex`),
KEY `D_GivenNames` (`D_GivenNames`),
KEY `D_IDIR` (`D_IDIR`),
KEY `D_ChangeDate` (`D_ChangeDate`) )
ENGINE=InnoDB DEFAULT CHARSET=utf8

Options: ReplyQuote


Subject
Written By
Posted
Unexpected Data truncated for Date
June 12, 2019 02:08PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.