MySQL is not allowing ON UPDATE CURRENT_TIMESTAMP for a DATETIME field

Question

I have seen a lot of related questions, but I cannot place my finger on this specific question:

I have a MySQL table with both a TIMESTAMP (for when the field was created) and a DATETIME (for each time the field gets updated). It looks like this:

CREATE TABLE 'vis' (
ID BIGINT PRIMARY KEY AUTO_INCREMENT NOT NULL,
ENTRY VARCHAR(255),
AUTHOR VARCHAR(255),
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATED_AT DATETIME ON UPDATE CURRENT_TIMESTAMP,
UPDATED_BY VARCHAR(255)
)

When I try this though, the error I am getting is: (SQL Error: 1294 SQL State: HY000) - Invalid ON UPDATE clause for 'updated_at' field

Everywhere I have read (even on Stack Overflow) suggests I should be able to do this, yet I am getting this error. Perhaps there is another way to have a field that automatically updates the time each time I update it?

I am using MySQL Server 5.5.

Answer 1

DATETIME cannot use CURRENT_TIMESTAMP on update. Instead, change it to a TIMESTAMP.

Or, consider using a trigger for this situation: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html

EDIT: As in the comments (thanks @АлександрФишер!), this is no longer the case since MySQL 5.6.5, so another solution is to upgrade your server if possible.

Answer 2

That feature appears to have been introduced in 5.6. Works as expected on my default OS X install.

Reference: Automatic Timestamp Properties Before MySQL 5.6.5