-2

I am migrating my project from php 5.6 to 7 but here mysql is also updated when i try to select a data and check if date is empty or not i have applied check in where clause that WHERE date is '' means empty not NULL. but when i execute this query on php 7 dates it says #1525 - Incorrect DATE value: ''. Anyone please help me. I have applied this on multiple sides of a complete project.

Śyeð ŚhðRiq Ålee
  • 1
  • 1
  • 1
  • Did you also upgrade the database? – Honk der Hase Jul 27 '21 at 12:29
  • yes mysql version is also updated. Previous version is 5.6.51 and new version is 8.0.21 – Śyeð ŚhðRiq Ålee Jul 27 '21 at 12:34
  • 1
    The default [sql_mode](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_mode) changes in the varying versions and distributions of MySQL, most likely caused by sql_mode containing [`NO_ZERO_IN_DATE NO_ZERO_DATE`](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_date), with [Strict Mode](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict) enabled or not containing [`ALLOW_INVALID_DATES`](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_allow_invalid_dates). – Will B. Jul 27 '21 at 12:35
  • Does this answer your question? [How to fix MySQL 8 error codes : 1525 and 1292?](https://stackoverflow.com/questions/58431136/how-to-fix-mysql-8-error-codes-1525-and-1292) – Will B. Jul 27 '21 at 12:36

1 Answers1

2

You can try to play with the sql-mode of mysql.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

There are options, like ALLOW_INVALID_DATES.

Check the changed default behaivior of mysql in newer versions. MySQL8 runs with enabled sql-mode-options: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION

The first one is quite interessting, because it requires all columns of "GROUP BY"-Statements in the SELECT-output. It costs me lots of time, to find out this problem. I still can not understand the added value of this change.

Sven
  • 524
  • 4
  • 10
  • 1
    For `ONLY_FULL_GROUP_BY`, it is because when the selected columns are not part of an aggregate function and not grouped, MySQL may retrieve a value for those columns from any of the rows in the recordset, causing unexpected results. See: [my explanation and multiple solutions](https://stackoverflow.com/a/26124759/1144627) – Will B. Jul 27 '21 at 12:48
  • ah, understood!! Thx! However, I would not enable those option by default - but I'm not the mysql-expert ;-) – Sven Jul 27 '21 at 12:53
  • The selecting of any value in the recordset when grouping is the default behavior of MySQL. Enabling `ONLY_FULL_GROUP_BY` forces the queries to adhere to proper SQL standards for grouping or use `ANY_VALUE(column)` to avoid the ambiguity of receiving the unexpected results. The warning about any value has been on the MySQL [GROUP BY page](https://web.archive.org/web/20150103231126/http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html) since MySQL 5.0 – Will B. Jul 27 '21 at 12:58
  • I have tried mutiple sql mode but still the issue couldn't resolve. First i'm getting the error on "0000-00-00" but that is resolve now i'm getting error on (where date=''). – Śyeð ŚhðRiq Ålee Jul 28 '21 at 06:00
  • are you using PHP's PDO to access to the database? If so, you could try to set this option: PDO::NULL_EMPTY_STRING – Sven Jul 28 '21 at 11:43
  • @Sven No I am using Code igniter PHP Framework with mysqli. – Śyeð ŚhðRiq Ålee Jul 29 '21 at 05:15