MySQL Bugs: #103228: Error when UTC_TIMESTAMP set for ON UPDATE

Bug #103228	Error when UTC_TIMESTAMP set for ON UPDATE
Submitted:	6 Apr 2021 17:46	Modified:	12 Feb 2022 2:17
Reporter:	John Carew	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	8.0.23	OS:	CentOS (8)
Assigned to:		CPU Architecture:	x86

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.

Hello John Carew,

Thank you for the report and test case.

regards,
Umesh

- 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

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.

So this would then be a feature request to allow ON UPDATE work life DEFAULT, as we need UTC times when things are modified.

We're now 5 releases later, any news on this being implemented?

Bug #106805 marked as duplicate of this one.

When is this bug going to be fixed?

I understand that ON UPDATE is specific to datetime datatype columns. And, if that's the case - it should support utc_timestamp and current_timestamp - as the choice of using TZ is upto the 'application' and that need not align with where the DB server is running.

So, this is a defect - isn't it?