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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  insert into test table stamp created  stamp updated  values null  null    Query OK  1 row affected  0 06 sec  mysql gt  select   from t5    ---- --------------------- ---------------------     id   stamp created         stamp updated          ---- --------------------- ---------------------     2   2009-04-30 09 44 35   2009-04-30 09 44 35    ---- --------------------- ---------------------  2 rows in set  0 00 sec    mysql gt  update test table set id   3 where id   2   Query OK  1 row affected  0 05 sec  Rows matched  1  Changed  1  Warnings  0   mysql gt  select   from test table   ---- --------------------- ---------------------    id   stamp created         stamp updated           ---- --------------------- ---------------------      3   2009-04-30 09 44 35   2009-04-30 09 46 59     ---- --------------------- ---------------------   2 rows in set  0 00 sec

User · Answer

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 gt  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 gt  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

User · Answer

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

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

Examples related to mysql

Examples related to timestamp

Examples related to mysql-error-1293