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.

Solution 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);

Solution 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'.

Solution 3

You must remember that if no value was changed on update it won't set the current time stamp,

You have to set the value in query NOW() to set for the current time stamp !!!

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

Remember the value must change in order for the current time stamp to change.

Solution 4

If you want the record to automatically update the timestamp whenever the record is changed, here's the four simple steps you need to accomplish (could be all in one step, depending on if you're using command line or GUI to administer):

1. Create field to hold auto-updated timestamp (I typically call mine 'modified').
2. Specify field Type as 'TIMESTAMP'
3. Specify field Default as 'CURRENT_TIMESTAMP'
4. Specify field Extra as 'ON UPDATE CURRENT_TIMESTAMP'

Now the field that contains your timestamp will always be updated to the current timestamp anytime the record is updated.

Solution 5

To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. The order of the clauses does not matter. If both are present in a column definition, either can occur first. Any of the synonyms for CURRENT_TIMESTAMP have the same meaning as CURRENT_TIMESTAMP. These are CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP().

Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.

DEFAULT 0 do not work if the NO_ZERO_DATE SQL mode is enabled because that mode causes “zero” date values (specified, for example, as 0 '0000-00-00 00:00:00') to be rejected. Be aware that the TRADITIONAL SQL mode includes NO_ZERO_DATE.

In addition, you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values.

View more solutions

Comments

[barranquero]

More explanation on this... dev.mysql.com/doc/refman/5.0/en/timestamp.html

[Mark Taylor]

This just restates information already provided in other answers.

[testing]

I had to set the timestamp field to null in the update statement. Now every time the field is updated (even when the data has not really changed).

[Frungi]

Am I missing something, or is this answer saying to do what the OP was already doing, which failed anyway? He said that the timestamp field with ON UPDATE CURRENT_TIMESTAMP does not change on update, didn’t he? Good general information about timestamps; bad answer to this question.

[Thomas Decaux]

It's a common mistake true, "on update" clause is more administrative and should not be used like this. If there is no real changements on the data, it will not work.

[Mikeys4u]

Concise, easy to read....it helped me! I was only getting an update when it was inserted not updated. I had missed step 4.

[barranquero]

Yes, and there exists other synonyms for NOW() like LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP(), CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() - dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

[rjh]

Isn't it better to use UTC_TIMESTAMP() to avoid problems with the MySQL server timezone being different to your application?