MySQL CURRENT_TIMESTAMP as DEFAULT

Question

While creating a table I am getting the following error:

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

The problem is that I don't actually have two columns TIMESTAMP with CURRENT_TIMESTAMP as default, neither I am using ON UPDATE clause.

The DDL query I'm trying to execute is

CREATE TABLE user(     /* Basic Information */     id INT NOT NULL AUTO_INCREMENT,     firstname VARCHAR(255) NOT NULL,     surname VARCHAR(255) NOT NULL,     email VARCHAR(255) NOT NULL UNIQUE,     username VARCHAR(255) NOT NULL UNIQUE,     password CHAR(40) NOT NULL,      /* System status information */     active BOOL NOT NULL DEFAULT FALSE,     validated BOOL NOT NULL DEFAULT FALSE,     date_validated TIMESTAMP,     date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,      /* Index */     PRIMARY KEY (id) ) Engine=InnoDB; 

What's causing the issue?

Answer 1

You can use two timestamp in one table. For default, use DEFAULT field first and then the rest timestamp fields.

Below query should work.

CREATE TABLE myTable  (  id INT,  date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   date_validated TIMESTAMP ); 

Demo at sqlfiddle