MySQL Forums
Forum List  »  Newbie

Re: Web App crashes when using MySQL v8.0
Posted by: Phillip Ward
Date: December 06, 2021 09:34AM

Quote

What options do I have to make the app work with MySQL v8.0?

Option #1:
Change the database option to allow your query to run.
But, in my humble opinion, that's just putting off the inevitable.

Option #2:
Fix your SQL so that it has only the fields that appear in the "group by" clause, plus aggregate functions on any other fields. That's how your query needs to be. MySQL is just moving closer to how all the other DBMSs would handle this, which is to throw an error (and with no option to "get around" the issue).

Quote

I prefer solutions that do not require me to rewrite the MySQL code to make it compatible with the new restrictions imposed by the new version.

Not wishing to sound heartless but, if that really is your preference, then don't upgrade.
There will always be something in every upgrade that will trip us up and require us to revisit our codebase to some degree. That's the reality of the "Technology Hamster-wheel" - once you get on, it's very hard to get off again without scraped knees and a bruised ego.

So what's MySQL complaining about?

Consider this table:

+----+------+-------+
| id | name | value | 
+----+------+-------+
|  1 |   22 |    33 |
|  1 |   44 |    66 | 
+----+------+-------+

and this query:

select id, name, max( value ) m_value 
from table1 
group by id ; 

+----+------+---------+
| id | name | m_value | 
+----+------+---------+
|  1 |   ?? |      66 | 
+----+------+---------+

What value of name should be returned?

The query is grouping my id, so it will return a row for each, distinct value of id. value part of an aggregate function (max), so that's OK. But what about name? Which if those two values should it return?
Without being told how to work it out, most DBMSs - and that includes MySQL, by default - will throw an error.

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Web App crashes when using MySQL v8.0
December 06, 2021 09:34AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.