Simon Fontana Oscarsson Simon Fontana Oscarsson - 4 months ago 22
MySQL Question

MySQL Error "There can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause" even though I'm doing nothing wrong

CREATE TABLE AlarmHistory
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
value DOUBLE NOT NULL,
startedStamp TIMESTAMP NOT NULL,
finishedStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
);


When trying to create the above table I get the following error: "SQL Error (1293): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause".

My question is this a bug? Because sure, I have two TIMESTAMP columns, but only ONE of them have a default definition. When I remove startedStamp I have no errors.

Answer

Per the MySQL manual, version 5.5, Automatic Initialization and Updating for TIMESTAMP

With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.

CREATE TABLE t1 (
  ts TIMESTAMP
);

However,

With a constant, the default is the given value. In this case, the column has no automatic properties at all.

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0
);

So, this should work:

CREATE TABLE AlarmHistory
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    value DOUBLE NOT NULL,
    startedStamp TIMESTAMP DEFAULT 0 NOT NULL,
    finishedStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

fiddle

Comments