Wondering how to resolve Incompatible with sql_mode=only_full_group_by? Our in-house experts are here to help you out with this article. Our mysql support is here to offer a lending hand with your queries and issues.
Incompatible with sql_modeonly_full_group_by
While migrating a client website from one WHM/cPanel to another WHM/cPanel,
We got an SQL error “Incompatible with sql_mode=only_full_group_by“. Below is the complete error message.
SELECT list is not in GROUP BY clause and contains nonaggregated column 'dbname.p.picfile'
Today, let us see the steps followed by our support techs to resolve incompatible error
MySQL 5.7.5+ has changed
GROUP BY
Copy Code
behavior to be SQL99 compliant (Older versions were not) and that was the reason for the error.
The workaround is to edit your MySQL configurations to remove
ONLY_FULL_GROUP_BY
Copy Code
option from sql_mode
Copy Code
.
This will change
GROUP BY
Copy Code
behavior back to its pre-MySQL 5.7.5 behavior.
Remove ONLY_FULL_GROUP_BY
Copy Code
in my.cnf
Copy Code
file
ONLY_FULL_GROUP_BY
Copy Codemy.cnf
Copy CodeIf you have access to MySQL configuration file
my.cnf
Copy Code
, open the file:
# vim /etc/mysql/my.cnf
and add the below line to the end of the file:
[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"
Copy Code
Restart the MySQL service:
# systemctrl restart mysql
Remove ONLY_FULL_GROUP_BY
Copy Code
in WHM/cPanel
ONLY_FULL_GROUP_BY
Copy CodeIf your domain is on a cPanel (Shared Hosting), then you need to contact your hosting provider to update the same.
If you have access to WHM, then access
PHPMyAdmin
Copy Code
under SQL Services
Copy Code
from WHM as shown below:
Click on
Variables
Copy Code
from the menu and search for SQL_MODE
Copy Code
Click on edit button and change remove
ONLY_FULL_GROUP_BY
Copy Code
. Once edited, click on save
Copy Code
.
That’s it!
[Looking for a solution to another query? We’re happy to help.]
Conclusion
In this article, we provide a quick and simple solution from our Support team to how to resolve Incompatible with sql_mode=only_full_group_by
0 Comments