MySQL ON UPDATE CURRENT_TIMESTAMP not updating

Question

I've got a table that looks like this:

CREATE TABLE IF NOT EXISTS `Hosts` ( `id` int(128) NOT NULL AUTO_INCREMENT, `IP` varchar(15) NOT NULL DEFAULT '', `Port` varchar(5) NOT NULL DEFAULT '', `Password` varchar(32) NOT NULL DEFAULT '', `Username` varchar(32) NOT NULL DEFAULT '', `Tid` varchar(32) NOT NULL DEFAULT '', `EquipType` varchar(64) NOT NULL DEFAULT '', `Version` varchar(128) DEFAULT NULL, `Status` varchar(10) NOT NULL DEFAULT '', `Location` varchar(128) NOT NULL DEFAULT '', `Lastconnection` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, `Lastbackup` date NOT NULL DEFAULT '0000-00-00', `Backupstatus` varchar(64) NOT NULL DEFAULT '', `Backupmsg` text, `Backupfile` varchar(30) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `IP` (`IP`), KEY `Tid` (`Tid`), KEY `EquipType` (`EquipType`), KEY `Status` (`Status`), KEY `Lastbackup` (`Lastbackup`), KEY `Backupstatus` (`Backupstatus`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=716 ; 

In my mind, this means that any time a row is updated, the field 'Lastconnection' should be stamped with the current timestamp. However, when I run something like:

update Hosts set Backupstatus = 'FAIL',  Backupmsg = 'Connection timed out' where Tid = 'SITE001' 

Lastconnection stays '0000-00-00 00:00:00'. There's either a database issue I'm not seeing, or I'm completely misunderstanding the 'ON UPDATE CURRENT_TIMESTAMP' clause.

Answer 1

Have you tried to use null for that field when updating?

You could also try setting default value to CURRENT_TIMESTAMP, rather than 0000-00-00 00:00:00.

Nevertheless, whenever I want to have creation and update time I always use the following:

... CREATED timestamp NOT NULL default '0000-00-00 00:00:00', UPDATED timestamp NOT NULL default now() on update now(), .... 

I use now(), because is an alias for CURRENT_TIMESTAMP and it is shorter. At the end, table structure gets CURRENT_TIMESTAMP, so don't worry.

The trick with CREATED field is to remember to use null on both fields for INSERT statements, for UPDATE statements it is not required:

INSERT INTO mytable (field1, field2, created, updated) VALUES ('foo', 'bar', null, null); 

Answer 2

It might be the case that the update statement doesn't change anything. If the row with Tid = 'SITE001' already has Backupstatus set to 'FAIL' and Backupmsg set to 'Connection timed out' (maybe, set by some previous backup attempt), then MySQL will skip this row and therefore won't change the Lastconnection timestamp.

Also, I see ON UPDATE CURRENT_TIMESTAMP more like an administrative feature to keep track of data changes. As a programmer, I would add the timestamp update explicitly:

update Hosts
set Backupstatus = 'FAIL', Backupmsg = 'Connection timed out', Lastconnection = NOW() where Tid = 'SITE001'.