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

Question

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 1

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

Answer 2

This is the limitation in MYSQL 5.5 version. You need to update the version to 5.6.

I was getting this error in adding a table in MYSQL

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause My new MYSQL

table looks something like this.

create table table_name (col1 int(5) auto_increment primary key, col2 varchar(300), col3 varchar(500), col4 int(3), col5 tinyint(2), col6 timestamp default current_timestamp, col7 timestamp default current_timestamp on update current_timestamp, col8 tinyint(1) default 0, col9 tinyint(1) default 1);

After some time of reading about changes in different MYSQL versions and some of the googling. I found out that there was some changes that were made in MYSQL version 5.6 over version 5.5.

This article will help you to resolve the issue. http://www.oyewiki.com/MYSQL/Incorrect-table-definition-there-can-be-only-one-timestamp-column