31

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?

Jonnny
  • 4,939
  • 11
  • 63
  • 93
jameshfisher
  • 34,029
  • 31
  • 121
  • 167
  • 2
    You probably want `datetime` instead of `timestamp`. A `timestamp` is for versioning records and the default behaviour is updating that column if the record is updated. – juergen d Apr 04 '14 at 10:36
  • 2
    @juergend thanks. [The MySQL documentation](https://dev.mysql.com/doc/refman/5.1/en/datetime.html) does not make it at all clear that that is what the `TIMESTAMP` type is intended for. Whoever wrote that documentation should be taken outside and shot. – jameshfisher Apr 04 '14 at 10:44
  • I suppose you're aware of it but docs say «The TIMESTAMP data type offers automatic initialization and updating to the current date and time» and links to [Automatic Initialization and Updating for TIMESTAMP](https://dev.mysql.com/doc/refman/5.1/en/timestamp-initialization.html). Writer does not deserve more than a short sentence. – Álvaro González Apr 04 '14 at 11:16
  • @ÁlvaroG.Vicario I just stumbled upon this behaviour and I read the docs, but I didn't expect an *offer* being imposed on me ;-) – Olaf Dietsche Jun 26 '15 at 12:17

2 Answers2

18

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.

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Erico
  • 1,401
  • 9
  • 18
  • 1
    It is worth mentioning, that MySQL only adds these defaults for the _first_ TIMESTAMP column in the table, even before 5.6.5 – Hannele Nov 21 '16 at 18:39
3

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

Community
  • 1
  • 1
vhu
  • 12,244
  • 11
  • 38
  • 48
  • 1
    Thanks, though neither of those options look like what I want in my case: I want `TIMESTAMP NOT NULL` without a default value and without any crazy update trigger. How do I specify that? – jameshfisher Apr 04 '14 at 10:42
  • Those are the only options. – Álvaro González Apr 04 '14 at 11:18
  • @jameshfisher It night help to know what exact feature of TIMESTAMP that you need to use is missing from DATETIME. Perhaps we can think of an alternative. – Álvaro González Apr 04 '14 at 11:23
  • 2
    @ÁlvaroG.Vicario no, `DATETIME` is fine. It just annoys me that `TIMESTAMP` has these arbitrary restrictions, sadistically unintuitive specification options, and that the documentation is so unclear. – jameshfisher Apr 04 '14 at 11:32
  • I edited the answer to explain key difference between `DATETIME` and `TIMESTAMP`. – vhu Apr 04 '14 at 18:28