Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?

Question

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

It's actually much worse than what the error message make it look to be. You cannot define a column with `CURRENT_TIMESTAMP` in `DEFAULT` or `ON UPDATE` clause once there's a column with `TIMESTAMP` data type, no matter if it got an extra clause! — Nicolas Buduroi, Jan 14 '11 at 04:11
So this work: `CREATE TABLE foo (created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMP)`, but not this: `CREATE TABLE foo (updated_on TIMESTAMP, created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP)` — Nicolas Buduroi, Jan 14 '11 at 04:14
@NicolasBuduroi Not if the first `timestamp` column is nullable i.e `null`. If the first `timestamp` column is `not null` then by default `DEFAULT CURRENT_TIMESTAMP` and `ON UPDATE CURRENT_TIMESTAMP` will be added. http://stackoverflow.com/a/13544181/2859238 — user104309, Feb 17 '17 at 20:08
@NicolasBuduroi Also not if the first `timestamp` column has an explicit default value set like `default '0000-00-00 00:00:00'`. If the column is nullable or explicitly default value is set,then `DEFAULT CURRENT_TIMESTAMP` and `ON UPDATE CURRENT_TIMESTAMP` will NOT be added — user104309, Feb 17 '17 at 20:59
Would really love to see an answer about the why? not how to get around it or how it's fixed now. Why was this ever implemented in this way? It seems like a total braindead way and i can't find any design/implementation that could possible be a reason for this restriction. I want to learn how dumb people program, so please teach me. — Lothar, Jan 11 '18 at 20:56

augustin · Accepted Answer · 2013-07-06T08:23:58.993

179

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

edited Jul 06 '13 at 08:23

answered Jul 05 '13 at 23:40

augustin

14,373
13
66
79

Check also @mooli answer below. Actually the first timestamp column has "default current_timestamp on update current_timestamp" automatically set (so it should be named updated_at). You just have to set created_at manually at insert time. – Gismo Ranas Dec 07 '15 at 11:38
1

what can we do if the mysql dump belongs to a 5.7 version and the setup needs to be run on a 5.4 – otc Feb 21 '16 at 12:45
1

@otc, you could edit the dump, or start again with 5.4 – Jasen Jan 10 '17 at 05:01

Lachezar Balev · Answer 2 · 2010-12-20T13:28:59.690

40

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!

edited Dec 20 '10 at 13:28

answered Dec 20 '10 at 13:20

Lachezar Balev

11,498
9
49
72

Wow, that really stinks. Hope we see a fix soon. – BoltClock Dec 20 '10 at 13:28
50

Another great MySQL limitation for us to enjoy! – Nicolas Buduroi Jan 14 '11 at 04:02
1

@gorn the easier solution/work around is the one from Scarlett below. – tihe May 30 '13 at 08:13

score 38 · Answer 3 · edited May 02 '22 at 09:07

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)

mooli · Answer 4 · 2013-01-12T00:16:21.450

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.

score 14 · Answer 5 · edited Jun 20 '16 at 17:07

14

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 ;

edited Jun 20 '16 at 17:07

user812786

4,302
5
38
50

answered Apr 21 '11 at 12:20

Feng-Chun Ting

3,371
3
17
11

I've always considered this method much less jankety than the half-implemented CURRENT_TIMESTAMP functionality. – TehShrike Jul 13 '11 at 17:45

score 4 · Answer 6 · answered Feb 17 '17 at 22:44

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:

1) 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

2) The first NOT NULL TIMESTAMP column without an explicit DEFAULT value like created_date timestamp default '0000-00-00 00:00:00' will be implicitly given a DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP and hence subsequent TIMESTAMP columns cannot be given CURRENT_TIMESTAMP on DEFAULT or ON UPDATE clause

CREATE 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;

score 1 · Answer 7 · answered Dec 20 '10 at 17:07

1

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.

answered Dec 20 '10 at 17:07

HLGEM

94,695
15
113
186

score 0 · Answer 8 · edited Jan 30 '14 at 11:23

0

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;

edited Jan 30 '14 at 11:23

Don Cruickshank

5,641
6
48
48

answered Jan 30 '14 at 11:03

Shoaib Qureshi

45
1

3

This will work but it doesn't solve the problem. created_at is 0 which is kind of useless. – CompEng88 Dec 12 '14 at 22:23
@ComputerEngineer88 This is the right solution for old mysql server. You should set the `created_at` column yourself. I think this is what op want to say intentionally. – Roger Nov 26 '15 at 02:29
this is exactly the official solution, documented here http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html – Rangi Lin Jan 06 '16 at 05:25

score 0 · Answer 9 · answered Jul 31 '16 at 20:08

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

Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT clause?

9 Answers9

Linked

Related