Bug #101486 Error when UTC_TIMESTAMP set as default value
Submitted: 6 Nov 2020 3:53 Modified: 3 Dec 2020 17:58
Reporter: John Carew Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.22 OS:CentOS (8)
Assigned to: CPU Architecture:x86

[6 Nov 2020 3:53] John Carew 
Description:
When creating a table with a DATETIME column having (UTC_TIMESTAMP()) as it's default value, an error is thrown from MySQL upon any further ALTER TABLE statements.

ERROR 1067: Invalid default value

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

ALTER TABLE `test_timestamp` 
ADD COLUMN `test` VARCHAR(45) NULL;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1067: Invalid default value for 'created'

Suggested fix:
Recognize (UTC_TIMESTAMP()) expression as valid default value.
[6 Nov 2020 3:58] John Carew 
If I run this before the ALTER, it works; and then have to run another to set it back.

Prior to ALTER
ALTER TABLE test_timestamp
  CHANGE created created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHANGE modified modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

After ALTER
ALTER TABLE test_timestamp
  CHANGE created created datetime NOT NULL DEFAULT (UTC_TIMESTAMP()),
  CHANGE modified modified datetime NOT NULL DEFAULT (UTC_TIMESTAMP());
[6 Nov 2020 7:12] MySQL Verification Team 
Hello John Carew,

Thank you for the report and test case.

regards,
Umesh
[14 Nov 2020 2:17] John Carew 
Were you able to replicate the issue?
[16 Nov 2020 8:00] Ståle Deraas 
Yes, it was verified.
[16 Nov 2020 21:55] John Carew 
What is the expected fix timeframe for this? This is causing us to straight l drastically have to change update scripts to deploy updates to our dev databases.
[3 Dec 2020 17:58] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.24.

Creating a table containing a column with a nonconstant default
expression caused subsequent ALTER TABLE statments to fail.