Aggregated query without GROUP BY

Question

This query seems to work perfect on my older machine. However, on my new machine with MySQL 5.7.14 and PHP 5.6.25 it seems to throw an error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pixel_perfect.users.id'; this is incompatible with sql_mode=only_full_group_by' in C:\wamp64\www

Here is what my query looks like:

$sql="SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email LIMIT 1";  $stmt=$db->prepare($sql); $stmt->bindValue(':email', $email); $stmt->execute(); 

Why am I getting this error now and what do I do to resolve it painlessly.

Answer 1

A change was made in version 5.7-ish where it will now, by default, reject queries in which you aggregate using a function (sum, avg, max, etc.) in the SELECT clause and fail to put the non-aggregated fields in the GROUP BY clause. This behavior is part and parcel to every other RDBMS and MySQL is finally jumping on board.

You have two options:

1. You can change the MySQL settings to default to the old behavior to allow not-so-great queries like this. Information can be found here
2. You can fix your query

Option 2 would look something like:

SELECT id, password, COUNT(id) AS count FROM users WHERE email = :email GROUP BY id, password LIMIT 1