MySQL 5.5 Alter Table TimeStamp default to Current Value, but it's being set to 0 instead

Question

I am trying to add a last_updated column to a table w/ preexisting data in it. After I execute the ALTER TABLE statement, I expect for the last_updated column for all existing data to be set to the CURRENT_TIMESTAMP.

ALTER TABLE TableName
ADD COLUMN last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP

However, the value is set to 0000-00-00 00:00:00 for all preexisting rows. Any subsequent inserts are created with CURRENT_TIMESTAMP as expected.

I am using MySQL 5.5. Are my expectations incorrect here? Or am I doing something wrong?

Answer 1

Well, this is an old bug Bug #68040: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT CURRENT_TIMESTAMP still inserts zero, solved from version 5.6.11 Changes in MySQL 5.6.11 (04/18/2013).

In 5.5, with a 13.5 SQL Syntax for Prepared Statements can avoid the problem with some more code.

Example:

SET @`ddl` := CONCAT('ALTER TABLE `TableName`
                   ADD COLUMN `last_updated` TIMESTAMP NOT NULL
                   DEFAULT \'', CURRENT_TIMESTAMP(), '\'
                   ON UPDATE CURRENT_TIMESTAMP;');
PREPARE `stmt` FROM @`ddl`;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;

ALTER TABLE `TableName`
MODIFY COLUMN `last_updated` TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;

SQL Fiddle demo