MySQL 5.5 Alter Table TimeStamp default to Current Value, but it's being set to 0 instead -
i trying add last_updated column table w/ preexisting data in it. after execute alter table statement, expect last_updated column existing data set current_timestamp.
alter table tablename add column last_updated timestamp not null default current_timestamp on update current_timestamp however, value set 0000-00-00 00:00:00 preexisting rows. subsequent inserts created current_timestamp expected.
i using mysql 5.5. expectations incorrect here? or doing wrong?
well, old bug bug #68040: alter table add column timestamp default current_timestamp still inserts zero, solved version 5.6.11 changes in mysql 5.6.11 (04/18/2013).
in 5.5, 13.5 sql syntax prepared statements can avoid problem 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` @`ddl`; execute `stmt`; deallocate prepare `stmt`; alter table `tablename` modify column `last_updated` timestamp not null default current_timestamp on update current_timestamp;