0

I am using a third-party application that works fine in my local development environment (uses MariaDB 10.4 with a reported drop-in compatibility for MySQL 5.7 [1]), but crashes in my deployment environment (uses MySQL server v8.0.27).

I could pinpoint the problem to the following MySQL query:

SELECT DISTINCT user_id,title,description,posted,expire,thumbnail,(SELECT MAX(us.id) FROM Wo_UserStory us WHERE us.user_id = Wo_UserStory.user_id) AS id FROM Wo_UserStory WHERE (user_id IN (SELECT following_id FROM Wo_Followers WHERE follower_id = '1') OR user_id = 1) AND user_id IN (SELECT user_id FROM Wo_Users WHERE active = '1') GROUP BY user_id ORDER BY id DESC LIMIT 4

which causes a 500 internal system error. When I execute this query in phpmyadmin, I get the error:

#1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wowonder.Wo_UserStory.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Reading further about this, I decided to remove ONLY_FULL_GROUP_BY from the flags inside the variable "sql mode". This did not solve my problem, and caused an application crash earlier in the execution flow.

So, how do I make this work in MySQL v8? Downgrading to MySQL v5.7 in my deployment server is not an option.

[1] https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/

  • check https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by – ProDec Dec 05 '21 at 13:06
  • MySQL 8.0 is correct. In previous versions, invalid GROUP BY usage wasn't rejected and simply returned random results instead –  Dec 05 '21 at 13:24
  • Thanks. None of these are answers to my question i.e. they do not show how to adapt MySQL 8 to make the application work. –  Dec 05 '21 at 13:53
  • Can you describe what you intended the query to do, or show a mock-up of the output you want? If you group by user_id, which values for the other columns should be output? For example, are you trying to get the most recent story for a specific user? – Bill Karwin Dec 05 '21 at 14:05
  • In general, you should not think of MariaDB as a drop-in replacement for MySQL. They have some code in common, but they have both changed since MariaDB forked in 2010. They are no longer compatible in many cases. You should always use the same brand and version in development that you will use in deployment, to avoid surprises about incompatibility. – Bill Karwin Dec 05 '21 at 14:07

1 Answers1

0

You are using group by improperly .. you have not aggregation function and the use of group by for reduce the number of rows returned produce impredictable result.

The mysql verion > 5.7 don't allow this behavior so you should rewrite your query in proper way .. You could also refactory your query for avoid IN clause on subquery and other

Your query should be equivalent to this

select u.user_id
    ,u.title,u.description,u.posted,u.expire,u.thumbnail, mxt.max_id
FROM Wo_UserStory u
INNER JOIN  (
    select 1 user_id
    union 
    select following_id
    FROM Wo_Followers 
    WHERE follower_id = '1'
    union 
    SELECT user_id 
    FROM Wo_Users 
    WHERE active = '1'
) t on y.user_id ) u.user_id
INNER JOIN ( 
    select user_id, max(id) max_id
    FROM Wo_UserStory 
    group by user_id
) mxt on mxt.user_id u.user_id 
    AND mxt.max_id = u.id

In this way you can use a query with group by properly and avoid the improer use of group based on the disabling of ONLY_FULL_GROUP_BY mode. Keep in mind that the group by restriction enforce in most recen mysl version are the right way for use the group by

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Or, just to show that there are also **invalid** answers on SO, see: [Disable ONLY_FULL_GROUP_BY](https://stackoverflow.com/questions/23921117/disable-only-full-group-by) (which is NOT the solution to solve this problem!) – Luuk Dec 05 '21 at 14:52
  • 1
    Based on the comment by @Luuk ..i have updated the answer adding some raccomendation for a proper use of group by and avoid improper use based un disabling the correct group by enforcement.. – ScaisEdge Dec 05 '21 at 15:02
  • Thanks. Your solution requires that I change the third-party code which seems to include a lot of GROUP BY statements with potentially the same pitfalls. Like I said in my question, I am not the author of the code and my development expertise lies elsewhere. I am hoping for a solution that makes the code "unchanged" work in the new MySQL. Like I said, this is a perfectly working program under MySQL 5.7 even with the infamous "GROUP BY" statements, so I am hoping for a solution that does not require me to perform code changes because I do not have the resources (skills, time, etc.) for that. –  Dec 06 '21 at 08:42