MySQL Bugs: #17392: ALTER TABLE add column TIMESTAMP on update CURRENT

Bug #17392	ALTER TABLE add column TIMESTAMP on update CURRENT_TIMESTAMP inserts ZERO-datas
Submitted:	14 Feb 2006 18:48	Modified:	10 May 2012 17:30
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.1 - probably any ?	OS:	Linux (Linux, WinXP)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
When adding a TIMESTAMP column to an existing table (with existing rows in it) with the definition: 

`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,  the values are created as 0000-00-00 00:00:00.

I would have expected CURRENT_TIMESTAMP to be inserted.

Is this a but or expected behaviour?  I don't find anything in the docs.

How to repeat:
USE TEST; 

CREATE TABLE `tablename1` (                                                       
   `id` bigint(20) NOT NULL auto_increment,                                        
   `string` varchar(50) default NULL,                                              
    PRIMARY KEY  (`id`)                                                             
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO tablename1 values (1,'a'), (2,'b');

ALTER TABLE tablename1   add column `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP  after `string`;

SELECT * from tablename1;

returns:
    id  string                 ts  
------  ------  -------------------
     1  a       0000-00-00 00:00:00
     2  b       0000-00-00 00:00:00
 

Suggested fix:
Insert CURRENT_TIMESTAMP to the new database TIMESTAMP fields.

Thank you for a problem report. Verified just as described with 5.0.19-BK on Linux. Looks like intended behaviour that should be documented explicitely.

I think this is inconsequent then:

Even after:
set SQL_mode = 'NO_ZERO_IN_DATE';

it still insert ZERO-dates.

A 'logical' behaviour would be to insert CURRENT_TIMESTAMP in the newly created columns of existing rows!

Correction/clarification: 

even after 
set SQL_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
and
set global SQL_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';

ZERO-dates are inserted

OK. Even if it was intended, it is incorrect. Everybody expects to get current timestamp of ALTER TABLE with that statement. So, it is a verified bug.

Server bug (or feature request), not documentation issue.

Is this bug fixed? If yes, what version? Its happening for me on 5.0.41.

not fixed in 5.1.51!

CREATE TABLE `111111` (
  `id` int(11) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT ...

alter table `test`.`111111`  add column `ts` timestamp DEFAULT current_timestamp NOT NULL after `id`;

SELECT * ... 

data: 

    id                 ts  
------  -------------------
     1  0000-00-00 00:00:00
     2  0000-00-00 00:00:00

Is there a work-around/trick for this bug?
It may not be critical in a general sense, but for application interfaces it is critical...
Now, the only solution is to:
ALTER TABLE mytable ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
UPDATE mytable SET ts='20101006111600';
Which requires 2 passes of all the altered tables.

@Omer .. what is 'Farawaystani' to me!

Reproducible via Peter Laursen's steps in 5.5.8

It's been almost 5 years. Can we get this assigned to somebody?

Noted in 5.6.6 changelog.

Using ALTER TABLE to add a TIMESTAMP column containing DEFAULT
CURRENT_TIMESTAMP in the definition resulted in a column containing
'0000-00-00 00:00:00', not the current timestamp.

More than 11 years and this bug is still not fixed.

Tom, which version are you using? According to the comment from Paul above, this issue was fixed in 5.6.6.