getting "only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause" error while it's used only once

Question

I am creating a database table with the following sql:

CREATE  TABLE `cs3_ds1` (
`ID` INT NOT NULL ,
`TIME` TIMESTAMP NOT NULL ,
`USER` VARCHAR(45) NOT NULL ,
`TIME1` TIMESTAMP NOT NULL ,
`TIME2` TIMESTAMP NOT NULL ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
 PRIMARY KEY (`ID`) )
 ENGINE = InnoDB

Although I am using the current timestamp as default in one column only, But I am getting the following error:

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

why I am getting this error?

Solution 1

MySQL auto initialises TIMESTAMP Columns with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, so your first column TIME has the CURRENT_TIMESTAMP Added as default. Therefore by the time you explicitly add a DEFAULT to a column one already exists. You either need to change the order your columns are defined:

CREATE  TABLE `silas`.`cs3_ds1` (
`ID` INT NOT NULL ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
`TIME` TIMESTAMP NOT NULL ,
`USER` VARCHAR(45) NOT NULL ,
`TIME1` TIMESTAMP NOT NULL ,
`TIME2` TIMESTAMP NOT NULL ,
 PRIMARY KEY (`ID`) )
 ENGINE = InnoDB

Or add defaults to your other timestamp columns:

CREATE  TABLE `silas`.`cs3_ds1` (
`ID` INT NOT NULL ,
`TIME` TIMESTAMP NOT NULL DEFAULT 0,
`USER` VARCHAR(45) NOT NULL  DEFAULT 0,
`TIME1` TIMESTAMP NOT NULL DEFAULT 0 ,
`TIME2` TIMESTAMP NOT NULL DEFAULT 0 ,
`INSERT_TIME` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
 PRIMARY KEY (`ID`) )
 ENGINE = InnoDB

See the MySQL Docs for further information.

Solution 2

Please check your MYSQL version. Your MYSQL version might be other thatn 5.6. This issue is happening due to a limitation in MYSQL version 5.5. You need to update the version to 5.6.

Below is how I enquired this issue

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