mysql 5.7.12 - Disabling only_full_group_by sql_mode is not fully successful

Question

I'm using mysql version 5.7.12 on Ubuntu 16.04. I noticed the following exception while running my web application:

SQL Exception : Expression #x of SELECT list is not in GROUP BY clause and contains nonaggregated column 'something.something' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It used to work perfectly on mysql 5.5. I looked for solutions online. I can understand why they are enforcing only_full_group_by mysql_mode by default in version 5.7. However, I cannot afford at this stage to go back to the code and keep correcting the queries. I chose to disable this sql_mode by editing /etc/mysql/my.cnf file as suggested here. By appending the following lines to the end of my.cnf and restarted mysql.

[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

To verify that it works I tried the following commands in mysql console:

SELECT @@sql_mode;

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

All of these returned the same result as shown below:

+---------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +---------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

After starting my webapp, I found that it indeed worked for some queries but for others it still threw the same exception. I don't know what to do. Please help me resolve this issue.

Thanks

Answer 1

Please check for the multiple occurrences of my.cnf file in your file system, that could be overriding the sql_mode specified in the above.