Bug #105517 INSERT of CURRENT_TIMESTAMP() vs. DEFAULT differs for TIMESTAMP field

• Submitted: 10 Nov 2021 13:36
• Modified: 25 Apr 13:02
• Reporter: Dan Hentschel
• Status: Can't repeat
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 8.0
• OS: Any (20.0.4.1)
• CPU Architecture: Any

[10 Nov 2021 13:36] Dan Hentschel

Description:
When a TIMESTAMP field has a DEFAULT of CURRENT_TIMESTAMP, I would expect that an INSERT of CURRENT_TIMESTAMP() into this field would behave the same way as an insert of NULL. This is not the case if:

a) The server's time_zone is set to a timezone that experiences a clock shift for DST (EST /EDT in my case).
b) The insert is done during the hour before a backwards clock shift (in the fall, between 1:00 and 2:00 AM EDT).

I suspect this is related to this other bug that I submitted:

https://bugs.mysql.com/105515

How to repeat:
SELECT VERSION(), @@global.system_time_zone;
+-------------------------+---------------------------+
| VERSION()               | @@global.system_time_zone |
+-------------------------+---------------------------+
| 8.0.27-0ubuntu0.20.04.1 | EST                       |
+-------------------------+---------------------------+

$ sudo date -s "11/07/21 01:59:59-04:00" &&
mysql --login-path=local dst_test -e '
DROP TABLE IF EXISTS t;
CREATE TABLE t(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO t() VALUES();
INSERT INTO t(ts) VALUES(CURRENT_TIMESTAMP());
SELECT UNIX_TIMESTAMP(ts) FROM t;
'
Sun 07 Nov 2021 01:59:59 AM EDT
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1636264799 |
|         1636268399 |
+--------------------+

[10 Nov 2021 14:04] MySQL Verification Team

Thank you Mr. Hentschel,

We were able to repeat the behaviour on our own machines.

It is possible that these two bugs are related, but we can not yet be sure that they are been duplicates.

Verified as reported.

[25 Apr 12:53] MySQL Verification Team

Hello,

I've just tried this on 8.0.46 and it was not repeatable anymore.
Please check on your side.

$ sudo timedatectl set-timezone America/New_York
$ sudo service mysqld restart
$ sudo date -s "11/07/21 01:59:59-04:00" &&
mysql -uroot -p test -e "SELECT VERSION(), @@global.system_time_zone;;DROP TABLE IF EXISTS t;CREATE TABLE t(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);INSERT INTO t() VALUES();INSERT INTO t(ts) VALUES(CURRENT_TIMESTAMP());SELECT UNIX_TIMESTAMP(ts) FROM t;"
Sun Nov  7 01:59:59 EDT 2021
Enter password:
+-------------------+---------------------------+
| VERSION()         | @@global.system_time_zone |
+-------------------+---------------------------+
| 8.0.46-commercial | EST                       |
+-------------------+---------------------------+
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1636264811 |
|         1636264811 |
+--------------------+
$

[25 Apr 12:59] MySQL Verification Team

However, I couldn't repeat it on 8.0.17 either.

$ sudo date -s "11/07/21 01:59:59-04:00" &&
> mysql -uroot -p test -e "SELECT VERSION(), @@global.system_time_zone;;DROP TABLE IF EXISTS t;CREATE TABLE t(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);INSERT INTO t() VALUES();INSERT INTO t(ts) VALUES(CURRENT_TIMESTAMP());SELECT UNIX_TIMESTAMP(ts) FROM t;"
Sun Nov  7 01:59:59 EDT 2021
Enter password:
+-------------------+---------------------------+
| VERSION()         | @@global.system_time_zone |
+-------------------+---------------------------+
| 8.0.17-commercial | EST                       |
+-------------------+---------------------------+
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1636264810 |
|         1636264810 |
+--------------------+
$

[25 Apr 13:02] MySQL Verification Team

Neither on 8.0.27.

$ sudo date -s "11/07/21 01:59:59-04:00" &&
 mysql -uroot -p test -e "SELECT VERSION(), @@global.system_time_zone;;DROP TABLE IF EXISTS t;CREATE TABLE t(ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);INSERT INTO t() VALUES();INSERT INTO t(ts) VALUES(CURRENT_TIMESTAMP());SELECT UNIX_TIMESTAMP(ts) FROM t;"
Sun Nov  7 01:59:59 EDT 2021
Enter password:
+-------------------+---------------------------+
| VERSION()         | @@global.system_time_zone |
+-------------------+---------------------------+
| 8.0.27-commercial | EST                       |
+-------------------+---------------------------+
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1636264805 |
|         1636264805 |
+--------------------+
$