Jump to content

MySQL 8.0.17 datetime error


dreerr
 Share

Recommended Posts

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? 

  • Like 1
Link to comment
Share on other sites

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.

  • Like 7
Link to comment
Share on other sites

  • 8 months later...

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

 

 

 

 

 

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...