#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by
MysqlMysql Error-1055Mysql Problem Overview
my Query :
select libelle,credit_initial,disponible_v,sum(montant) as montant
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee
and annee = year(current_timestamp) GROUP BY libelle order by libelle asc
- Mysql 5.7.9 runs the query without any problem but mysql 5.7.12 display the error above Could you please help me with that
Mysql Solutions
Solution 1 - Mysql
I had a struggle getting this to work i've tested it and it's working on lamp server mysql version 5.12
So, steps to success:
sudo vim /etc/mysql/conf.d/mysql.cnf
- Scroll to the bottom of file Copy and paste
[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
to the bottom of the file
- save and exit input mode
sudo service mysql restart
to restart MySQL.
Done!
Solution 2 - Mysql
This worked for me:
mysql -u root -p
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
You might need sudo for the first step:
sudo mysql -u root -p
Solution 3 - Mysql
You need to specify all of the columns that you're not using for an aggregation function in your GROUP BY
clause like this:
select libelle,credit_initial,disponible_v,sum(montant) as montant
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee
and annee = year(current_timestamp) GROUP BY libelle,credit_initial,disponible_v order by libelle asc
The full_group_by
mode basically makes you write more idiomatic SQL. You can turn off this setting if you'd like. There are different ways to do this that are outlined in the MySQL Documentation. Here's MySQL's definition of what I said above:
> 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. (Before > 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of > pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)
You're getting the error because you're on a version < 5.7.5
Solution 4 - Mysql
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';
I have tried this and worked for me. Thanks :)
Solution 5 - Mysql
Solution 1: Remove ONLY_FULL_GROUP_BY from mysql console
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
you can read more here
Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin
Open phpmyadmin & select localhost
Click on menu Variables & scroll down for sql mode
Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.
Solution 6 - Mysql
You have to aggregate by anything NOT IN
the group by
clause.
So,there are two options...Add Credit_Initial and Disponible_v to the group by
OR
Change them to MAX( Credit_Initial ) as Credit_Initial, MAX( Disponible_v ) as Disponible_v
if you know the values are constant anyhow and have no other impact.
Solution 7 - Mysql
Base ond defualt config of 5.7.5 ONLY_FULL_GROUP_BY You should use all the not aggregate column in your group by
select libelle,credit_initial,disponible_v,sum(montant) as montant
FROM fiche,annee,type
where type.id_type=annee.id_type
and annee.id_annee=fiche.id_annee
and annee = year(current_timestamp)
GROUP BY libelle,credit_initial,disponible_v order by libelle asc
Solution 8 - Mysql
So let's fully understand, Let's say you have a query which works in localhost but does not in production mode, This is because in MySQL 5.7 and above
they decided to activate the sql_mode=only_full_group_by
by default, basically it is a strict mode which prevents you to select non aggregated fields.
Here's the query (works in local but not in production mode) :
SELECT post.*, YEAR(created_at) as year
FROM post
GROUP BY year
SELECT post.id, YEAR(created_at) as year // This will generate an error since there are many ids
FROM post
GROUP BY year
To verify if the sql_mode=only_full_group_by
is activated for, you should execute the following query :
SELECT @@sql_mode; //localhost
Output : IGNORE_SPACE, STRICT_TRANS, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
(If you don't see it, it means it is deactivated)
But if try in production mode, or somewhere where it gives you the error it should be activated:
SELECT @@sql_mode; //production
Output: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO...
And it's ONLY_FULL_GROUP_BY
we're looking for here.
Otherwise, if you are using phpMyAdmin then go to -> Variables
and search for sql_mode
Let's take our previous example and adapt it to it :
SELECT MIN(post.id), YEAR(created_at) as year //Here we are solving the problem with MIN()
FROM post
GROUP BY year
And the same for MAX()
And if we want all the IDs, we're going to need:
SELECT GROUP_CONCAT(post.id SEPARATOR ','), YEAR(created_at) as year
FROM post
GROUP BY year
or another newly added function:
SELECT ANY_VALUE(post.id), YEAR(created_at) as year
FROM post
GROUP BY year
⚠️ ANY_VALUE does not exist for MariaDB
And If you want all the fields, then you could use the same:
SELECT ANY_VALUE(post.id), ANY_VALUE(post.slug), ANY_VALUE(post.content) YEAR(created_at) as year
FROM post
GROUP BY year
❌ To deactivate the sql_mode=only_full_group_by
then you'll need to execute this query:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Sorry for the novel, hope it helps.
Solution 9 - Mysql
Same thing is happened with 8.0+ versions as well. By default in 8.0+ version it is "enabled" by default. Here is the link official document reference
In case of 5.6+, 5.7+ versions, the property "ONLY_FULL_GROUP_BY" is disabled by default.
To disabled it, follow the same steps suggested by @Miloud BAKTETE
Solution 10 - Mysql
Also if you use MySql 8+ try in console this command:
Open MySql console
mysql -u user_name -p
And set global SQL mode
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
Solution 11 - Mysql
In Laravel, you just set false
in file database.php
.
...
'mysql' => [
...
'strict' => false,
...
]
Solution 12 - Mysql
just go to the window bottom tray click on wamp icon ,click mysql->my.ini,then there is option ;sql-mode="" uncomment this make it like sql-mode="" and restart wamp worked for me