How to fix MySQL 8 error codes : 1525 and 1292?

user_8259219 0
Viewed 9819

For invalid dates on Mysql 8 server, I'm getting 2 types of errors :-

  1. Error Code: 1525. Incorrect DATE value: '2019-09-31'.

  2. Warning Code : 1292. Incorrect datetime value: '2019-09-31 23:59:59' for column 'date_column' at row 1 .

Can anyone please suggest how to resolve / fix / ignore these errors for invalid dates on mysql 8?

We're migrating our database servers from MySQL 5 to MySQL 8. I'm running the following 2 queries on both the servers :-

Query 1 : SELECT * FROM db1.table1 WHERE date_column >= '2019-09-01 00:00:00' AND date_column <= '2019-09-31 23:59:59' ;

Query 2 : SELECT * FROM db1.table1 WHERE date_column BETWEEN '2019-09-01 00:00:00' AND '2019-09-31 23:59:59' ;

On mysql 5 server, I'm getting no error / warning and both the queries return same results.

On mysql 8 server, Query 1 breaks with error code 1525 while Query 2 runs successfully with no result (though there are results) with warning code 1292 and message "Incorrect datetime value: ''2019-09-31 23:59:59'' for column 'date_column' at row 1" .

mysql-8.0 datetime mysql sql database
3 Answers

The lesser evil is possibly to change the session-wide SQL mode for both the migration script and the affected application—that should be a simple one time change (as long as the connection code is not copy+pasted in a hundred places). The mode that allows invalid dates is ALLOW_INVALID_DATES:

SET @@SESSION.sql_mode = CONCAT_WS(',', @@SESSION.sql_mode, 'ALLOW_INVALID_DATES');

Full demo:

mysql> CREATE TABLE foo (bar DATE);
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------------------------------+
| @@SESSION.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO foo (bar) VALUES ('2019-02-30');
ERROR 1292 (22007): Incorrect date value: '2019-02-30' for column 'bar' at row 1
mysql> SET @@SESSION.sql_mode = CONCAT_WS(',', @@SESSION.sql_mode, 'ALLOW_INVALID_DATES');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO foo (bar) VALUES ('2019-02-30');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM foo;
+------------+
| bar        |
+------------+
| 2019-02-30 |
+------------+
1 row in set (0.00 sec)
user_135080

September have only 30 days. SO you queries must be as follows -

Query 1

SELECT *
FROM db1.table1
WHERE date_column >= '2019-09-01 00:00:00' AND date_column <= '2019-09-30 23:59:59';

Query 2

SELECT *
FROM db1.table1
WHERE date_column BETWEEN '2019-09-01 00:00:00' AND '2019-09-30 23:59:59' ;
user_36277560

I couldn't able to update the records using the below update statement in MySQL 8.0 got 1525 error, but I can run in 5.1

UPDATE agency 
    VALIDATION_DESCRIPTION = CONCAT(IFNULL(VALIDATION_DESCRIPTION,''),'Accounting Date Key is Missing',"::  ::")
    WHERE (date(ACCOUNTING_DATE_KEY) IS NULL OR date(ACCOUNTING_DATE_KEY) ='') and agency_id=1;
user_113034720
Related