Aggregated query without GROUP BY

Question

This query seems to work perfectly on my older machine. However, on my new machine with MySQL 5.7.14 and PHP 5.6.25 it throws the 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 can I do to resolve it?

Answer 1

A change was made in version 5.7.5 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

It's also important to note that excluding a non-aggregated column from the GROUP BY clause is permitted in 5.7.5 and newer version in the event that the unaggregated column has been limited to a single value (such as a filter in the WHERE clause). See the link above for examples of this allowed exception.