How to Bring Back GROUP BY in MySQL

0 min read 131 words

If you are suddenly not able to perform GROUP BY statements as you used to after a MySQL/MariaDB version update, then you will have noticed the change forced upon you.

You can re-enable the previous GROUP BY behaviour by editing your /etc/mysql/my.cnf and adding in the following line below the [mysqld] section:

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

This will allow you to GROUP BY without having to return all those specific columns as well.

Why this happened

It turns out that the original functionality was a trick that we all used to use, and it was removed recently in order to address “incompatibilities”. You can read more about it here:

https://mariadb.com/kb/en/mariadb/group-by-trick-has-been-optimized-away/

This is more of a problem of MySQL having implemented the ANSI standard and then deciding to remove it much later

Tags:
Andrew
Andrew

Andrew is a visionary software engineer and DevOps expert with a proven track record of delivering cutting-edge solutions that drive innovation at Ataiva.com. As a leader on numerous high-profile projects, Andrew brings his exceptional technical expertise and collaborative leadership skills to the table, fostering a culture of agility and excellence within the team. With a passion for architecting scalable systems, automating workflows, and empowering teams, Andrew is a sought-after authority in the field of software development and DevOps.

Tags