Sometimes when we handle queries like GROUP BY and SELECT clause without any aggregate functions (SUM(), COUNT(), MAX()), that will lead to error because MySQL doesn’t know how to handle the query, and also there will be an error when Only_Full_Group_By is enabled in the system. In this blog, you will explore different approaches to disable ONLY_FULL_GROUP_BY in MySQL in detail with examples for each.
Table of Contents:
What is ONLY_FULL_GROUP_BY in MySQL?
The ONLY_FULL_GROUP_BY is a mode in MySQL that follows rules for GROUP BY queries. This mode strictly follows that all the selected columns that were being created in the system must either include the GROUP BY clause or it should use aggregate functions with a select statement. If this rule is violated, MySQL throws an error during query execution:
Expression #1 of the SELECT list is not in the GROUP BY clause and contains the nonaggregated column ‘db.table.col’, which is not functionally dependent on columns in the GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
This error happens because MySQL expects all selected columns to be either in the GROUP BY clause or used with an aggregate function.
Why do we need to disable ONLY_FULL_GROUP_BY in MySQL?
As the ONLY_FULL_GROUP_BY mode in MySQL regulates structural and standard SQL queries, it ensures compliance with strict SQL rules. But sometimes it needs to be disabled because many applications were developed even before these strict rules were developed. So, while performing in the old versions, we might encounter the error, so to prevent this, we need to disable the ONLY_FULL_GROUP_BY mode in the system environment.
When working on report or analysis-based queries using GROUP BY, developers need some flexibility in the usage of the GROUP-BY clause. At that time, GROUP BY may raise an error, even if the query returns a logically correct result.
So, by disabling it, we can write the query easily, and it will be compatible with older applications to perform operations while reducing the time for query modification.
How to check whether ONLY_FULL_GROUP_BY is enabled or disabled in MySQL?
There are a few methods to check whether ONLY_FULL_GROUP_BY is enabled or not.
Method 1: Using SELECT statement in MySQL
The SELECT statement uses the query below to check the status of ONLY_FULL_GROUP_BY mode.
Query:
SELECT @@sql_mode;
Output:
Explanation: The output returns a list of enabled SQL modes, and the ONLY_FULL_GROUP_BY appears there, which means the ONLY_FULL_GROUP_BY mode is enabled.
Method 2: Using the SHOW VARIABLES command in MySQL
This command is an alternative method to check if the mode is enabled or not.
Query:
SHOW VARIABLES LIKE 'sql_mode';
Output:
Explanation: The output shows that the ONLY_FULL_GROUP_BY mode is enabled, as you can see in the list of enabled modes in the output.
Methods to Disable ONLY_FULL_GROUP_BY in MySQL
You can perform the disable commands on your MySQL server that is installed in your system environment. By following these methods, based on the level that you want to work on.
Method 1: Session-level disabling in MySQL
The session-level disabling in MySQL will temporarily disable your system’s ONLY_FULL_GROUP_BY mode. This will only disable your current session.
Query:
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Output: This command will not give you any output, it will just temporarily terminate your session.
Explanation: After ONLY_FULL_GROUP_BY mode is disabled for your current session, you can enable the mode by disconnecting and reconnecting your session.
Method 2: Global-level disabling in MySQL
This method will disable the ONLY_FULL_GROUP_BY mode for the entire session, but the previous session will not be affected by this. To run this query, you need to have the necessary privilege, if you don’t have it, you will have an error in disabling the mode.
Query:
SET GLOBAL sql_mode = (SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Note: If you have the necessary privileges, then while running this command, you won’t get any error while running this code, and the mode will be disabled.
Output:
Explanation: If you don’t have the necessary permissions, then you will get this error as output.
Method 3: Permanently Disabling via Configuration File in MySQL
This method uses the MySQL configuration file to disable the ONLY_FULL_GROUP_BY mode. The configuration files like my.cnf or my.ini will be used. This is a method to change a file, so it will not give any output, but you can verify whether it is disabled or not by restarting MySQL. You can use the below query:
First locate the configured file, my.cnf or my.ini
In Linux: /etc/mysql/my.cnf or /etc/my.cnf
Check the file location.
In windows: C:\ProgramData\MySQL\MySQL Server X.X\my.ini
In the [mysqld] section, add or modify the following:
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Output:
You can see that ONLY_FULL_GROUP_BY mode is not there, then we can infer that ONLY_FULL_GROUP_BY mode is disabled.
Now, we can restart the file in Linux by using this bash command in the Linux command prompt.
sudo systemctl restart mysql
In Windows, you can restart MySQL.
SearchService from Services(services.msc) Click to restart MySQL.
Note: By this, we can disable the ONLY_FULL_GROUP_BY mode permanently.
Alternative Approaches
Using ANY_VALUE() for Non-Aggregated Columns in MySQL
When using GROUP BY in MySQL, we need to follow certain conditions or rules by ONLY_FULL_GROUP_BY, which either create columns by GROUP BY or by any aggregate functions. However, this can be overridden by using non-aggregated columns. If you need to select a column that doesn’t fit the rules, you can usethe ANY_VALUE() function.
Example:
-- Step 2: Create the `orders` table
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
sales DECIMAL(10,2) NOT NULL
);
-- Step 3: Insert sample data into the `orders` table
INSERT INTO orders (user_id, name, sales) VALUES
(1, 'Mary', 100),
(2, 'Baskar', 200),
(1, 'Mary', 150),
(2, 'Basker', 250),
(3, 'Kiran', 300),
(3, 'Kiran', 350);
SELECT user_id, name, SUM(sales) AS total_sales
FROM orders
GROUP BY user_id;
Output:
Explanation: This occurs as an error because the ONLY_FULL_GROUP_BY mode is enabled and the name doesn’t have any aggregate function or group by.
To fix this we can use ANY_VALUE()
-- Step 1: Create the `orders` table
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
sales DECIMAL(10,2) NOT NULL
);
-- Step 2: Insert sample data into the `orders` table
INSERT INTO orders (user_id, name, sales) VALUES
(1, 'Mary', 100),
(2, 'Baskar', 200),
(1, 'Mary', 150),
(2, 'Basker', 250),
(3, 'Kiran', 300),
(3, 'Kiran', 350);
-- Step 3: Run the query using `ANY_VALUE()`
SELECT user_id, ANY_VALUE(name) AS name, SUM(sales) AS total_sales
FROM orders
GROUP BY user_id;
Output:
Explanation: Now, this query runs successfully, even if ONLY_FULL_GROUP_BY mode is enabled. The ANY_VALUE will instruct MySQL to pick any function for the name of each user ID without any errors.
Permission Issues When Modifying SQL Mode in MySQL
While modifying the SQL mode, sometimes you may face an error. That is due to insufficient privileges.
This will be the error message when you try to change the sql_mode without the privilege.
There are reasons why the error occurs.
- Lack of admin privileges like SYSTEM_VARIABLES_ADMIN may require modification in GLOBAL sql_mode.
- In Linux, the user is not the root user or admin: this cannot be changed because a user cannot change the global variable.
- Using third-party MySQL hosting services like AWS or Google Cloud, which don’t allow the users to set global SQL_mode.
To fix all these issues,
1. First, check the user privilege in the Linux command prompt
SHOW GRANTS FOR CURRENT_USER;
In the prompt, if you don’t see SUPER or SYSTEM_VARIABLES_ADMIN, it means that you don’t have the privilege.
2. If you are a Root or ADMIN user or in an ADMIN account, log in to that:
mysql -u root -p
3. Try to change the sql_mode
SET GLOBAL sql_mode = (SELECT REPLACE(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', ''));
4. Modify the necessary privilege you want through the root user account.
GRANT SUPER ON *.* TO 'your_user'@'localhost';
FLUSH PRIVILEGES;
5. Log out from the user account and then try modifying the sql_mode from your account.
Use Cases
- Application compatibility: When queried, older applications, probably built before strict SQL standards, might break, they might have references or expect results to follow certain attributes. When required, ONLY_FULL_GROUP_BY can be turned on.
- Flexibility: A developer could decide that GROUP BY should be used, but without adjusting for MySQL’s stricter rules.
- Disabling ONLY_FULL_GROUP_BY: This could help prevent the error when a query groups by a non-aggregated column but does not introduce an aggregate function such as ANY_VALUE().
- External tools: Some external tools, plugins, or ORMs may not support ONLY_FULL_GROUP_BY, requiring it to be turned off for compatibility.
Conclusion
In conclusion, the ONLY_FULL_GROUP_BY mode in MySQL follows strict grouping rules, which causes errors while executing the older versions of SQL. The ONLY_FULL_GROUP_BY mode ensures strict structured SQL queries while selecting columns. By disabling the ONLY_FULL_GROUP_BY mode, you can create queries, which can provide flexibility for developers to work with. There are many levels of configuration, like session-level, global-level, and permanent configuration using configuration files like my.cnf and my.ini. TheONLY_FULL_GROUP_BY mode can be overridden by using the ANY_VALUE() function. Understanding these methods will help develop applications without any struggles. To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts.
How to Disable ONLY_FULL_GROUP_BY in MySQL? – FAQs
1. What is ONLY_FULL_GROUP_BY?
A MySQL mode that enforces strict GROUP BY rules, requiring all selected columns to be in GROUP BY or use aggregate functions.
2. Why disable ONLY_FULL_GROUP_BY?
It can cause errors in legacy applications or when queries need flexibility without strict grouping rules.
3. How do I check if ONLY_FULL_GROUP_BY is enabled?
Run: SELECT @@sql_mode;
If ONLY_FULL_GROUP_BY appears in the result, it is enabled.
4. How to disable it temporarily?
Run: SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, ‘ONLY_FULL_GROUP_BY’, ”));
5. How to disable it permanently?
Remove ONLY_FULL_GROUP_BY from the sql_mode setting in my.cnf or my.ini, then restart MySQL.