1

I upgraded from Godaddy to blue host and now I'm getting this error:

Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'customeTable.fromuser' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' in

$statement = $db->prepare("SELECT * FROM(SELECT DISTINCT(thread_id) AS 
thread_id, fromuser, touser, aDeleted, bDeleted, avatar, message, seen, 
time FROM conversations WHERE fromuser=:username AND bDeleted=0 OR 
touser=:username AND aDeleted =0 ORDER BY time DESC) customeTable GROUP 
BY thread_id ORDER BY time DESC");

$statement->bindParam("username", $username, PDO::PARAM_STR) ;
$statement->execute();
user3783243
  • 5,368
  • 5
  • 22
  • 41
Chris
  • 17
  • 6
  • Change the strictness, or probably more logical put the columns you are aggregating in the select list. You changed mysql versions, you now are running 5.7. – user3783243 Jul 15 '19 at 21:41
  • Ended up disabling only_full_group_by, all fixed now :) – Chris Jul 15 '19 at 23:51
  • Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) – user3783243 Jul 16 '19 at 02:14

1 Answers1

1

To fix the query take a look at following things:

  • The query has a GROUP BY-clause without any aggregate function (you should probably remove it). See documentation.
  • The DISTINCTmodifier is not a function. It removes duplicate rows over all columns
  • As the query is mixing AND and OR it should use parentheses, otherwise you might get unexpected results
  • The subquery is not needed, you can remove it
slaakso
  • 8,331
  • 2
  • 16
  • 27