Timestamp vs. Datetime

We encountered a strange behavior in our Rails application: created_on timestamp was sometimes younger than updated_at_.

For each item in our application, we had a created_on and an updated_at field in the database. These were supposed to be updated by the Rails framework at item creation and item update times respectively. However, created_on got to be updated more reliably, even for small flag changes. We tried

ActiveRecord::Timestamp.record_timestamps = false

but things got even worse: created_on kept changing with every little update but updated_at remained untouched. So, we sometimes had a strange situation that created_on was younger than updated_at.

So what could help? Read the good old manual? Well, sometimes you just have to…

And there it was: even though Rails does not seem to distinguish much between :datetime and :timestamp, our MySQL database has the following default behaviour:

The first TIMESTAMP column in table row automatically is updated to the current timestamp when the value of any other column in the row is changed, unless the TIMESTAMP column explicitly is assigned a value other than NULL.

So the innocently looking:

CREATE TABLE items (
...
created_on timestamp NOT NULL,
...

in create_tables.sql is completely wrong (don’t ask me how it got there, it has survived an unbelievable number of refactorings). This is made obvious by

mysql> show create table items;
...
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
...

What helped us in the end was:

mysql>	alter table items modify created_on datetime NOT NULL;
	update items set created_on=updated_at where created_on>updated_at;

Note: actually the error showed only for small flag changes, done by update_attributes. When using the full update, Rails keeps the correct old value of created_on.