Before installing an application, we often recommended that customers always check the application's documentation to see what might be needed for a successful installation/operations.
Failure to do this will sometimes result in your application deviating from the expected behavior.
For example, an application may only be capable of working properly with older versions of MySQL or PHP.
Another example would be when a hosting account has been recently migrated, and an application that was working perfectly well with MySQL 5.6 is not having some weird issues.
What thing could explain these scenarios would that the changes that current versions of MySQL have made since MySQL5.7.
The default SQL mode for these later versions is more restrictive than MySQL 5.6.
Let's take a look at ONLY_FULL_GROUP_BY SQL mode which is enabled by default in MySQL 8.0/5.7.
You can see some of these changes at:
When the mode when enabled, all the select fields should be included in "GROUP BY".
When disabled, you can use one or more or all of the select fields.
So you may run into a situation where you are getting errors such as this:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.web_log.user_id' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
OR:
$applicatioName may not work properly because ONLY_FULL_GROUP_BY is enabled, consult with your hosting provider to disable ONLY_FULL_GROUP_BY in sql_mode configuration
Our production machines run MySQL 8.
To eliminate this kind of error, the mode needs to be disabled.
However, ONLY_FULL_GROUP_BY is a server-wide DB configuration and can only be implemented (on a customer's request) for private VMs and dedicated servers.
It is something that cannot be disabled for a single cPanel account or database.
Possible Solutions
Option 1: Migrate to MySQL5.6 support region:
If you are a Web Hosting Magic customer with access to just cPanel, what you may want to do (probably the most viable option) is to ask for the account to be migrated to one of our regions that support MySQL5.6 and see if it will address the issue for you.
Option 2: Rewrite the query on the application-side:
Since selecting a column that is not part of the grouping is not always the right approach, the Percona team suggests that the query be rewritten without those columns:
mysql> SELECT page_url, COUNT(*) AS visits
-> FROM web_log
-> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------+
| page_url | visits |
+-------------------+--------+
| /index.html | 3 |
| /news.php | 2 |
| /store_offers.php | 2 |
| /faq.html | 1 |
+-------------------+--------+
The challenge with this though is that if the queries are part of a legacy application (or if this problem touches tons of queries), you may not want to touch the code base.
You may consider re-architecting your queries to comply with ONLY_FULL_GROUP_BY especially since this is now part of MySQL default values.
Option 3: Upgrade to a VPS:
Customers running private virtual machines have more leeway when it comes to the way the machine is configured or works.
If your application must run with this disabled, then you need to upgrade the account to a private virtual machine.
What this does is to give you more flexibility in the way the system is configured.
For example, you can request that the mode be disabled.
You can also opt that the server run MySQL5.6.
Disable ONLY_FULL_GROUP_BY in a cPanel & WHM machine:
If you are using cPanel & WHM, have "root" access, and want this to be disabled, pull your Terminal and SSH in.
Run the following command to determine which sql_mode options are enabled and copy the output:
sudo mysql -sse "SELECT @@GLOBAL.sql_mode;"
As a best practice, back-up the /etc/my.cnf file before making any change:
sudo cp /etc/my.cnf /etc/my.cnf.backup.`date -I`
Edit /etc/my.cnf:
sudo vim | vi | nano vim /etc/my.cnf
Add:
sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Restart MySQL to load the change:
sudo /scripts/restartsrv_mysql
Verify that ONLY_FULL_GROUP_BY has been removed from the enabled sql_mode options:
mysql -sse "SELECT @@GLOBAL.sql_mode;"
Regular Linux Box:
Query the SQL_MODE setting and find ONLY_FULL_GROUP_BY:
mysql> select version(),@@sql_mode;
Roll back MySQL's configuration to the "forgiving" mode.
Either set the complete "forgiving" mode with the following SQL command:
mysql> SET GLOBAL sql_mode='';
Or remove ONLY_FULL_GROUP_BY to disable this mode:
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Exit the MySQL prompt:
mysql> exit;
Whenever you want to re-enable it:
mysql> SET sql_mode=(SELECT CONCAT(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
For the change to persist, edit my.cnf.
The location of this file varies and you can locate yours with:
mysql –verbose –help
sudo vim /etc/mysql/my.cnf
Add this to my.cnf:
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
OR:
[mysqld]
sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Save and restart:
sudo systemctl restart mysql
PHPMyAdmin:
You can remove ONLY_FULL_GROUP_BY from PHPMyAdmin:
Pull up PHPMyAdmin.
Select localhost.
Click on menu Variables.
Scroll down and look for sql mode.
Click on the Edit button.
Delete the ONLY_FULL_GROUP_BY to change the value.
Save.
Restart MySQL either with:
$ sudo systemctl start mysql.service
Or by visiting WHM >>> Restart Services >>> SQL Server (MySQL) if you are running a cPanel machine.
If you are not sure how to go about this and have access to our VM/dedicated server-managed support, contact our team to get this done for you.
