Incorrect datetime value on MySQL 8.0.32 - possible bug?

Question

There is a strange behaviour on MySQL 8.0.32-0ubuntu0.22.04.2 where a date seems not valid:

CREATE TABLE `user_logindates` (
  `user_id` mediumint(8) unsigned NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `user_id` (`user_id`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This works:

INSERT INTO `user_logindates` VALUES (48506,'2011-03-28 02:17:04');

But this one not:

INSERT INTO `user_logindates` VALUES (48506,'2011-03-27 02:17:04');

It returns:

[22001][1292] Data truncation: Incorrect datetime value: '2011-03-27 02:17:04' for column 'timestamp' at row 1

Checking the time zone:

$ timedatectl
Local time: Mon 2023-03-13 08:39:22 CET
Universal time: Mon 2023-03-13 07:39:22 UTC
RTC time: Mon 2023-03-13 07:39:23
Time zone: Europe/Berlin (CET, +0100)
System clock synchronized: yes
NTP service: active
RTC in local TZ: no

Check MySQL timezone settings:

SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone

SYSTEM;SYSTEM;CET

I tried various sql modes including '' but nothing worked. This seems to be a bug?

Screenshot of my install:

**Did you notice these WARNINGS** from the `CREATE` - 0 row(s) affected, 2 warning(s): 1681 Integer display width is deprecated and will be removed in a future release. 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. — RiggsFolly, Mar 12 '23 at 14:34
Where do you see those warnings? Are you saying that I should upgrade to UTF8MB4? I would rather avoid that for now. Setting it to character_set_server = UTF8MB3 did not fix the problem. The posted fiddle is 8.0.30 not 32. — merlin, Mar 12 '23 at 14:40
I am not suggestion anything. Oracle are suggesting you use a simple `\`user_id\` INT NOT NULL,` and that you use `CHARSET=UTF8MB4` now because if you continue using `CHARSET=utf8` it in fact means you are using `UTF8MB3` (which I believe has some fairly MAJOR issues) but it will in future releases mean `UTF8MB4` which may cause you inexplicable issues when that happens as you wont think anything has changed, but in fact it has — RiggsFolly, Mar 12 '23 at 14:45
I am trying to recover a backup from a large application to a new server, so I am limited in changing the structure for now. After you posted the fiddle I tried to reproduce on another remote server, also running 8.0.32 and the same problem appeared. — merlin, Mar 12 '23 at 14:49
Could be that the error is in happening in your `on update` trigger. — topsail, Mar 12 '23 at 14:49
Just installed MySQL 8.0.32 and still no problems with the create and 2 inserts you show. So something else is going on here that you have not told us about — RiggsFolly, Mar 12 '23 at 14:58
Did you test this by runing JUST the CREATE and the 2 INSERTS??? — RiggsFolly, Mar 12 '23 at 14:58
It is remotely possible (depending on timezones and other stuff I know little about) that you have actually run into a datetime value that does not exist because of a time change. Ah - and indeed Germany 2011 does have a DST change on Mar 27 and 2:00 in the morning is just when these things tend to happen... https://www.timeanddate.com/time/dst/2011.html — topsail, Mar 12 '23 at 15:08
Ok, thank you @topsail. So somebody closed the question and my problem persists. No idea on how to proceed from here. It appears to be a MySQL bug if I understand you right. — merlin, Mar 12 '23 at 15:18
@merlin Please [edit] your question to include the output of `SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone` to your question (see https://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql) — Progman, Mar 12 '23 at 15:28
The issue with times that are "timezone aware" is that in the case of DST changes, certain times are simply invalid. For instance, if you were watching the clock at 1:59:59 AM and waited one second ... well, one second later it will be 3:00:00. So there are no times that day for 02:00:00-02:59:59. So not a bug - only a "feature". You may need to clean your data of invalid times, convert them to a correct local time, fudge the bad dates, or update the time zones to match the data coming in - not fun stuff. Dates are hard when you starting looking closely. — topsail, Mar 12 '23 at 16:09
I am able to reproduce this on my local system (not on dbfiddle). The output for `SELECT @@VERSION, @@global.time_zone, @@session.time_zone, @@system_time_zone;` is `| 8.0.30 | SYSTEM | SYSTEM | W. Europe Standard Time |` — Luuk, Mar 12 '23 at 16:14
It's not a bug, MySQL is behaving correctly. See this 'bug' report about a timestamp of '2011-03-27 02:40:04' in CET time zone: https://bugs.mysql.com/bug.php?id=66240 — Bill Karwin, Mar 12 '23 at 16:40
You can also demonstrate this without using MySQL, using the POSIX `date` command: `TZ=CET date -j 032702172011` returns a date with time 03:27, because the time is advanced one hour by DST. — Bill Karwin, Mar 12 '23 at 16:42

score 0 · Answer 1 · answered Mar 13 '23 at 18:20

This is not a bug. The issue is that you picked a value for a TIMESTAMP column which does not exists in some timezones.

When the value is saved in a TIMESTAMP column, the value gets converted to UTC, as mentioned in the documentation 11.2.2 The DATE, DATETIME, and TIMESTAMP Types:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

So the entered value isn't stored directly into the column, unlike DATETIME. Your current timezone is set to CET, which uses daylight saving time. In this particular timezone, the time span from 2011-03-27 02:00:00 to 2011-03-27 03:00:00 does not exists as the clocks will be turned forward by one hour at 02:00:00. But you picked the value 2011-03-27 02:17:04 which is between these time span borders. This makes it an invalid timestamp value for the CET timezone to convert from and you get the error message:

Data truncation: Incorrect datetime value: '2011-03-27 02:17:04' for column 'timestamp' at row 1

There are several options depending on what you want to do. One option would be to check the timezone your MySQL client is using and adjust/use timestamp values which exists in the selected timezone. So your application shouldn't allow values to be entered which don't exists in the timezone you are using.

You can also change the timezone with the SET time_zone command to a timezone which fits your needs. This can look like this:

mysql> CREATE TABLE Foobar(data TIMESTAMP NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;
+--------------------+---------------------+--------------------+
| @@global.time_zone | @@session.time_zone | @@system_time_zone |
+--------------------+---------------------+--------------------+
| SYSTEM             | SYSTEM              | CET                |
+--------------------+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO Foobar(data) VALUES('2011-03-27 02:17:04');
ERROR 1292 (22007): Incorrect datetime value: '2011-03-27 02:17:04' for column 'data' at row 1
mysql> SET time_zone=UTC;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;
+--------------------+---------------------+--------------------+
| @@global.time_zone | @@session.time_zone | @@system_time_zone |
+--------------------+---------------------+--------------------+
| SYSTEM             | UTC                 | CET                |
+--------------------+---------------------+--------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO Foobar(data) VALUES('2011-03-27 02:17:04');
Query OK, 1 row affected (0.00 sec)

Backup applications/tools might have settings which set/defines the timezone to use when retrieving or inserting TIMESTAMP values.

Incorrect datetime value on MySQL 8.0.32 - possible bug?

1 Answers1