MySQL ALTER Table query not working. Default Null, on update current timestamp

I’m trying to change the default value of column updated_at, which is a datetime type. What i want is:

Allow Null =  True
Default = Null
On Update =  Current Timestamp

Here is my query attempt:

ALTER TABLE billing_payment_flow MODIFY updated_at datetime(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)

I run the query and then when i check the table, nothing has been changed?

Where did i go wrong in query?

Answer

You can try the query below. I expect that the problem occurs due to the size specifier (6) in your statement, this depends on the MySQL version you’re using. If you want to use DATETIME(6) you need MySQL 5.7 or later.

ALTER TABLE `billing_payment_flow`
MODIFY COLUMN `updated_at` DATETIME NULL
DEFAULT NULL 
ON UPDATE CURRENT_TIMESTAMP;

This works for me, tested it with the following script in MySQL 8.0:

CREATE TABLE `billing_payment_flow`(`ID` int, `updated_at` DATETIME(6));

INSERT INTO `billing_payment_flow`(`ID`) values(1);

ALTER TABLE `billing_payment_flow`
MODIFY COLUMN `updated_at` DATETIME(6) NULL
DEFAULT NULL 
ON UPDATE CURRENT_TIMESTAMP(6);

UPDATE `billing_payment_flow` SET `ID` = 2 WHERE `ID` = 1;

SELECT * FROM `billing_payment_flow`;

Result:

ID  updated_at
2   2021-09-14 10:55:55.891703