Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?
MysqlTimestampMysql Error-1293Mysql Problem Overview
Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause?
CREATE TABLE `foo` (
`ProductID` INT(10) UNSIGNED NOT NULL,
`AddedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`UpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=INNODB;
The error that results:
> Error Code : 1293 > > Incorrect table definition; there can > be only one TIMESTAMP column with > CURRENT_TIMESTAMP in DEFAULT or ON > UPDATE clause
Mysql Solutions
Solution 1 - Mysql
This limitation, which was only due to historical, code legacy reasons, has been lifted in recent versions of MySQL:
> Changes in MySQL 5.6.5 (2012-04-10, Milestone 8) > > Previously, at most one TIMESTAMP column per table could be > automatically initialized or updated to the current date and time. > This restriction has been lifted. Any TIMESTAMP column definition can > have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE > CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used > with DATETIME column definitions. For more information, see Automatic > Initialization and Updating for TIMESTAMP and DATETIME.
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html
Solution 2 - Mysql
I also wondered that long time ago. I searched a bit in my history and I think that this post: http://lists.mysql.com/internals/34919 represents the semi-official position of MySQL (before Oracle's intervention ;))
In short:
> this limitation stems only from the > way in which this feature is currently > implemented in the server and there > are no other reasons for its > existence.
So their explanation is "because it is implemented like this". Doesn't sound very scientific. I guess it all comes from some old code. This is suggested in the thread above: "carry-over from when only the first timestamp field was auto-set/update".
Cheers!
Solution 3 - Mysql
We can give a default value for the timestamp to avoid this problem.
This post gives a detailed workaround: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/
> create table test_table(
> id integer not null auto_increment primary key,
> stamp_created timestamp default '0000-00-00 00:00:00',
> stamp_updated timestamp default now() on update now()
> );
>
Note that it is necessary to enter nulls into both columns during "insert":
> mysql> select now() as before insert
;
> +---------------------+
> | before insert |
> +---------------------+
> | 2022-04-29 18:43:58 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> insert into test_table(stamp_created, stamp_updated) values(null, null);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from test_table;
> +----+---------------------+---------------------+
> | id | stamp_created | stamp_updated |
> +----+---------------------+---------------------+
> | 1 | 2022-04-29 18:43:58 | 2022-04-29 18:43:58 |
> +----+---------------------+---------------------+
> 1 row in set (0.00 sec)
>
> mysql> select now() as before sleep(3)
;
> +---------------------+
> | before sleep(3) |
> +---------------------+
> | 2022-04-29 18:43:58 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> DO SLEEP(3);
> Query OK, 0 rows affected (3.00 sec)
>
> mysql> select now() as before update
;
> +---------------------+
> | before update |
> +---------------------+
> | 2022-04-29 18:44:01 |
> +---------------------+
> 1 row in set (0.00 sec)
>
> mysql> update test_table set id = 2 where id = 1;
> Query OK, 1 row affected (0.00 sec)
> Rows matched: 1 Changed: 1 Warnings: 0
>
> mysql> select * from test_table;
> +----+---------------------+---------------------+
> | id | stamp_created | stamp_updated |
> +----+---------------------+---------------------+
> | 2 | 2022-04-29 18:43:58 | 2022-04-29 18:44:01 |
> +----+---------------------+---------------------+
> 1 row in set (0.00 sec)
Solution 4 - Mysql
Indeed an implementation fault.
The native approach in MySQL is to update a creation date yourself ( if you need one ) and have MySQL worry about the timestamp update date ? update date : creation date
like so:
CREATE TABLE tracked_data(
`data` TEXT,
`timestamp` TIMESTAMP,
`creation_date` TIMESTAMP
) ENGINE=INNODB;
On creation Insert NULL:
INSERT INTO tracked_data(`data`,`creation_date`) VALUES ('creation..',NULL);
NULL values for timestamp are interperted as CURRENT_TIMESTAMP by default.
In MySQL the first TIMESTAMP column of a table gets both DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
attribute, if no attributes are given for it.
this is why TIMESTAMP column with attributes must come first or you get the error described in this thread.
Solution 5 - Mysql
- Change data types of columns to datetime
- Set trigger
Such as:
DROP TRIGGER IF EXISTS `update_tablename_trigger`;
DELIMITER //
CREATE TRIGGER `update_tablename_trigger` BEFORE UPDATE ON `tablename`
FOR EACH ROW SET NEW.`column_name` = NOW()
//
DELIMITER ;
Solution 6 - Mysql
Combining various answers :
In MySQL 5.5, DEFAULT CURRENT_TIMESTAMP
and ON UPDATE CURRENT_TIMESTAMP
cannot be added on DATETIME
but only on TIMESTAMP
.
Rules:
- at most one
TIMESTAMP
column per table could be automatically (or manually[My addition]) initialized or updated to the current date and time. (MySQL Docs).
So only one TIMESTAMP
can have CURRENT_TIMESTAMP
in DEFAULT
or ON UPDATE
clause
-
The first
NOT NULL
TIMESTAMP
column without an explicitDEFAULT
value likecreated_date timestamp default '0000-00-00 00:00:00'
will be implicitly given aDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
and hence subsequentTIMESTAMP
columns cannot be givenCURRENT_TIMESTAMP
onDEFAULT
orON UPDATE
clauseCREATE TABLE
address
(id
int(9) NOT NULL AUTO_INCREMENT,village
int(11) DEFAULT NULL,created_date
timestamp default '0000-00-00 00:00:00',-- Since explicit DEFAULT value that is not CURRENT_TIMESTAMP is assigned for a NOT NULL column, -- implicit DEFAULT CURRENT_TIMESTAMP is avoided. -- So it allows us to set ON UPDATE CURRENT_TIMESTAMP on 'updated_date' column. -- How does setting DEFAULT to '0000-00-00 00:00:00' instead of CURRENT_TIMESTAMP help? -- It is just a temporary value. -- On INSERT of explicit NULL into the column inserts current timestamp.
--
created_date
timestamp not null default '0000-00-00 00:00:00', // same as above--
created_date
timestamp null default '0000-00-00 00:00:00', -- inserting 'null' explicitly in INSERT statement inserts null (Ignoring the column inserts the default value)! -- Remember we need current timestamp on insert of 'null'. So this won't work.--
created_date
timestamp null , // always inserts null. Equally useless as above.--
created_date
timestamp default 0, // alternative to '0000-00-00 00:00:00'--
created_date
timestamp, -- first 'not null' timestamp column without 'default' value. -- So implicitly adds DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. -- Hence cannot add 'ON UPDATE CURRENT_TIMESTAMP' on 'updated_date' column.`updated_date` timestamp null on update current_timestamp,
PRIMARY KEY (
id
) ) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=utf8;
INSERT INTO address (village,created_date) VALUES (100,null);
mysql> select * from address;
+-----+---------+---------------------+--------------+
| id | village | created_date | updated_date |
+-----+---------+---------------------+--------------+
| 132 | 100 | 2017-02-18 04:04:00 | NULL |
+-----+---------+---------------------+--------------+
1 row in set (0.00 sec)
UPDATE address SET village=101 WHERE village=100;
mysql> select * from address;
+-----+---------+---------------------+---------------------+
| id | village | created_date | updated_date |
+-----+---------+---------------------+---------------------+
| 132 | 101 | 2017-02-18 04:04:00 | 2017-02-18 04:06:14 |
+-----+---------+---------------------+---------------------+
1 row in set (0.00 sec)
Other option (But updated_date
is the first column):
CREATE TABLE `address` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`village` int(11) DEFAULT NULL,
`updated_date` timestamp null on update current_timestamp,
`created_date` timestamp not null ,
-- implicit default is '0000-00-00 00:00:00' from 2nd timestamp onwards
-- `created_date` timestamp not null default '0000-00-00 00:00:00'
-- `created_date` timestamp
-- `created_date` timestamp default '0000-00-00 00:00:00'
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=132 DEFAULT CHARSET=utf8;
Solution 7 - Mysql
Well a fix for you could be to put it on the UpdatedDate field and have a trigger that updates the AddedDate field with the UpdatedDate value only if AddedDate is null.
Solution 8 - Mysql
Try this:
CREATE TABLE `test_table` (
`id` INT( 10 ) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT 0,
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = INNODB;
Solution 9 - Mysql
This is the limitation in MYSQL 5.5 version. You need to update the version to 5.6.
Error
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