MySQL, the strange case of a timestamp field

I discovered a feature (or bug) of MySQL timestamp fields. Maybe is documented in some place what I not read yet:

When I add a new timestamp field to a table, MySQL magically adds some features to new timestamp field like a “trigger” and a default value to CURRENT_TIMESTAMP.

There is test-case script:

-- 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)

What happened? I dont know.

The new table structure is:

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

It only happens if the table doesn’t have another timestamp field yet.

Now, this is useful? maybe. This is a bug? maybe.

Update: This is a feature documented in https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html, sorry about my lapsus-post.

6 Responses to MySQL, the strange case of a timestamp field

  1. Hi,

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

  2. 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

  3. 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.

  4. 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. “

  5. 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;

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">

Go back to top