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.

Your statement (after removing the last `,`) works as expected (MySQL 5.6.14). Are there any other columns? — Vatev, Apr 14 '14 at 07:34
Doesn't work in 5.5 though. What version are you (the OP) using? In 5.5, a `TIMESTAMP` with neither `DEFAULT` not `ON UPDATE` is treated as if it has *both* of them applied. — Damien_The_Unbeliever, Apr 14 '14 at 07:37
That's the latest mySQL version according to packages.ubuntu.com. I think I'll just fix it with a before insert trigger. Or how do I get the newer version? — Simon Fontana Oscarsson, Apr 14 '14 at 07:46

score 12 · Accepted Answer · answered Apr 14 '14 at 07:44

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

score 2 · Answer 2 · answered Jul 31 '16 at 20:11

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

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

2 Answers2

Linked