[SOLVED] Proper workaround – there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Issue

I have this table:

CREATE TABLE IF NOT EXISTS news (
  id int(11) NOT NULL AUTO_INCREMENT,
  data text,
  date_published timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  date_edited timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

Giving me this error:

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

There are older posts regarding this problem, but i want a proper workaround, due to current database structure.

Version: libmysql – 5.5.41
Running on: 5.5.41-ubuntu.14.04.1

Solution

It’s a hard limitation. Best bet is making date_edited use CURRENT_TIMESTAMP and managing date_published in your application.

date_published timestamp NULL DEFAULT NULL,
date_edited timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Triggers might be an alternative option, but I try not to rely on stuff like that that can’t easily be version-controlled.

Answered By – ceejayoz

Answer Checked By – Marilyn (BugsFixing Volunteer)