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
Triage: Triaged: D3 (Medium) / R3 (Medium) / E2 (Low)

[14 Feb 2006 18:48] Peter Laursen
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.
[15 Feb 2006 11:48] Valeriy Kravchuk
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.
[16 Feb 2006 16:49] Peter Laursen
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!
[16 Feb 2006 16:59] Peter Laursen
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
[19 Apr 2006 17:40] Valeriy Kravchuk
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.
[19 Apr 2006 17:40] Valeriy Kravchuk
Server bug (or feature request), not documentation issue.
[4 Oct 2010 17:08] Sam Chet
Is this bug fixed? If yes, what version? Its happening for me on 5.0.41.
[4 Oct 2010 17:20] Peter Laursen
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
[6 Oct 2010 15:20] Larry Irwin
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.
[12 Jan 2011 21:01] Peter Laursen
@Omer .. what is 'Farawaystani' to me!
[4 May 2011 17:00] Nicholas Lopez
Reproducible via Peter Laursen's steps in 5.5.8
[20 Dec 2011 19:42] John Allison
It's been almost 5 years. Can we get this assigned to somebody?
[10 May 2012 17:30] Paul Dubois
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.
[6 Nov 2017 16:39] Tom Riddle
More than 11 years and this bug is still not fixed.
[6 Nov 2017 18:39] Ståle Deraas
Tom, which version are you using? According to the comment from Paul above, this issue was fixed in 5.6.6.