mysqlmysql-error-1055

Select list contains nonaggregated column

Since updating MySQL I've noticed the following query fails

SELECT u.*, p.name as plan, COUNT(u.id) as totalprojects FROM users u LEFT JOIN plans p ON p.id = access LEFT JOIN maps m ON m.user_id = u.id WHERE u.email = 'john@doe.com'

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'kontakt.u.id'; this is incompatible with sql_mode=only_full_group_by

Does anyone know how to get this query fixed to sort the error?

Solution

  • On my Node server with Digital Ocean the conference file I needed to edit was located /etc/mysql/mysql.conf.d/mysqld.cnf

    Adding in the following 

    [mysqld] sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    And then service mysql restart fixes the issue.