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.

Answer 2

Thos are default values which are being used even if not explicitly stated in the CREATE statement. If you want to avoid both, use t1 TIMESTAMP DEFAULT 0 or ts1 TIMESTAMP NULL DEFAULT NULL

Update

Prior to MySQL 5.6.5 you could only use TIMESTAMP in case you wanted to have column that is automatically updated when row is changed. Unfortunately this functionality was limited to MyISAM and isn't available on InnoDB tables.

MySQL 5.6.5 allows this with DATETIME as well. See other posts on this site for more details