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
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| id | user_id | load_id | bill_id | latitude | langitude | proof_type | document_type | file_name | is_private | status | createdon | updatedon |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| 1 | 1 | 78 | 1 | 21.1212 | 21.5454 | 1 | 1 | id_Card.docx | 0 | Active | 2017-01-27 11:30:11 | 2017-01-27 11:30:14 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
This question is related to
mysql
group-by
mysql-error-1055
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.
Type sudo service mysql restart
to restart MySQL.
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.
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.
only_full_group_by = on tells MySQL engine: Do not apply GROUP BY
when you have doubt about what results to show and throw an error. only apply it if the command specifically tells you what to do. i.e. when the command is full and complete!
only_full_group_by = off tells MySQL engine: always apply GROUP 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 use GROUP BY
properly!
Example:
Table: users
id | name
----------------
1 ali
2 john
3 ali
When you use GROUP BY
on the name
column:
SELECT * FROM users GROUP BY name;
There are two possible results:
1 ali
2 john
OR
2 john
3 ali
MYSQL does not know what result to choose! Because there are different id
s but both have name=ali
.
Solution1:
only selecting the name
field:
SELECT name FROM users GROUP BY name;
result:
ali
john
This is a perfect solution. removing columns that makes GROUP 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 only_full_group_by
. MYSQL will show you one of the two possible results RANDOMLY!! (It's ok if you do not really care what id
it will choose)
Solution3
Use an Aggregate
function like MIN()
, MAX()
to help MYSQL to decide what it must choose.
For example, I want the minimum id
:
SELECT MIN(id), name FROM users GROUP BY name;
result:
1 ali
2 john
It will choose the ali
row which has the minimum id
.
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';
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!!!
Hi instead of taking all columns, just take what you need by using ANY_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`
go to the phpmyadmin and open the console and execute this request
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
(1) PHPMyAdmin
if you are using phpMyAdmin then change the "sql_mode" setting as mentioned in the below screenshot.
Edit "sql mode" variable and remove the "ONLY_FULL_GROUP_BY" text from the value
OR
(2) SQL/Command prompt Run the below command.
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
OR
(3) Don't use SELECT *
Use relevant column in SELECT query. relevant means columns, which are either coming in "group by" clause or column with the aggregate function (MAX, MIN, SUM, COUNT etc)
Changes made by using point(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 OR sql-mode
Remove word ONLY_FULL_GROUP_BY from the value and save the file.
Note : If you have not found "sql_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
Search for "SQL mode" if you are using PhpMyAdmin and take off the value: ONLY_FULL_GROUP_BY
, just did and it okay.
That's all.
I did this in my Ec2 and it worked like charm.
Update for MySQL 8.0
Your sql-mode
will not have NO_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 a my.cnf
file then they create a new one in /etc/my.cnf
and then add the above lines.
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 by proof_type
first then id
. I hope this does not alter the results. In some/most cases group by multiple columns gives wrong results.
Here is a really fast and easy way of setting it permanently
NB: running SET 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
wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh
chmod +x disable-strict-mode.sh
./disable-strict-mode.sh
And your done , changes will be made to mysql and it will be restarted
> sudo nano /etc/mysql/my.cnf
Enter below
[mysqld]
sql_mode = ""
Ctrl + O => Y = Ctrl + X
> sudo service mysql restart
When MySQL's only_full_group_by
mode is turned on, it means that strict ANSI SQL rules will apply when using GROUP BY
. With regard to your query, this means that if you GROUP BY
of the proof_type
column, then you can only select two things:
proof_type
column, or
By "aggregates" of other columns, I mean using an aggregate function such as MIN()
, MAX()
, or AVG()
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 MySQL GROUP 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 in GROUP 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).
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
In your my.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
Source: Stackoverflow.com