Automatically set timestamps for "create" and "modified"?

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?

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 ;

StackExchange Database Administrators Q#118086, answer score: 2