My colleague Martin Hansson did most of the work and summarized it well in his blog. Thanks to him, since MySQL 5.6.5, it's possible to declare more than one TIMESTAMP column with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. And it's possible to have DATETIME columns with such attributes. Two limitations lifted!
But that is not the end of the story. TIMESTAMP was still special. Unlike other datatypes, if not declared with the NULL or NOT NULL attributes, it would automatically get NOT NULL. And the first TIMESTAMP column of the table would automatically get DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. Many people found these behaviors odd. For them, my colleague Gopal Shankar added a new option to the server, documented here. The old behavior, now depreciated, is still available if the option is not used.
But that is still not the end of the story. "CREATE TABLE ... SELECT" dealt strangely with columns having DEFAULT CURRENT_TIMESTAMP. Just look at this simple example, which shows results with version 5.6.9:
CREATE TABLE t1 ( t1a TIMESTAMP DEFAULT CURRENT_TIMESTAMP, t1b TIMESTAMP DEFAULT '2000-01-01 01:00:00' ); INSERT INTO t1 VALUES (); SELECT * FROM t1; t1a t1b 2013-02-19 18:12:41 2000-01-01 01:00:00
Now let's create a second table, which should have four columns: first, two extra columns t2a and t2b, then two columns filled with values selected from t1:
CREATE TABLE t2 ( t2a TIMESTAMP DEFAULT CURRENT_TIMESTAMP, t2b TIMESTAMP DEFAULT '2000-01-01 02:00:00' ) SELECT * FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `t2a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `t2b` timestamp NOT NULL DEFAULT '2000-01-01 02:00:00', `t1a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `t1b` timestamp NOT NULL DEFAULT '2000-01-01 01:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Now let's look at the content of t2:
SELECT * FROM t2; t2a t2b t1a t1b 0000-00-00 00:00:00 2000-01-01 02:00:00 2013-02-19 18:12:41 2000-01-01 01:00:00
The two first (extra) columns did not have their values specified, so their default should have been inserted. That's what nicely happened for t2b. But not for t2a: year zero again! That's one second problem: an extra column is not filled with its default if it is a function default.
I grouped those two problems under the name of Bug#16163936, and fixed them. Here are the results in 5.6.10:
SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `t2a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `t2b` timestamp NOT NULL DEFAULT '2000-01-01 02:00:00', `t1a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `t1b` timestamp NOT NULL DEFAULT '2000-01-01 01:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM t2; t2a t2b t1a t1b 2013-02-19 18:27:39 2000-01-01 02:00:00 2013-02-19 18:27:39 2000-01-01 01:00:00