mysql - SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by
Get the solution ↓↓↓AM using MySQL 5.7.13 on my windows PC with WAMP Server
Here my Problem is While executing this query
SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
`status` = 'Active'
GROUP BY `proof_type`
Am getting always error like this
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Can you please tell me the best solution...
I need Result like
+----+---------+---------+---------+
Answer
Answer
Answer
Solution:
This
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
will be simply solved by changing the sql mode in MySQL by this command,
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
This too works for me.. I used this, because in my project there are many Queries like this so I just changed this sql mode to only_full_group_by
OR simply include all columns in the GROUP BY clause that was specified by the SELECT statement. The sql_mode can be left enabled.
Thank You... :-)
Answer
Solution:
When MySQL'sonly_full_group_by
mode is turned on, it means that strict ANSI SQL rules will apply when usingGROUP BY
. With regard to your query, this means that if youGROUP BY
of theproof_type
column, then you can only select two things:
- the
proof_type
column, or - aggregates of any other column
By "aggregates" of other columns, I mean using an aggregate function such asMIN()
,MAX()
, orAVG()
with another column. So in your case the following query would be valid:
SELECT proof_type,
MAX(id) AS max_id,
MAX(some_col),
MIN(some_other_col)
FROM tbl_customer_pod_uploads
WHERE load_id = '78' AND
status = 'Active'
GROUP BY proof_type
The vast majority of MySQLGROUP BY
questions which I see on SO have strict mode turned off, so the query is running, but with incorrect results. In your case, the query won't run at all, forcing you to think about what you really want to do.
Note: The ANSI SQL extends what is allowed to be selected inGROUP BY
by also including columns which are functionally dependent on the column(s) being selected. An example of functional dependency would be grouping by a primary key column in a table. Since the primary key is guaranteed to be unique for every record, therefore the value of any other column would also be determined. MySQL is one of the databases which allows for this (SQL Server and Oracle do not AFAIK).
Answer
Solution:
There is a system variable ONLY_FULL_GROUP_BY
in MySql engine.
From Mysql Version 5.7.5:ONLY_FULL_GROUP_BY
SQL mode is enabled by default
Before Version 5.7.5:ONLY_FULL_GROUP_BY
was not enabled by default.
If theONLY_FULL_GROUP_BY
SQL mode is enabled (which it is by default from version 5.7.5), MySQL rejects queries for which the select list,HAVING
condition, orORDER BY
list refer to non-aggregated columns that are neither named in theGROUP BY
clause nor are functionally dependent on them.
To sort out the issue, use any one solution (out of below 3)
(1) PHPMyAdmin
Disable:ONLY_FULL_GROUP_BY
mode
if you are using phpMyAdmin then change the sql_mode
setting as mentioned in the below screenshot.
Editsql mode
variable and remove theONLY_FULL_GROUP_BY
text from the value
OR
(2) SQL/Command prompt
Disable:ONLY_FULL_GROUP_BY
mode by running the below command.
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
OR
(3) Don't useSELECT *
Do not disable theONLY_FULL_GROUP_BY
mode but
Use relevant column inSELECT
query. relevant means columns, which are either coming ingroup by
clause or column with the aggregate function (MAX
,MIN
,SUM
,COUNT
etc)
Important note
Changes made by usingpoint(1) OR point(2)
does not set it PERMANENTLY, and it will revert after every restart.
So you should set this in your config file (e.g./etc/mysql/my.cnf
in the[mysqld]
section), so that the changes remain in effect after MySQL restart:
Config File:/etc/mysql/my.cnf
Variable name:sql_mode
ORsql-mode
Remove word ONLY_FULL_GROUP_BY
from the value and save the file.
Note: If you have not foundsql_mode
variable in the config file than please insert below 2 lines at 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
Answer
Solution:
{-code-18} = on tells MySQL engine: Do not applyGROUP BY
when you have doubt about what results to show and throw an error. Only apply it ifGroup By
specifically tells you what to do. i.e. when theGroup By
is full and perfect!
{-code-18} = off tells MySQL engine: always applyGROUP BY
and if you have doubt about what results to choose, just pick one randomly!
You don't have to turn it off if you useGROUP BY
properly!
Example:
Table: users
id | name
When you useGROUP BY
on the{-code-8}
column:
SELECT * FROM users GROUP BY {-code-8};
There are two possible results:
{-code-10}
OR
{-code-11}
MYSQL does not know what result to choose! Because there are different{-code-12}
s but both have{-code-8}={-code-25}
.
Solution1:
only selecting the{-code-8}
field:
SELECT {-code-8} FROM users GROUP BY {-code-8};
result:
{-code-16}
This is a perfect solution. removing columns that makesGROUP BY
confused. This means you know what you're doing. Usually, you do not need
those columns, but if you need them, go to Solution3!
Solution2:
Turning off{-code-18}
. MYSQL will pick one of the two possible results RANDOMLY!! (It's ok if you do not really care what{-code-12}
it will choose, but remember to turn it on immediately after your query to prevent unexpected behaviors in future groupBys)
Solution3
Use an{-code-20}
function like{-code-21}
,{-code-22}
to help MYSQL to dec{-code-12}e what it must choose.
For example:
SELECT MAX({-code-12}), {-code-8} FROM users GROUP BY {-code-8};
result:
{-code-24}
It will choose the{-code-25}
row which has the maximum{-code-12}
.
Answer
Answer
Solution:
Below method solved my problem:
In ubuntu
Type:sudo vi /etc/mysql/my.cnf
type A to enter insert mode
In the last line paste below two line code:
[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
Type esc to exit input mode
Type :wq to save and close vim.
Typesudo service mysql restart
to restart MySQL.
Answer
Solution:
You can disable sql_mode=only_full_group_by by some command you can try this by terminal or MySql IDE
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Answer
Solution:
In Ubuntu
Step 1:
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Step 2: Go to last line and add the following
sql_mode = ""
Step 3: Save
Step 4: Restart mysql server.
Answer
Solution:
Hi instead of taking all columns, just take what you need by usingANY_VALUE(column_name)
. It is working perfectly. Just check.
E.g.:
SELECT proof_type,any_value("customer_name") as customer_name
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND `status` = 'Active' GROUP BY `proof_type`
Answer
Solution:
For the query to be legal in SQL92, the name column must be omitted from the select list or named in the GROUP BY clause.
SQL99 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.
MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.
via MySQL :: MySQL 5.7 Reference Manual :: 12.19.3 MySQL Handling of GROUP BY
You can solve it by changing the sql mode with this command:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
and ... remember to reconnect the database!!!
Answer
Solution:
From how it looks, I think grouping by multiple columns/fields wont hurt your result. Why don't you try adding to the group by like this:
GROUP BY `proof_type`, `id`
This will group byproof_type
first thenid
. I hope this does not alter the results. In some/most cases group by multiple columns gives wrong results.
Answer
Solution:
go to the phpmyadmin and open the console and execute this request
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Answer
Solution:
> sudo nano /etc/mysql/my.cnf
Enter below
[mysqld]
sql_mode = ""
Ctrl + O => Y = Ctrl + X
> sudo service mysql restart
Answer
Solution:
I was facing this issue and the following query wasn't enough
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
because I was using Stored Procedure. After executing this query I had to drop the procedure and create again.
Answer
Solution:
I had similar problem with part of my database crushing. What I did is I changed the parameter in the DB via PHPStorm database console like this:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
works like charm now
Answer
Solution:
Search for "SQL mode" if you are using PhpMyAdmin and take off the value:ONLY_FULL_GROUP_BY
, just did and it okay.
Answer
Solution:
Update for MySQL 8.0
Yoursql-mode
will not haveNO_AUTO_CREATE_USER
as it has been removed as mentioned here - how-to-set-sql-mode-in-my-cnf-in-mysql-8
[mysqld]
sql-mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Also if someone doesn't have amy.cnf
file then they can create a new one in/etc/my.cnf
and then add the above lines.
Answer
Solution:
Before running you query run bellow query
SET SQL_MODE = '';
Answer
Solution:
- Login to phpMyAdmin
- Navigate to : Server: localhost:3306 and do not select any database
- Click on variables from the top menu
- Search for "sql mode" and edit the corresponding value to : NO_ENGINE_SUBSTITUTION
That's all.
I did this in my Ec2 and it worked like charm.
Answer
Solution:
Here is a really fast and easy way of setting it permanently
NB: runningSET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
is temporary and on server restart you will still end up with the error.
To fix this permanently do the below
- Login to your server as root
- in your terminal run
wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh
- Make the script executable by running
chmod +x disable-strict-mode.sh
- Run the script by running
./disable-strict-mode.sh
And your done , changes will be made to mysql and it will be restarted
Answer
Solution:
You can also simply add GROUP_CONCAT() to the nonaggregated columns.
likeGROUP_CONCAT(your_column)
Answer
Solution:
For The ones using CentOS/RHEL (Linux server) and XAMPP Locally
Hello, I've come to a very different solution and I hope this helps somone. (May look overkill but this really worked for me unlike other solutions)
I am running CentOS 7 and all my code was working locally, but when I uploaded it to my server I started getting the error which this question addresses.
After some hours I tried viewing the problem from a different angle, I remembered that my local setup uses XAMPP and I thought I was using MySQL 8 (MySQL 8 was installed on my server).
But after logging in into MySql from XAMPP I got the next output:
mysql -u root -p //Login
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.4.18-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Yes you may have noticed the same, XAMPP uses MariaDB, so the solution was to uninstall mysql and install MariaDB
And as I am running Centos 7 the steps to do this are the ones below:
- Remove mysql from your server
# yum remove mysql mysql-server
- Update your system (this is common everytime we install something new)
# sudo yum -y update
- Add MariaDB to repository
# sudo tee /etc/yum.repos.d/MariaDB.repo<<EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
- Update cache
# sudo yum makecache fast
- Install MariaDB
# sudo yum -y install MariaDB-server MariaDB-client
- Add MariaDB to the startup (Start MariaDB every time your system gets restarted)
#sudo systemctl enable --now mariadb
Then you can secure your installation by running the next command:
# sudo mysql_secure_installation
This last command will begin a process where you can set password, and other options.
After that dont forget to add non-root user and grant it the necessary privileges, that's because you can't use root user in your applications (Yes I had to figure that out later)
First Login using the root account:
# mysql -u root -p
Then add your user and grant privileges:
CREATE USER 'YourUserName'@localhost IDENTIFIED BY 'YourPassword';
GRANT ALL PRIVILEGES ON *.* TO 'YourUserName'@localhost IDENTIFIED BY 'YourPassword' WITH GRANT OPTION;
And finally you have to create your database, import your tables/data/triggers/procedures.
Now you will be able to run your code without any problems, just as your local setup. (Maybe you will have to install mysql extensions in case you had to do it locally too).
Answer
Solution:
Login to phpMyAdmin
Navigate to : Server: http://localhost/phpmyadmin and do not select any database
Click on SQL from the top menu and pest below code Run SQL query/queries on serverSET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Sorce link
Answer
Solution:
Open you WAMP panel and open MySQL configuration file. In it search for "sql_mode" if you find it set it to "" else if you don't find it add sql_mode="" to the file.
Restart the MySQL server and you are good to go...
happy coding.
Answer
Solution:
In yourmy.ini
, write this:
[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"
depend on your version. Or:
[mysqld]
sql_mode = ""
or simply remove this:ONLY_FULL_GROUP_BY
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: composer detected issues in your platform: your composer dependencies require a php version ">= 8.0.2".
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.