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

Solution 1

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

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

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

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

1. Change data types of columns to datetime
2. 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 ;

View more solutions

Comments

[Nicolas Buduroi]

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]

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)

[user104309]

@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. stackoverflow.com/a/13544181/2859238

[user104309]

@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

[Lothar]

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.

[BoltClock]

Wow, that really stinks. Hope we see a fix soon.

[Nicolas Buduroi]

Another great MySQL limitation for us to enjoy!

[TehShrike]

I've always considered this method much less jankety than the half-implemented CURRENT_TIMESTAMP functionality.

[tihe]

@gorn the easier solution/work around is the one from Scarlett below.

[CompEng88]

This will work but it doesn't solve the problem. created_at is 0 which is kind of useless.

[Roger]

@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.

[Gismo Ranas]

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.

[Rangi Lin]

this is exactly the official solution, documented here dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.htm‌​l

[otc]

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

[Jasen]

@otc, you could edit the dump, or start again with 5.4