MySQL Bugs: #99291: ERROR 1292 (22007) only when a specific value is used in a TIMESTAMP column

Bug #99291	ERROR 1292 (22007) only when a specific value is used in a TIMESTAMP column
Submitted:	17 Apr 2020 16:20	Modified:	17 Apr 2020 16:48
Reporter:	Krassimir Tzvetanov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.7.29-0ubuntu0.18.04.1	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
I am executing the following query with an error:
mysql> INSERT INTO TorQueries (Version, Relays_published, Bridges_published, AcquisitionTimestamp) VALUES( "8.0", "2020-03-29 00:00:00", "2020-03-28 23:57:55", "20200329010400");
ERROR 1292 (22007): Incorrect datetime value: '20200329010400' for column 'AcquisitionTimestamp' at row 1
mysql> SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;

The problem appears to be the specific value for AcquisitionTimestamp, which is 20200329010400.

==================
This also failed for 20200329010500 and 20200329010300 but did not fail for 20200329000400. See below:
mysql> INSERT INTO TorQueries (Version, Relays_published, Bridges_published, AcquisitionTimestamp) VALUES( "8.0", "2020-03-29 00:00:00", "2020-03-28 23:57:55", "20200329010500");
ERROR 1292 (22007): Incorrect datetime value: '20200329010500' for column 'AcquisitionTimestamp' at row 1

mysql> INSERT INTO TorQueries (Version, Relays_published, Bridges_published, AcquisitionTimestamp) VALUES( "8.0", "2020-03-29 00:00:00", "2020-03-28 23:57:55", "20200329010300");
ERROR 1292 (22007): Incorrect datetime value: '20200329010300' for column 'AcquisitionTimestamp' at row 1

mysql> INSERT INTO TorQueries (Version, Relays_published, Bridges_published, AcquisitionTimestamp) VALUES( "8.0", "2020-03-29 00:00:00", "2020-03-28 23:57:55", "20200329000400");
Query OK, 1 row affected (0.00 sec)
========================
This is a valid date, it should be accepted.

I'll submit the table schema in the "How to repeat section".
I have the following session flags: "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

========================
TROUBLESHOOTING
========================
First I removed all session flags. It worked. Then I started isolating which flag breaks it.
I kept on getting the same error if I removed one of the following flags: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION.

The problem was resolved when I removed STRICT_TRANS_TABLES and this is not making any sense. This is a valid date.

I also reformatted it to: "2020-03-29 01:04:00" and it fails again.

I also noticed that when I remove the flag and it imports it, it is off: 2020-03-29 02:00:00.

How to repeat:
mysql> SHOW COLUMNS FROM TorQueries;
+----------------------+------------------+------+-----+-------------------+-----------------------------+
| Field                | Type             | Null | Key | Default           | Extra                       |
+----------------------+------------------+------+-----+-------------------+-----------------------------+
| ID                   | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| Version              | char(6)          | NO   |     | NULL              |                             |
| queryTime            | timestamp        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Relays_published     | datetime         | NO   |     | NULL              |                             |
| Bridges_published    | datetime         | NO   |     | NULL              |                             |
| AcquisitionTimestamp | timestamp        | NO   |     | CURRENT_TIMESTAMP |                             |
+----------------------+------------------+------+-----+-------------------+-----------------------------+
CREATE TABLE TorQueries (
	ID INT UNSIGNED AUTO_INCREMENT NOT NULL, 
	Version CHAR(6) NOT NULL,
	queryTime TIMESTAMP NOT NULL,
	Relays_published DATETIME NOT NULL, 
	Bridges_published DATETIME NOT NULL,
	AcquisitionTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (ID)
);

Use the queries from above.

Suggested fix:
This is a valid date, it should be accepted by the engine, regardless of transaction state readiness.

Thank you for the bug report. Check your Daylight Saving Time (DST) which makes really invalid values:

Your MySQL connection id is 3
Server version: 5.7.31-log Source distribution BUILT: 2020-APR-07

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > USE test
Database changed
mysql 5.7 > CREATE TABLE TorQueries (
    -> ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
    -> Version CHAR(6) NOT NULL,
    -> queryTime TIMESTAMP NOT NULL,
    -> Relays_published DATETIME NOT NULL,
    -> Bridges_published DATETIME NOT NULL,
    -> AcquisitionTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -> PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > INSERT INTO TorQueries (Version, Relays_published, Bridges_published, AcquisitionTimestamp) VALUES( "8.0", "2020-03-29 00:00:00", "2020-03-28 23:57:55", "20200329010300");
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > INSERT INTO TorQueries (Version, Relays_published, Bridges_published, AcquisitionTimestamp) VALUES( "8.0", "2020-03-29 00:00:00", "2020-03-28 23:57:55", "20200329010500");
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > SELECT * FROM TorQueries\G
*************************** 1. row ***************************
                  ID: 1
             Version: 8.0
           queryTime: 2020-04-17 13:42:03
    Relays_published: 2020-03-29 00:00:00
   Bridges_published: 2020-03-28 23:57:55
AcquisitionTimestamp: 2020-03-29 01:03:00
*************************** 2. row ***************************
                  ID: 2
             Version: 8.0
           queryTime: 2020-04-17 13:43:03
    Relays_published: 2020-03-29 00:00:00
   Bridges_published: 2020-03-28 23:57:55
AcquisitionTimestamp: 2020-03-29 01:05:00
2 rows in set (0.00 sec)

mysql 5.7 >

If you are looking for a gaming chair you can find it online and enjoy it while playing. There is a number of models are available in the market. I must suggest visiting https://www.essaygeeks.co.uk website to avail the helpful material. Thumbs up with your sharing these are wise.