14

I'd like some clarification on the behaviour of default values in MySQL 5.5 & 5.6. Lets say we have the following table on a MySQL 5.5 server:

CREATE TABLE `test` (
`TestColumn` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB DEFAULT CHARSET=utf8;

I am able to run the following query without issue:

INSERT INTO `test` VALUES (NULL);

Which creates the following row:

 TestColumn
 2014-02-20 14:55:05

Now if I repeat the same test on a MySQL 5.6 server, the insert fails:

Error Code: 1048
Column 'TestColumn' cannot be null

I understand that timestamp automatic initilisation has changed in 5.6 (http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html), but what I can't work out is how to replicate the behaviour seen in 5.5 in 5.6.

Not sure if it matters but our 5.6 server has explicit_defaults_for_timestamp set to ON.

Ideally what I'm after is a solution to this, but if anyone has a better understand and can explain it that would be useful as well.

Thanks.

EDIT: We are using MySQL 5.6.13

Gray
  • 115,027
  • 24
  • 293
  • 354
BombTodley
  • 537
  • 1
  • 5
  • 10
  • 1
    the first insert shouldn't have worked in the first place. You were *asking* the database to put a NULL value into not-null column, so 5.6 is right in rejecting that. –  Feb 20 '14 at 15:04
  • @a_horse_with_no_name the definition is that the column cannot be null - however, if a default is provided, it will use the default. Edit, scratch that – bear Feb 20 '14 at 15:09
  • @Shamil what version of MySQL are you using? Behaviour changed in 5.6.5 (we're using 5.6.13, I should've said that in the original post) – BombTodley Feb 20 '14 at 15:11
  • @user1095982 MySQL 5.5.32 test, MySQL 5.6.10 – bear Feb 20 '14 at 15:12
  • @Shamil what do you have explicit_defaults_for_timestamp set to? – BombTodley Feb 20 '14 at 15:13
  • @BombTodley false in both cases – bear Feb 20 '14 at 15:16
  • 1
    @Shamil: there was no default provided, the insert specifies an explicit `NULL` value. Using the default value is only applicable when the column is not specified at all or the keyword `DEFAULT` was used. –  Feb 20 '14 at 15:17
  • @a_horse_with_no_name the default is provided by `DEFAULT CURRENT_TIMESTAMP`, in 5.5 and downwards it works if not specified or if null (where not null is provided). – bear Feb 20 '14 at 15:17
  • @Shamil: no, that defines a default value for the column for the case (and only that case) when no value (or `default`) is used during insert. –  Feb 20 '14 at 15:21
  • Ok so if we're saying the first insert shouldn't work, was this a bug in 5.5? If this is the case I'm guessing we won't be able to replicate the 5.5 behaviour in 5.6? Thanks. – BombTodley Feb 20 '14 at 15:42
  • 1
    Please read this link: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp it may help. This is a description of `explicit_defaults_for_timestamp` system variable, it's was added in 5.6 – krokodilko Feb 20 '14 at 16:01
  • Thanks all, setting explicit_defaults_for_timestamp to OFF allows me to run the insert (still doesn't make complete sense to me) – BombTodley Feb 21 '14 at 08:18

2 Answers2

12

The behavior you showed above in 5.5 was actually a bug. This was fixed in 5.6.11. Check out the release notes for 5.6.11 at the very bottom. It was part of bugs 68472 and 16394472 (which I can't actually find a link to).

If you turn the explicit_defaults_for_timestamp flag off, it SHOULD work as it did in 5.5

This was the exact same issue that I had with our DB when we updated from 5.5 to 5.6.

Boerema
  • 338
  • 2
  • 13
  • The reason this works is a little complicated. Your query was working in 5.5 due to a bug that was allowing you to set a column to NULL when you really shouldn't have been able to. When 5.6 was rolled out, they made the `explicit_defaults_for_timestamp` flag to allow for deprecated functionality to work in the interim. However, in 5.6.11, they fixed the bug your (and my) query was relying on. Luckily for you and I the NEW default behavior with that flag turned off is exactly the behavior we wanted. – Boerema May 01 '14 at 13:06
0

Did you try omitting the column?

INSERT INTO `test` () VALUES ();
Rob Starling
  • 3,868
  • 3
  • 23
  • 40
  • Hi Rob, this would work for my example but in production we use Hibernate as our ORM so want to be able to set NULL against a column in a lot of cases, thanks anyway. – BombTodley Feb 21 '14 at 08:19
  • and have it result in a dynamic timestamp default instead of an actual null? hm. not sure if that's possible. did "setting explicit_defaults_for_timestamp to OFF" make that happen? – Rob Starling Feb 21 '14 at 08:25
  • It did yeah - as I said in my comment above it still doesn't make sense to me, but it works! – BombTodley Feb 21 '14 at 08:58
  • 1
    I said this in a comment on my answer as well, but the reason this works is a little complicated. Your query was working in 5.5 due to a bug that was allowing you to set a column to NULL when you really shouldn't have been able to. When 5.6 was rolled out, they made the `explicit_defaults_for_timestamp` flag to allow for deprecated functionality to work in the interim. However, in 5.6.11, they fixed the bug your (and my) query was relying on. Luckily for you and I the NEW default behavior with that flag turned off is exactly the behavior we wanted. – Boerema May 01 '14 at 13:05