Understanding the ONLY_FULL_GROUP_BY Error
You’re facing a common issue when working with MySQL - the ONLY_FULL_GROUP_BY error that persists despite your attempts to disable it via the SQL mode setting. Let’s dive into this problem and find a solution that doesn’t require modifying third-party stored procedures.
The Problem with ONLY_FULL_GROUP_BY
MySQL 5.7 introduced the ONLY_FULL_GROUP_BY mode, which enforces stricter rules around GROUP BY clauses to prevent ambiguity and improve data integrity. However, for legacy applications or third-party modules like your Magento setup, this enforcement can cause existing queries to fail.
Your Current Approach
You’ve taken the right initial steps:
-
You tried setting the
ONLY_FULL_GROUP_BYmode globally using the command:SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); -
You confirmed that the mode has been removed by checking
@@sql_mode. -
You’ve also likely modified the my.cnf file to exclude
ONLY_FULL_GROUP_BYin thesql-modeparameter.
However, the error persists because stored procedures don’t inherit the current session’s SQL mode. They behave as if they were run with the default system variables at the time of creation.
Why Stored Procedures Ignore Session SQL Mode
When a stored procedure is created or altered, it’s compiled using the system variables that are in effect at that moment. Once compiled, it uses those same values every time it runs, regardless of any changes you make to the session’s SQL mode.
For example, if you create a stored procedure with ONLY_FULL_GROUP_BY enabled in your SQL mode, and later disable it for other queries, the stored procedure will still enforce this restriction each time it’s executed.
The Solution: Disabling ONLY_FULL_GROUP_BY Permanently
Since we can’t modify the third-party stored procedures directly without causing upgrade issues, we need to find a way to disable ONLY_FULL_GROUP_BY permanently for all sessions:
- Update the my.cnf file:
Open your MySQL configuration file (my.cnf) and locate or add the following line under [mysqld] section:
sql_mode=STRICT_ALL_TABLES,NO_AUTO_VALUE_ON_ZERO,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Note that ONLY_FULL_GROUP_BY is not included here.
- Restart the MySQL service:
After updating the config file, restart your MySQL server for the changes to take effect:
sudo /etc/init.d/mysql restart # For Linux systems with systemd
or
sudo service mysql restart # For older Linux distributions
The Impact on Your Database
Disabling ONLY_FULL_GROUP_BY at the global level will have consequences. It will allow queries that might otherwise raise errors due to ambiguous grouping or non-aggregated columns in the result set.
You should review your database schema and ensure that all tables are properly indexed and normalized to avoid data inconsistencies when this feature is disabled.
Conclusion
In conclusion, to resolve the ONLY_FULL_GROUP_BY error for third-party stored procedures without modifying them directly, you need to disable it at the MySQL server level by updating the my.cnf file and restarting the service.
This approach allows you to maintain a consistent SQL mode across all sessions while avoiding the need to alter existing stored procedures. However, always be cautious when disabling safety features like ONLY_FULL_GROUP_BY as it can lead to unexpected data issues if not properly managed.
For more detailed information on MySQL configuration and best practices, I recommend visiting Laravel Company.