0

The table T has this field

+---------------+--------------+------+-----+-------------------+-----------------------------+
| Field         | Type         | Null | Key | Default           | Extra                       |
+---------------+--------------+------+-----+-------------------+-----------------------------+
| updated       | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+--------------+------+-----+-------------------+-----------------------------+

In MySQL 5.7, doing an update of a record and setting updated=null was automatically setting updated to the current date-time

UPDATE T SET ...,updated=null WHERE k=123

==> 

+---------------------+
| updated             |
+---------------------+
| 2022-01-05 22:52:05 |
+---------------------+

But doing the same in MySQL 8.0, gives

+---------------------+
| updated             |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+

How can I fix that, is this a MySQL bug, or a new setting (or a change in behavior)?

Improve this question
7
  • 2
    If you want updated to get the current time, you can use updated=NOW, or because it has a on update CURRENT_TIMESTAMP, you can just omit it from the UPDATE SQL, and it will get the current timestamp. Its a change in behaviour that was outside specification, is your bug for using it that way :-)
    – danblack
    Jan 6, 2022 at 4:37
  • The thing is I want to change as less code as possible (eg an option in MySQL). It seems that disabling explicit_defaults_for_timestamp would get the NULL behavior back, but that option is also deprecated... (and since it's not part of my settings, 5.7 should also not have worked like that...)
    – Déjà vu
    Jan 6, 2022 at 5:06
  • Why did you use updated=null on the UPDATE in the first place, when the column is defined with ON UPDATE CURRENT_TIMESTAMP ?
    – ypercubeᵀᴹ
    Jan 6, 2022 at 7:43
  • @ypercubeᵀᴹ There is a need to update the timestamp when no data changed in this table, while some data changed in "child" tables (eg order -> products).
    – Déjà vu
    Jan 6, 2022 at 10:23
  • This behavior depends on the SQL Server mode.
    – Akina
    Jan 6, 2022 at 10:55

1 Answer 1

Reset to default
2

Probably late, but you were on the right track with explicit_defaults_for_timestamp. The key fact is that this variable defaults to ON in version 8.0, but it defaulted to OFF in v.5.7.

Improve this answer
1
  • I had to figure it by myself eventually. Nice catch anyway, thanks!
    – Déjà vu
    Jul 18, 2022 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.