DBI mysql question

kiat has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I've a column in a mysql database that gets updated when it shouldn't have been. I've pasted the table specs and the relevant perl code here:

# table specs
CREATE TABLE tasks (
      member_id MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY,
      quota MEDIUMINT NOT NULL,
      logged TIMESTAMP NULL,
);

# perl code to decrease quota by 1

my $member_id = get_memberid();

my $sql = qq{ UPDATE tasks
   SET quota=quota-1
   WHERE member_id=?
};

# Assuming a connection via $dbh exists
$dbh->do($sql, undef, $member_id);
[download]

With the perl code above, 'quota' gets decremented by 1, which is correct, but 'logged' gets updated with the currect timestamp too, which is not what I intended.

Am I missing something?

Updated: Thanks! It makes sense now :)

Comment on DBI mysql question Download Code

Replies are listed 'Best First'.
Re: DBI mysql question by matija (Priest) on Mar 03, 2004 at 09:51 UTC
The timestamp type in MySQL is documented to do exactly what you describe: it gets updated every time one of the elements of the record gets changed. MySQL docs say: Automatic updating of the first `TIMESTAMP` column occurs under any of the following conditions: The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the `TIMESTAMP` column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) You explicitly set the `TIMESTAMP` column to NULL. (end quote) If you don't want that behaviour, you should use a `DATETIME` type, and explicitly set it to now() every time you want it updated. (`DATETIME` has exactly the same format as timestamp, it just doesn't have the special propperties.)	[reply]
Re: DBI mysql question by neniro (Priest) on Mar 03, 2004 at 09:43 UTC
Do not use a timestamp! A timestamp is updated everytime an entrie changes. Just use a date field.	[reply]
Re: DBI mysql question by Tomte (Priest) on Mar 03, 2004 at 09:43 UTC
`TIMESTAMP` is exactly doing what it is there for, check the mysql-documentation; it's a convinience column-type that gets updated everytime a change in a row occurs (on every update), so you don't have to. regards, tomte Hlade's Law: If you have a difficult task, give it to a lazy person -- they will find an easier way to do it.	[reply]
Re: DBI mysql question by markov (Scribe) on Mar 03, 2004 at 10:01 UTC
The specs say: the FIRST timestamp is automatically updated. It shows any change in the fields, like mtime. So you may consider adding a second timestamp to the field, which contains only explicitly updated stamps. The other thing what I have found to work is by adding logged to the update: `UPDATE tasks SET quota = quote -1, logged = logged WHERE member_id = ?` [download] It's a bit of a hack...	[reply] [d/l]
Re: DBI mysql question by castaway (Parson) on Mar 03, 2004 at 12:33 UTC
Ugh! Doesn't anyone else think thats icky? Why should a timestamp field update itself, without being told to? If it wants updating, it should have to be created with something like: logged TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP; .. Thats DB2 syntax, anyway.. Especially if one can allow NULL values, kinda silly I think.. hohum C.	[reply]
Re: Re: DBI mysql question by mpeppler (Vicar) on Mar 03, 2004 at 16:19 UTC
At least here the TIMESTAMP is actually time-related. With Sybase and MS-SQL a "timestamp" column is just an 8 byte binary value that bears no relation to the time or date, and it does get updated every time you modify the table. It is actually quite useful when using optimistic locking - you fetch the value of the timestamp along with your other columns, let the user make edits, and then before committing the edits you verify that the timestamp value is unchanged, thus guaranteeing that no one else has modified the row while the user was making the changes. Michael	[reply]