MySql cannot update a DATETIME field - no record found

Question

I have a table in MySql 5.6.10 defined as this:

When I do a select query (from HeidiSQL client) for a particular record filtering on the id_failed_delivery_log column, the record is found successfully, but when I use the same filter for the UPDATE command, then no record is found and no error reported:

When I update a different column using the same filter, the update works and I can see the updated value. Then there is an issue with an update to this particular column.

I've also tried updating with a time function instead of hard-coded date value, for example with the now() function but I still get the same result and the record is not found.

Could it be caused by the 'Default' value is set to CURRENT_TIMESTAMP?

It might be because you posted photos instead of textual codes. — FanoFN, Jul 19 '19 at 00:42
I've tested on my side with `CURRENT_TIMESTAMP` as default and the date value changed according the update statement. The `date_created` column will only change to `CURRENT_TIMESTAMP` if I update other columns. (on HeidiSQL) — FanoFN, Jul 19 '19 at 00:59

Vortanz · Accepted Answer · 2019-07-21T00:54:10.603

After further investigation I found the reason why I couldn't update that field. I was not totally familiarized with the database definition and I found that there was a trigger in the same schema that forced to keep the date_created column with the same value:

SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_ALL_TABLES';
DELIMITER //
CREATE TRIGGER `failed_delivery_log_before_update` BEFORE UPDATE ON 
`failed_delivery_log` FOR EACH ROW BEGIN

    SET NEW.date_created = OLD.date_created ; 

END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

I removed this trigger temporarily in order to test. Once removed, the updated worked fine. The trigger execution was not reported in the SQL client, so it was difficult to find out its execution.

MySql cannot update a DATETIME field - no record found

1 Answers1