Bug #103228 Error when UTC_TIMESTAMP set for ON UPDATE
Submitted: 6 Apr 17:46 Modified: 7 Apr 19:40
Reporter: John Carew Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.23 OS:CentOS (8)
Assigned to: CPU Architecture:x86

[6 Apr 17:46] John Carew
Description:
When creating a table with a DATETIME column having (UTC_TIMESTAMP()) as it's ON UPDATE, an error is thrown from MySQL. We can use it as a default, but fails when ON UPDATE?

How to repeat:
CREATE TABLE `test_timestamp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `test_timestamp` 
  MODIFY COLUMN created DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()),
  MODIFY COLUMN modified DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()) ON UPDATE (UTC_TIMESTAMP());

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(UTC_TIMESTAMP())' at line 3

Suggested fix:
Since this functionality is allowed for default column setting, should also be allowed for ON UPDATE.
[7 Apr 7:38] MySQL Verification Team
Hello John Carew,

Thank you for the report and test case.

regards,
Umesh
[7 Apr 7:44] MySQL Verification Team
- 8.0.23
IMHO if it allows for automatically initialization then and it should support for update also.

Other than UTC_TIMESTAMP(), most of the synonyms listed here works - https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

Also, a doc bug as I see no mention of UTC_TIMESTAMP() in automatically initialization in above manual page.

-
CREATE TABLE `test_timestamp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `test_timestamp` 
  MODIFY COLUMN created DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()),
  MODIFY COLUMN modified DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()) ON UPDATE UTC_TIMESTAMP();

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UTC_TIMESTAMP()' at line 3
[7 Apr 19:34] Roy Lyseng
Posted by developer:
 
This does not seem to be a bug.

Apparently, only CURRENT_TIMESTAMP can be specified with ON UPDATE.
The manual specifies this in chapter "Automatic Initialization and Updating for TIMESTAMP and DATETIME" :

"Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP and DATETIME. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value (for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00')."

But there seems to be something missing from documentation. This chapter only mentions that constant values and CURRENT_TIMESTAMP can be used, but DEFAULT has been extended beyond that. In addition, it seems that the chapter on CREATE TABLE lacks documentation for the ON UPDATE clause.
[7 Apr 19:40] John Carew
So this would then be a feature request to allow ON UPDATE work life DEFAULT, as we need UTC times when things are modified.