[Ubuntu 20.04/MySQL 8.0] Upgrade to latest version broke existing queries

Berlin77 — Tue, 17 Nov 2020 06:00:39 GMT

OS: Ubuntu 20.04.1 LTS

MySQL: Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Hey everybody,

I f*cked up this morning by upgrading my laptops OS version from Ubuntu 18.04 to 20.04. Afterwards my mysql connections weren't running properly anymore so I completely reinstalled mysql. I've finally gotten to a point again where I can connect to my server and select data but I have one problem.

Previously (unsure which mysql version I used before) you were allowed to create statements comparing date fields with values in single quotes, example:

SELECT * FROM myUsers WHERE loginDate = '0';

'0' isn't a valid datetime but this shouldn't be the problem, mysql used to automatically and correctly convert the statement to a proper date comparison against a zero date but this is not working anymore. A simple query like the one above fails with the error:

Error Code: 1525. Incorrect DATETIME value: '0'

I've already checked the sql_mode (as I'm aware of the "NO_ZERO_DATE" etc. options from previous versions) and have currently set it to "ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION" in my.cnf and restarted the service to no success.

What's surprising is that the following statement will work:

SELECT * FROM myUsers WHERE loginDate = 0;

So without the single quotes.

Here's the problem: I'm developing several applications, most of them created by a former employee and there are a lot of date checks in the files that use single quotes for date comparisons and fixing every statement would take days.

Is there an option I'm missing to allow '0' checks again against date fields like it used to work in the previous version?

Re: [Ubuntu 20.04/MySQL 8.0] Upgrade to latest version broke existing queries

Pavlavcalm — Fri, 21 May 2021 17:20:26 GMT

https://maheenandco.com/product/hoodies/

topic Re: [Ubuntu 20.04/MySQL 8.0] Upgrade to latest version broke existing queries in Integration and Testing

[Ubuntu 20.04/MySQL 8.0] Upgrade to latest version broke existing queries

Re: [Ubuntu 20.04/MySQL 8.0] Upgrade to latest version broke existing queries