Aggregated query without GROUP BY
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:
- 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
- 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.
Easiest answer, in config/database.php make sure to set strict => true to strict => false for the mysql settings.
This will allow for less than strict queries at a cost of security for normal well-formed sql calls (still not 100% secure), but will allow for the use of other sql calls that could be in-secure if written improperly.
Its a little late but I just ran into this error.
This command might be useful for anyone else who runs into the same error
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
More information about this can be found at Table Plus and other links quoted above by JNevill.
Hope it helps someone else.