When UPDATE Doesn't

Several months ago I became aware of the ON DUPLICATE KEY UPDATE clause of the INSERT MySQL command. This meant that I could use a single command to insert and/or update a database record, without any additional code to determine whether or not I needed to INSERT or UPDATE. Couple that with the ON UPDATE CURRENT_TIMESTAMP option for a timestamp field and I can turn this:

$query = sprintf("SELECT status, published FROM users WHERE id = '%s'", mysql_real_escape_string($id, $dbh));
$result = @mysql_query($query, $dbh);

if (@mysql_num_rows($result)) {
  $query = sprintf("UPDATE users SET status='%s',published='%s' WHERE id='%s'",mysql_real_escape_string($status, $dbh),mysql_real_escape_string($published, $dbh),mysql_real_escape_string($id, $dbh));
}
else {
  $query = sprintf("INSERT INTO users (id,status,published) VALUES ('%s','%s','%s')",mysql_real_escape_string($id, $dbh),mysql_real_escape_string($status, $dbh),mysql_real_escape_string($published, $dbh));
}
$result = @mysql_query($query,$dbh);

into this:

$query = sprintf("INSERT INTO users (id,status,published) VALUES ('%s','%s','%s') ON DUPLICATE KEY UPDATE status=VALUES(status),published=VALUES(published)",mysql_real_escape_string($id, $dbh),mysql_real_escape_string($status, $dbh),mysql_real_escape_string($published, $dbh));
$result = @mysql_query($query,$dbh);

Or so I thought.

If I had hair left, I’d have been pulling it out when I couldn’t figure out why that timestamp field wouldn’t update. I already knew the command worked on another project; it just wouldn’t work here. I echoed $query; it looked good. No error from MySQL.

I finally found the problem after reading up on UPDATE. There’s this sneaky optimization in MySQL: “If you set a column to the value it currently has, MySQL notices this and does not update it.”

Bingo! I was sending information to the database that hadn’t changed, so MySQL wasn’t updating the record. Since the record wasn’t updated, ON UPDATED CURRENT TIMESTAMP didn’t come into play. This played havoc with my data caching, because now I was checking an external source and not updating when I did that. The correct code for this instance, where I need to track the last request, is:

$query = sprintf("INSERT INTO users (id,status,published,requested) VALUES ('%s','%s','%s',NOW()) ON DUPLICATE KEY UPDATE status=VALUES(status),published=VALUES(published),requested=VALUES(requested)",mysql_real_escape_string($id, $dbh),mysql_real_escape_string($status, $dbh),mysql_real_escape_string($published, $dbh));
$result = @mysql_query($query,$dbh);

This explicitly updates the timestamp field (requested) when the query is made, so even if the data hasn’t changed I can keep track of my app’s last request to the external data source.