Home > Databases, MySQL > Error: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

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

August 4th, 2010 Leave a comment Go to comments

This is a really small article and i’m not yet sure if i’m going to write many of these  in the future. But lets get to the point itself :)

I got “Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause” error when creating a table as shown bellow with SQLyog probably the best MySql GUI available at the moment

CREATE TABLE classroom (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`modifyDate` TIMESTAMP ,
`createDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`)
)

this error is fixed by adding “NULL DEFAULT NULL” to the modifyDate field as in the code bellow

CREATE TABLE classroom (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`modifyDate` TIMESTAMP NULL DEFAULT NULL,
`createDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`)
)

i’ve got 5.1.41 MySql and this is probably a bug, and i have no idea if it’s fixed in later builds :)

Categories: Databases, MySQL Tags: , ,
  1. Thibs
    September 1st, 2010 at 00:42 | #1
    Reply | Quote

    I got a solution for your

    Try with this sample table + associated trigger :

    CREATE TABLE sample_table (
    id TINYINT(4) NOT NULL AUTO_INCREMENT,
    xxx VARCHAR(255) NOT NULL DEFAULT ‘value’,
    date_insert TIMESTAMP NULL DEFAULT NULL,
    date_lastupdate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
    );

    CREATE TRIGGER sample_table_on_insert BEFORE INSERT
    ON sample_table
    FOR EACH ROW
    SET NEW.date_insert = CURRENT_TIMESTAMP

    You can test it with :

    INSERT INTO sample_table (xxx) VALUES (‘test’);

    wait a few seconds and try

    UPDATE sample_table SET xxx=’test2′ WHERE xxx=’test’

    The compare the dates

  2. Yervand Aghababyan
    September 1st, 2010 at 01:30 | #2
    Reply | Quote

    yeah, I know that to use a “modify date” in a table you have to write a trigger or update the date from the client application :)

  3. Richard
    October 18th, 2011 at 13:53 | #3
    Reply | Quote

    I had the same error on MySQL 5.5.16 with the following query (column names replaced):

    CREATE TABLE IF NOT EXISTS `TESTDATABASE`.`TEST` (
    `A` INT NOT NULL AUTO_INCREMENT ,
    `B` INT NOT NULL ,
    `C` TINYINT NOT NULL ,
    `D` TIMESTAMP NOT NULL ,
    `E` TIMESTAMP NOT NULL ,
    `F` TIMESTAMP NULL ,
    `G` TINYINT NOT NULL ,
    `H` TIMESTAMP NULL ,
    `I` TINYINT NULL ,
    `J` INT NOT NULL ,
    `K` INT NOT NULL ,
    `L` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ,
    `M` TIMESTAMP NULL ,
    `N` BIT NOT NULL DEFAULT 1 ,
    PRIMARY KEY (`A`)
    )
    ENGINE = InnoDB

    To get it to work I removed the DEFAULT CURRENT_TIMESTAMP() from column L:

    CREATE TABLE IF NOT EXISTS `CJRR`.`TEST` (
    `A` INT NOT NULL AUTO_INCREMENT ,
    `B` INT NOT NULL ,
    `C` TINYINT NOT NULL ,
    `D` TIMESTAMP NOT NULL ,
    `E` TIMESTAMP NOT NULL ,
    `F` TIMESTAMP NULL ,
    `G` TINYINT NOT NULL ,
    `H` TIMESTAMP NULL ,
    `I` TINYINT NULL ,
    `J` INT NOT NULL ,
    `K` INT NOT NULL ,
    `L` TIMESTAMP NOT NULL ,
    `M` TIMESTAMP NULL ,
    `N` BIT NOT NULL DEFAULT 1 ,
    PRIMARY KEY (`A`)
    )
    ENGINE = InnoDB

    I then went to alter the table through Workbench, and I can see that it has applied the default value CURRENT_TIMESTAMP to column D. So it seems that MySQL implicitly applies DEFAULT CURRENT_TIMESTAMP to the first column it comes across that is defined as TIMESTAMP NOT NULL.

    Subsequent TIMESTAMP NOT NULL columns get default value ’0000-00-00 00:00:00′ judging by what I see in the designer.

    Unfortunately, when I try to replace the default value for column D with ’0000-00-00 00:00:00′ and the default for column L with CURRENT_TIMESTAMP, and clicked Apply, the designer objected: “No changes to object were detected.”

    Knowing this, though, I got it to work as follows:

    CREATE TABLE IF NOT EXISTS `CJRR`.`TEST` (
    `A` INT NOT NULL AUTO_INCREMENT ,
    `B` INT NOT NULL ,
    `C` TINYINT NOT NULL ,
    `D` TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′,
    `E` TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′,
    `F` TIMESTAMP NULL ,
    `G` TINYINT NOT NULL ,
    `H` TIMESTAMP NULL ,
    `I` TINYINT NULL ,
    `J` INT NOT NULL ,
    `K` INT NOT NULL ,
    `L` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    `M` TIMESTAMP NULL ,
    `N` BIT NOT NULL DEFAULT 1 ,
    PRIMARY KEY (`A`)
    )
    ENGINE = InnoDB

    This is incorrect behaviour in my opinion, but it does at least explain the source of the error.

  1. No trackbacks yet.