MySQL, the strange case of a timestamp field

Posted on January 9, 2013 Comments

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.

Related Posts :

Comments

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

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