5

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

Community
  • 1
  • 1
Manu Mathew
  • 140
  • 1
  • 8
  • I also have this issue. Did you solve this? – SAI GIRI CHARY AOUSULA Mar 31 '18 at 12:29
  • @SAIGIRICHARYAOUSULA Please look at the accepted answer. I had multiple my.cnf files in the system. I had to delete a few to get the right configuration. I no longer use the system, so I can't say which particular files I deleted. – Manu Mathew Nov 28 '18 at 00:51

1 Answers1

2

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.

pavi
  • 654
  • 1
  • 9
  • 29