-- CREATING TABLE AND INSERT SOME DUMMY DATA
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM t;
+----+------+
| id | val  |
+----+------+
|  1 | foo  |
|  2 | var  |
+----+------+
2 ROWS IN SET (0.00 sec)
 
 
-- ADDING NEW TIMESTAMP FIELD AND ADD MORE DUMMY DATA
mysql> ALTER TABLE t ADD ts_field TIMESTAMP;
Query OK, 2 ROWS affected (0.35 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO t (val) VALUES ("foo 2") ,("var 2");
Query OK, 2 ROWS affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
-- THERE IS THE MAGIC:
mysql> SELECT * FROM t;
+----+-------+---------------------+
| id | val   | ts_field            |
+----+-------+---------------------+
|  1 | foo   | 0000-00-00 00:00:00 |
|  2 | var   | 0000-00-00 00:00:00 |
|  3 | foo 2 | 2013-01-09 23:20:01 |    <---
|  4 | var 2 | 2013-01-09 23:20:01 |    <---
+----+-------+---------------------+
4 ROWS IN SET (0.00 sec)

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(50) DEFAULT NULL,
  `ts_field` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

Comments

1. Hi,

   This is in fact a long time feature, and is well documented here: Automatic Initialization and Updating for TIMESTAMP.

2. It’s a feature
   http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

3. Ivan

   This is a feature (see eg http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html), If you don’t want this to happen you can use DATETIME instead (but then you also don’t get data stored in UTC; see this section http://dev.mysql.com/doc/refman/5.5/en/datetime.html for more information on the comparison between DATETIME and TIMESTAMP types.

   The critical paragraph reads:

   “With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP.”

   So because you haven’t specified either a DEFAULT or an ON UPDATE, you get both. Logical or not, at least it is documented.

   Hope that helps

   Regards Nigel

4. This is documented behavior: https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

   As you show, the first TIMESTAMP column of a table, will by default have the following properties

   “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”.

   If you want something different, you may explicitly specify another default value, e.g. “NULL DEFAULT NULL”.

   Or you can use DATETIME instead.

5. This is documented behaviour. Unexpected, but documented.

   https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

   “With neither DEFAULT CURRENT_TIMESTAMP nor ON UPDATE CURRENT_TIMESTAMP, it is the same as specifying both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. “

6. This behavior has existed for years. It is documented. But it is arguably odd, so in version 5.6 we have started offering a better behavior. This contains the details of the old and new behavior:
   http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
   Note that the new behavior is opt-in.
   I imagine that you are not using 5.6 yet; in that case the workaround is to write:
   ALTER TABLE t ADD ts_field TIMESTAMP NULL;