2

I have currently following SQL table:

CREATE TABLE IF NOT EXISTS `xyz` (
  `id` bigint(20) NOT NULL,
  `guid` char(36) NOT NULL,
  `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts_modified` timestamp NULL DEFAULT NULL,
  ................
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT AUTO_INCREMENT=1 ;

ts_created is the timestamp when the entry was created. The default value is CURRENT_TIMESTAMP.

ts_modified is the timestamp when the entry was updated.

In phpMyAdmin, I could choose ON UPDATE CURRENT_TIMESTAMP for ts_modified. This would be exactly what I want.

But the error message is following:

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

The error message is clear to me, but it is not clear to me

1) why this constraint is existing, since the "default" value (on create) and the "on update" value are two different things.

2) Is there anything I could do, or do I have to add ts_modified = NOW() at every UPDATE statement?

2

From here:

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.

What can you do?

You can add a BEFORE UPDATE trigger and set the ts_modified to NOW() like this:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.xyz_BEFORE_UPDATE$$
USE `test`$$
CREATE DEFINER = CURRENT_USER TRIGGER `test`.`xyz_BEFORE_UPDATE` BEFORE UPDATE ON `xyz` FOR EACH ROW
BEGIN

SET NEW.ts_modified=NOW();

END
$$
DELIMITER ;
  • 1
    Thank you for this hint, and the trigger definition. It is the first time I am using triggers in MySQL ;) – Daniel Marschall Oct 16 '15 at 17:05
0

You could easily change the column type to datetime2. Your default values would continue to work.

Timestamp is a special column type and is used to generate an absolutely unique number within the entire database. If you need a date/time they recommend datetime2.

link link

  • Sorry, I forgot to mention I am using MySQL (although ENGINE=InnoDB suggests it). datetime2 is not existing in MySQL. And datetime is not necessarily UTC, which may lead to problems when it is not written&interpreted carefully. – Daniel Marschall Oct 15 '15 at 0:23
  • try UTC_Timestamp() or UTC_Date() – Brodie Brickey Oct 15 '15 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.