GROUP BY does not work in Mysql 5.7

Question

Locally I have Mysql 5.6 installed, and this SELECT works smoothly:

SELECT dia FROM indicacao
GROUP BY YEAR(dia), MONTH(dia)
ORDER BY dia DESC

Already on the client server, with Mysql 5.7 this same SELECT does not work. I realize that the problem is in GROUP BY, because if I withdraw, it works. If I do a SELECT without PDO it simply returns nothing... if I do this same SELECT with PDO error 500 on the server.

The database content is the same (locally and on the client server). Has anyone experienced this? Do you know if it might be some kind of server configuration? Is there anything you can use to test or see an error log?

Answer 1

This is because Mysql 5.7 is now standardized as ONLY_FULL_GROUP_BY enabled.

To disable it:

1) Go to the bin folder where you have your Mysql installation (in the case of my computer is: C: WAMP Mysql 5.6.42 bin):

2) Run the mysql.exe name executable and display the Mysql command line window:

3) Execute one of the commands below to disable:

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

or

SET sql_mode = '';

If you want to inhabit it again at another time just execute this command:

SET sql_mode = 'ONLY_FULL_GROUP_BY';

After will display a 'Query ok' message when successfully executed.

To learn more about ONLY_FULL_GROUP_BY visit this link.