MySQL 8.0.17 datetime error

[dreerr]

Dear Community,

Since I upgraded my local installation of MySQL to 8.0.17 (stable) I get all kinds of errors in the frontend of a installation.

My selector is

$pages->find("template=show,date_show_to=,sort=-date_show_from")

 and I get the error

SQLSTATE[HY000]: General error: 1525 Incorrect DATETIME value: ''

so it is somehow not possible anymore to search for empty datetime fields. My my.cnf mode

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION

But I already tried with 

sql_mode=STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION

 

Any ideas? 

[bernhard]

Maybe you find some help in the links in this post?

 

[BitPoet]

3 hours ago, dreerr said:

Since I upgraded my local installation of MySQL to 8.0.17 (stable) I get all kinds of errors in the frontend of a installation.

It's a breaking change in MySQL 8.0.16 that Oracle developers are refusing to list as such, as documented here.

I guess Ryan will have to adapt the fieldtype's getMatchQuery code to keep things compatible. I have taken the liberty and opened an issue since this is going to bite me too soon.

In the mean time, you could try replacing the line in question in FieldtypeDatetime::getMatchQuery, changing it from

			else $value = '';

to

			else $value = '0000-00-00 00:00:00';

and see if that works.

[kixe]

This problem is still not solved. Strange results:

// check if field is empty (NULL)
$p = $pages->get("datetime="); // SQL Error
$p = $pages->get("datetime=''"); // SQL Error
$p = $pages->get("datetime=0000-00-00 00:00:00"); // SQL Error

$p = $pages->get("datetime=0"); // expected result, will find a page with datetime = 1970-01-01 00:00:00
$p = $pages->get("datetime=null"); // will find a page with datetime = 1970-01-01 00:00:00 why?
var_dump($p->getUnformatted('datetime')); // if a page exists result -3600 due to the locale

I need to check if the field is not set via selector. Any ideas? Currently I exlude this from the selector and loop the results (bad solution)

https://github.com/processwire/processwire-issues/issues/973#issuecomment-625678587