Mysql 5.6: alter column to set default to current_timestamp

This should be so simple but I'm pulling my hair out trying to figure this out.

This example mimics the migrations I'll run - essentially it's add a new nullable column to the table, set the value to match another column, then default it to current_timestamp/not nullable going forward.

DROP TABLE IF EXISTS testing;
CREATE TABLE testing (id BIGINT(20), created_at DATETIME);
ALTER TABLE testing ADD COLUMN new_column DATETIME DEFAULT NULL;
UPDATE testing SET new_column=created_at;
ALTER TABLE testing ALTER new_column SET DEFAULT current_timestamp;
That very last command results in:

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 'current_timestamp' at line 1

Comments

[jhfgloria]

Just came across this topic and the actual problem is the missing parenthesis ( ). According to this report on the MySQL website, the correct command should be:
ALTER TABLE testing ALTER new_column SET DEFAULT(current_timestamp)
and not
ALTER TABLE testing ALTER new_column SET DEFAULT current_timestamp

Hope it helps someone looking for this issue.

[Katie Nelson]

The correct syntax for that line is:

ALTER TABLE testing MODIFY new_column datetime DEFAULT current_timestamp; 

You need the MODIFY keyword instead of ALTER.