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.