Mysql error 1525 - Исправление ошибок и поиск оптимальных решений проблем

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

Error Code: 1525. Incorrect DATE value: ‘2019-09-31’.
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″ .

asked Oct 17, 2019 at 11:04

Mansi GuptaMansi Gupta

531 gold badge1 silver badge5 bronze badges

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)

answered Oct 17, 2019 at 16:09

Álvaro GonzálezÁlvaro González

139k39 gold badges258 silver badges352 bronze badges

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' ;

answered Oct 17, 2019 at 11:14

Ankit BajpaiAnkit Bajpai

12.9k4 gold badges26 silver badges40 bronze badges

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;

answered Feb 22, 2021 at 8:22

Источник

Всем привет!

Столкнулся тут с ошибкой базы данных после переезда на centos8 и mysql8 (по умолчанию там которая).

Сайт перенес, все вроде заработало — заказы оформляются, страницы открываются. Но есть досадная проблема: при посещении раздела сайта «отчеты» я получаю следующую ошибку:

Query Error 1525: Incorrect DATETIME value: ‘0000-00-00 00:00:00’

Query: SELECT MIN(create_datetime)
FROM shop_order
WHERE create_datetime > ‘0000-00-00 00:00:00’ code 1525

## wa-system/database/waModel.class.php(266) #0 wa-system/database/waModel.class.php(366): waModel->run('SELECT MIN(crea...') #1 wa-apps/shop/lib/model/shopOrder.model.php(782): waModel->query('SELECT MIN(crea...') #2 wa-apps/shop/lib/model/shopSales.model.php(56): shopOrderModel->getMinDate() #3 wa-apps/shop/lib/actions/reports/shopReportsSales.action.php(41): shopSalesModel->getMinDate() #4 wa-system/controller/waViewAction.class.php(161): shopReportsSalesAction->execute() #5 wa-system/controller/waViewController.class.php(86): waViewAction->display() #6 wa-system/controller/waDefaultViewController.class.php(48): waViewController->executeAction(Object(shopReportsSalesAction)) #7 wa-system/controller/waController.class.php(21): waDefaultViewController->execute() #8 wa-system/controller/waViewController.class.php(46): waController->run(NULL) #9 wa-system/controller/waFrontController.class.php(263): waViewController->run(NULL) #10 wa-system/controller/waFrontController.class.php(190): waFrontController->runController(Object(waDefaultViewController), NULL) #11 wa-system/controller/waFrontController.class.php(84): waFrontController->execute(NULL, 'reports', 'sales') #12 wa-system/controller/waDispatch.class.php(157): waFrontController->dispatch() #13 wa-system/controller/waDispatch.class.php(32): waDispatch->dispatchBackend('adminka...') #14 wa-system/waSystem.class.php(599): waDispatch->dispatch() #15 index.php(7): waSystem->dispatch() #16 {main}

GET

[  'module' => 'reports',  'action' => 'sales',  'timeframe' => '30',  'groupby' => 'days', ]

POST

[  '_csrf' => '[hidden]', ]

Что я делал:

1. проверил, через phpmyadmin, таблицу create_datetime на пустые и NULL значения. Везде стоят даты.

2. Гуглил по 1525 ошибки — в основном советуют изменить режим работы Mysql. Менял, убирал и STRICT_TRANS_TABLES и NO_ZERO_IN_DATE,NO_ZERO_DATE и ERROR_FOR_DIVISION_BY_ZERO, убирал вообще все значения — не помогло.

3. Идеи закончились (((

Прошу помочь. Кто сталкивался?

П.С. База переносится с mysql 5.5.65

Источник

In MySQL 8.0, when using invalid values for date data types, an error is returned. This was not the case in 5.x versions.

Let’s have a look using the table definition of bug 96361:

CREATE TABLE `new_table` (
  `id_table` int(11) NOT NULL AUTO_INCREMENT,
  `text_table` varchar(45) DEFAULT NULL,
  `date_table` date DEFAULT NULL,
  PRIMARY KEY (`id_table`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now we can try the following statement in MySQL 5.7 and MySQL 8.0:

MySQL 5.7.26> SELECT id_table, text_table 
              FROM new_table WHERE date_table = '' OR date_table IS NULL;
Empty set, 1 warning (0.01 sec)

MySQL 5.7.26> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '' for column 'date_table' at row 1 |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0.17> SELECT id_table, text_table 
              FROM new_table WHERE date_table = '' OR date_table IS NULL;
ERROR 1525 (HY000): Incorrect DATE value: ''

We can see that in MySQL 5.7, a warning is returned but no error.

In earlier version of 5.x it was by default also possible to store DATEs as 0000-00-00. This is not possible anymore neither in 5.7, neither in 8.0 (by default):

mysql> insert into new_table (text_table, date_table) values ('lefred','0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'date_table' at row 1

To be able to use 0000-00-00 as date the SQL_MODE need to be changed. By default it contains NO_ZERO_IN_DATE,NO_ZERO_DATE.

mysql8> set @@SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql8> select @@SQL_MODEG
*************************** 1. row ***************************
@@SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

mysql8> insert into new_table (text_table, date_table) values ('lefred','0000-00-00');
Query OK, 1 row affected (0.04 sec)

But even when this is changes, invalid dates (other than invalid 0‘s)are of course still considered as errors in MySQL 8.0:

mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '';
ERROR 1525 (HY000): Incorrect DATE value: ''
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-00-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
|        2 | lefred     |
+----------+------------+
1 row in set (0.00 sec)

mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-00-01';
Empty set (0.00 sec)

mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '2000-01-32';
ERROR 1525 (HY000): Incorrect DATE value: '2000-01-32'
mysql> SELECT id_table, text_table FROM new_table WHERE date_table = '0000-00-00';
+----------+------------+
| id_table | text_table |
+----------+------------+
|        1 | lefred     |
|        3 | lefred     |
+----------+------------+
2 rows in set (0.00 sec)

This is because when comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. Before 8.0 (8.0.16) when the conversion failed, MySQL executed the comparison treating the DATE as a string. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE.

Subscribe to Blog via Email

Источник