MySQL Bugs: #68040: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT CURRENT

Bug #68040	ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT CURRENT_TIMESTAMP still inserts zero
Submitted:	6 Jan 2013 2:08	Modified:	1 Mar 2013 16:51
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE t1i (i INT) ENGINE=InnoDB;
INSERT INTO t1i VALUES (1);
ALTER TABLE t1i ADD COLUMN ts TIMESTAMP 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
SELECT * FROM t1i;
i	ts
1	0000-00-00 00:00:00

Compare with MyISAM:

CREATE TABLE t1m (i INT) ENGINE=MyISAM;
INSERT INTO t1m VALUES (1);
ALTER TABLE t1m ADD COLUMN ts TIMESTAMP 
  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
SELECT * FROM t1m;
i	ts
1	2013-01-06 05:00:20

Preliminary analysis by Timour Katchaounov:

"it is an incomplete fix for MySQL's
Bug#11745578: 17392: ALTER TABLE ADD COLUMN TIMESTAMP DEFAULT CURRENT_TIMESTAMP INSERTS ZERO
They fixed only for MyISAM.
Their own test case for MySQL Bug#11745578 works incorrectly with InnoDB."

Indeed, if we run main.function_defaults with default storage engine InnoDB, we get

@@ -1508,7 +1508,7 @@
 ALTER TABLE t1 ADD COLUMN c6 DATETIME DEFAULT NOW() ON UPDATE NOW() AFTER c5;
 SELECT * FROM t1;
 a1	a2	a3	a4	a5	a6	b	c1	c2	c3	c4	c5	c6
-0000-00-00 00:00:00	1970-01-01 03:16:40	1970-01-01 03:16:40	NULL	1970-01-01 03:16:40	1970-01-01 03:16:40	1	0000-00-00 00:00:00	1970-01-01 03:16:40	1970-01-01 03:16:40	NULL	1970-01-01 03:16:40	1970-01-01 03:16:40
+0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	NULL	NULL	NULL	1	0000-00-00 00:00:00	0000-00-00 00:00:00	0000-00-00 00:00:00	NULL	NULL	NULL

@@ -3039,7 +3039,7 @@
 ALTER TABLE t1 ADD COLUMN c6 DATETIME(6) DEFAULT NOW(6) ON UPDATE NOW(6) AFTER c5;
 SELECT * FROM t1;
 a1	a2	a3	a4	a5	a6	b	c1	c2	c3	c4	c5	c6
-0000-00-00 00:00:00.000000	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000	NULL	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000	1	0000-00-00 00:00:00.000000	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000	NULL	1970-01-01 03:16:40.000000	1970-01-01 03:16:40.000000
+0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	NULL	NULL	NULL	1	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	0000-00-00 00:00:00.000000	NULL	NULL	NULL

How to repeat:
--source include/have_innodb.inc

CREATE TABLE t1 (i INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
ALTER TABLE t1 ADD COLUMN ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
SELECT * FROM t1;

verified as described!

Isn't this bug report of mine somewhat related?
http://bugs.mysql.com/bug.php?id=17392

Hi Peter, 

Certainly it is. As Timour's comment (quoted in the description) says, "it is an incomplete fix for MySQL's Bug#11745578: 17392 ..." -- there 17392 stands for your original bug report. 
From my experience, it's not a great idea to revive a bug report which was already handled, it causes confusion in fixed versions etc., so I've created a new one which is the 2nd generation of yours.

The fix for Bug11745578 only covered the case where ALTER TABLE creates
a new copy of the table,
      it forgot the case of "in-place ALTER", so it did not work for InnoDB.

Noted in 5.6.11, 5.7.1 changelogs.

ALTER TABLE tbl_name ADD COLUMN col_name TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP inserted 0000-00-00
00:00:00 rather than the current timestamp if the alteration was done
in place rather than by making a table copy.