MySql timestamp not updating correctly when using object->save()?
gorus
timestamp field being updated.
Say there is a table with the following field:
`date_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP
And the object class definition contains:
...
date_updated => {
type => 'timestamp',
default => 'now()',
not_null => 1,
},
...
__PACKAGE__->meta->allow_inline_column_values(1);
...
Then, somewhere in the code, you do:
object.title('new title');
object->save();
Checking the db shows that the title got updated, but date_updated is
not the latest timestamp. However, running a sql statement like
"update table_name set title='new title';" correctly updates the
date_updated field.
Here's a workaround:
object.title('new title');
object.date_updated(undef);
object->save();
This causes the default value of 'now()' to be used for date_updated.
But it's not a good solution--it seems that somehow, Rose should allow
MySql to update the timestamp correctly by itself. Has anyone else
run into this issue?
Thanks,
Anton
John Siracusa
> But it's not a good solution--it seems that somehow, Rose should allow
> MySql to update the timestamp correctly by itself.
You should also be able to do:
$o->save(changes_only => 1);
for a similar effect.
What's happening is that when you load(), you get the current value of
the timestamp column, and when you save(), the current value is saved
right back into the column. Apparently MySQL's "on update
CURRENT_TIMESTAMP" directive doesn't make it override an explicitly
provided value.
Anyway, a solution that lets you use plain old save() as well as
save(changes_only => 1) is to set an on_save trigger on the timestamp
column to set its value to undef prior to save()ing.
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Metadata/Column.pm#TRIGGERS
columns =>
[
...
date_updated => { type => 'timestamp', on_save => sub {
shift->date_updated(undef) } },
...
If that's a common occurrence in your code, you could make and
register your own custom column class that incorporates this trigger
by default, or override add_columns() or some other method in your
custom Metadata subclass to apply this trigger to all columns named
"date_updated," or whatever your convention might be, etc.
-John
gorus
know about the other ways, for future consideration.
-Anton
gorus
http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Tutorial.pod
we tried using 'now()' as the default value for timestamp:
...
type => 'timestamp',
default => 'now()',
on_save => sub{shift->dateUpdated(undef)},
...
However, when accessing $o->date_updated immediately after a save(),
the string value 'now()' is retrieved. Changing the object definition
to:
...
type => 'timestamp',
default => 'now',
on_save => sub{shift->dateUpdated(undef)},
...
results in the correct timestamp being retrieved. Note, the only
change is removing the parentheses after now--so, 'now()' and 'now'
result in different behavior after a save(). We can continue using
'now' as a workaround of course, but maybe you know what the issue is?
-Anton
John Siracusa
> Note, the only change is removing the parentheses after now--so,
> 'now()' and 'now' result in different behavior after a save(). We can
> continue using 'now' as a workaround of course, but maybe you know
> what the issue is?
Depending on the database, some values are passed through as-is to be
evaluated server-side. In your case (MySQL) the value "now()" is
passed through as-is and evaluated by the database server, but the
value "now" is evaluated client-side and the resulting date is passed
to the server.
The things evaluated client-side are called "keywords" in Rose::DB
parlance. Such keywords are defined in Rose::DB and its
database-specific subclasses. Example:
http://search.cpan.org/dist/Rose-DB/lib/Rose/DB/MySQL.pm#validate_timestamp_keyword
Values that are evaluated server-side must be re-load()ed if you want
to know what value the database actually ended up choosing.
-John
gorus
-Anton