MySQL column type "TIMESTAMP" implicitly includes "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"

Question

I've just spent a couple of hours tracking down this bug. Given the following SQL:

DROP DATABASE IF EXISTS db;
CREATE DATABASE db;
CREATE TABLE db.tbl (t1 TIMESTAMP) ENGINE=INNODB;
SHOW CREATE TABLE db.tbl;

The last line shows me:

'CREATE TABLE `tbl` (
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

Where on earth does the NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP come from? I didn't write any of that, and I very much do not want any of that, and I'm kinda lost for words that MySQL would make such a presumption.

Do I have some insane obscure configuration option turned on/off? Is this default behavior? It is a bug? In any case, how do I make MySQL behave sanely?

Answer 1

In MySQL 5.6.5 there are several updates regarding this initialization, you can see on this link (Automatic Timestamp Properties Before MySQL 5.6.5).

If you're using MySQL <= 5.6.5, in order to ignore this initialization you need to set the DEFAULT value to 0 or NULL with NULL allowed.

CREATE TABLE tbl
(
    field1 TIMESTAMP DEFAULT 0,
    field2 TIMESTAMP NULL DEFAULT NULL
)

If you're using MySQL >= 5.6.6, there is parameter called explicit_defaults_for_timestamp which is disabled by default. You can enable this setting or set the DEFAULT value to 0 or NULL, same approach for previous MySQL versions.

If you're using MySQL >= 8.0.2, then explicit_defaults_for_timestamp is enabled by default. This disables the non-standard behaviour (thankfully). Also, MySQL generates a warning when you disable this setting. So, for instance, if you don't define DEFAULT value for a TIMESTAMP column, it is automatically set to NULL.