GROUP By does not work in MySQL 5.7 [duplicate]
Locally I have MySQL 5.6 installed, and this SELECT works without problems:
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 retire, it works. If I do a SELECT without PDO it just doesn't return anything... if I do that same SELECT with error PDO 500 on the server.
The content of the database is equal (locally and on the server of the client). Has anyone been through this? Do you know if it could be some server configuration? Is there anything I can use to test or see some error log?
1 answers
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 the installation of your MySQL (in the case of my computer it is: C:\WAMP\MySQL\5.6.42\bin):
2) run the MySQL name executable.exe and will display MySQL command line Window:
3) run 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 want to inhabit it again at another time just run this command:
SET sql_mode = 'ONLY_FULL_GROUP_BY';
After will display a 'query ok' message when executed successfully.
To learn more about ONLY_FULL_GROUP_BY go to this Link.