SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

Question

I'm using MySQL 5.7.13 on my windows PC with WAMP Server.

My problem is while executing this query

SELECT *
FROM `tbl_customer_pod_uploads`
WHERE `load_id` = '78' AND
      `status` = 'Active'
GROUP BY `proof_type`

I'm 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?

My result should be like below:

+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+
| 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 |
+----+---------+---------+---------+----------+-----------+------------+---------------+--------------+------------+--------+---------------------+---------------------+

Homework

Don't use SELECT *. – Memory

dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – Memory

are saying like this SELECT id FROM tbl_customer_pod_uploads WHERE load_id = '78' AND status = 'Active' GROUP BY proof_type – Homework

Even Still am getting the same error "Expression #1 of SELECT list is.... " – Homework

Yes, that's better. Now the question is, what if one proof_type maps to different ids? Which one should be selected? – Memory

If you want compatibility for old queries, you can turn off the only_full_group_by SQL mode. – Denomination

If you show us sample data and desired output, I'm sure someone can give you a query. – Mccune

Try using ANY_VALUE(proof_type): dev.mysql.com/doc/refman/5.7/en/group-by-handling.html SELECT *, ANY_VALUE(proof_type) FROM tbl_customer_pod_uploads WHERE load_id = '78' AND status = 'Active' GROUP BY proof_type – Camelopardalis

The tricky thing here is that if you don't set sql_mode at all in my.cnf ( i.e it's absent) when you upgrade the mysql 5.7 you suddenly get this only_full_group_by issue by default . set sql_mode='' in my.cnf solves the issue – Guanabana

If you use Gcloud sql service, add flag sql_mode and choice traditional. – Chenault

A nice answer is also over here (with how to change the mysql code): dba.stackexchange.com/q/237048/200937 – Lusaka

Answer 1

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 the 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. :-)

---

Updated:14 Jul 2023

Changing SQL mode is a solution, but still, the best practice for Structured Query Language will be avoid selecting all (SELECT * ...) columns, instead use aggregator functions on the grouping columns as mentioned by @Tim Biegeleisen below answers https://mcmap.net/q/80719/-select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-incompatible-with-sql_mode-only_full_group_by

Homework

It works for me!. You save my day. Remember to RESTART MYSQL SERVICE – Retrorocket

If i am using codeigniter and i want to execute in model, then how would i use it ? Can help me ? @Retrorocket – Hayes

@DhavalThakor, once you run this query in your server, you no need to run again and again. so you no need to put it in your model – Homework

MySQL 5.7.29, didn't need to restart the service – Embed

I'm using the server version: 5.7.31-0ubuntu0.16.04.1 - (Ubuntu), and whenever I start my machine, i set it every time, Is there any permanent fix for this? – Fauces

NOTE: changing the sql_mode will not affect stored procedures. So you need to drop and execute SP again to take affect – Supernova

I changed global to session and it worked! 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'; – Nagano

It did not work for me with global even after restart. This worked for me when I changed it in the session as Fariman. But it wasn't persistent. To make it persistent, I set the following in my.cnf sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION – Josefinejoseito

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); this is working fine – Gudrunguelderrose

Including all columns in the GROUP BY clause worked.Thanks. – Predigest

so, is it safe to change this? and why this option is enabled? – Approbate

"OR simply include all columns in the GROUP BY clause that was specified by the SELECT statement. The sql_mode can be left enabled." I was searching for this literally for years, ^^ – Servais

Scroll down and see answers(like https://mcmap.net/q/80719/-select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-incompatible-with-sql_mode-only_full_group_by) to understand why this restriction exists in the first place. Don't follow this answer unless you want random results and know what you are doing. – Timbering

First of all, you are not changing the sql mode to ONLY_FULL_GROUP_BY. You just turned off ONLY_FULL_GROUP_BY to OFF which was already ON by default and causing the error in your query. I don't think turning it OFF is a good approach. I would definitely keep it turned ON and change all instances of group by which triggers the exception, even if it's a big application. You may temporarily turn it off, correct all the queries with group by and the later turn ONLY_FULL_GROUP_BY back to ON. – Peer

where and in which file we have to write this piece code? – Homoio

@ZiaKhan that was query, so run it on a MySQL client query tool – Homework

The latest MySQL version worked. Thank you <3 – Westfall

If using Azure MySQL Flex Server I was able to do this through the Azure Portal Interface. Select the Flex Server from the Resources List then select Server Parameters. Search for SQL Mode. Select SQL Mode and select the dropdown box. Uncheck ONLY_FULL_GROUP_BY. I did not have to restart the service although it may have in the background due to the change. – Ellora

@Josefinejoseito can u please check for permanent fix #2318150 – Bashuk

@DhanuK pls update the answer with permanent fix as well – Bashuk

Why is this solution not permanent? – Roberge

I experienced an error during the installation of OrangeHRM. This query solved the problem – Demosthenes

Answer 2

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 the ONLY_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, or ORDER BY list refer to non-aggregated columns that are neither named in the GROUP 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.

Edit sql mode variable and remove the ONLY_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 use SELECT *

Do not disable the ONLY_FULL_GROUP_BY mode but

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)

---

Important note

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

Succinic

Thank you so much for the above answer. I want to change this as you said in my my.cnf. But when I open that file, it opens only in read-only mode. Any helps or reference links???. I use Ubuntu 16.04 – Beyer

(1) Change the file permission of file my.cnf by using the command "chmod". (2) Perform changes in my.cnf (3) set it read-only again (change file permission again using "chmod") (4) restart mysql – Succinic

I have removed that option. but still I see the error – Mojave

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); This is working fine for me. – Gudrunguelderrose

@RakeshSoni can u mention the syntax for newer versions of mysql as well as this syntax in point 3 does not allow restarting mysql server > 5.7. UPDATE: Newer versions of Mysql (e.g. 5.7.8 or above) may require slightly different syntax: [mysqld] sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" – Bashuk

Answer 3

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:

• the proof_type column, or
• aggregates of any other column

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).

Mccune

Just one note: The result is not incorrect (it follows MySQL rules), but it is unpredictable, meaning, columns not part of group by and not aggregated (and not functionally dependent) will return a 'random' value from the corresponding group. As the manual states: "the server is free to choose any value from each group" – Barsky

Violating strict grouping doesn't necessarily produce incorrect results. I usually do it when the fields are guaranteed to be identical (e.g. a one-to-many join). And even if it's not guaranteed, selecting an arbitrary row will often be no more incorrect then using an aggregate function or adding a grouping level. – Memory

+1 for me. I have no idea why the "accepted" answer above has been accepted. Your way is the correct way and the accepted answer is the usual hacky fix which will lead to more issues in future. – Macpherson

@Macpherson ... and I couldn't agree with your comment more +1. Turning off the ANSI restriction on GROUP BY is almost always a bad idea. It scares me that so many users are taking the wrong advice and using it. – Mccune

@TimBiegeleisen welcome to modern copy+paste development. "I don't need to think, someone who has read a random thing somewhere from an unreliable source has already done the thinking for me". – Macpherson

This should be the accepted answer. It answers the question of the best solution and explains why it doesn't work in the current setup. Not all developers have access to these settings or are allowed to change these settings. – Bronez

+1 for "forcing you to think about what you really want to do". I wasn't doing what I really wanted to do! – Dissoluble

It works for me! Your way is the correct way of solving this issue. You saved my day. – Charismatic

Answer 4

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 Group By specifically tells you what to do. i.e. when the Group By is full and perfect!

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 ids but both have name=ali.

Solution 1:

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 Solution 3. (Not solution 2!)

Solution 2:

Turning off only_full_group_by. MYSQL will pick one of the two possible results RANDOMLY!! (It's ok if you do not really care what id it will choose, but remember to turn it on immediately after your query to prevent unexpected behaviors in future groupBys)

Solution 3

Use an Aggregate function like MIN(), MAX() to help MYSQL to decide what it must choose.

For example:

SELECT MAX(id), name FROM users GROUP BY name;

It will choose the ali row which has the maximum id:

  2      john     
  3      ali

Side Note: Please notice MySQL does not care what data you have in your table. It's the query itself that is ambiguous. So for example you may have even no records in the table and still see the only_full_group_by error.

Louanne

This answer helped me understand it best. Thank you. – Stanleystanly

Thanks so much to explain this issue. Now I understand better what is wrong in my query! – Upon

and if there are no ambiguous fields, because after a "LEFT JOIN" only one record goes? Then why I get this? – Outshoot

@JohnSmith did not quite get what you mean, but I think for your specific table data you get only one record, so if you had more (different) data, you would get multiple results? I think Mysql does not care (predict) if you will have one or many results, it's the query and the fields that comes after SELECT that are ambiguous. – Louanne

yes, it could be. Thanks. – Outshoot

Answer 5

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.

Tempi

thanks, it worked ! in cent os, whm, my.cnf was located at sudo vi /etc/my.cnf – Mishear

Answer 6

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';

Landfall

Answer 7

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.

Wyeth

Answer 8

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`

Dusa

This worked for me. The MySQL docs state "This function is useful for GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine." So if your DB user doesn't have permissions to turn off 'ONLY_FULL_GROUP_BY', this is another option to consider. – Dibucaine

Answer 9

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.

Calondra

Answer 10

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!!!

Albi

Answer 11

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 can create a new one in /etc/mysql/my.cnf and then add the above lines.

Mandler

Answer 12

> sudo nano /etc/mysql/my.cnf

Enter below

[mysqld]
sql_mode = ""

Ctrl + O => Y = Ctrl + X

> sudo service mysql restart

Spicule

Answer 13

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.

Insouciant

Answer 14

Two solutions:

a) Use

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

b) You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

ANY_VALUE(value)

Apollinaire

I have to use it like this for it to work: SET @@sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); – Eadwina

Answer 15

go to the phpmyadmin and open the console and execute this request

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Valedictory

Answer 16

In my case, This was my old sql_mode

sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

You can check sql_mode with

show variables like "sql_mode";

then i SET it as this

SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Ouabain

Answer 17

Before running you query run bellow query

SET SQL_MODE = '';

Cynical

But it can erase you things you need So use it https://mcmap.net/q/80719/-select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-incompatible-with-sql_mode-only_full_group_by without the global – Alina

Answer 18

Step 1. Check sql mode:

SELECT @@GLOBAL.sql_mode;

Step 2. Create a new configuration file under the /etc/mysql/conf.d/ directory:

sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf

Enter the text below on the editor:

[mysqld]

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Step 3. Restart MySQL:

sudo service mysql restart

Step 4. Confirm the change:

SELECT @@GLOBAL.sql_mode;

Carrelli

Answer 19

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

Chibouk

Answer 20

Search for "SQL mode" if you are using PhpMyAdmin and take off the value: ONLY_FULL_GROUP_BY, just did and it okay.

Busily

Answer 21

1. Login to phpMyAdmin
2. Navigate to : Server: localhost:3306 and do not select any database
3. Click on variables from the top menu
4. 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.

Sesquicarbonate

Answer 22

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

1. Login to your server as root
2. in your terminal run wget https://gist.githubusercontent.com/nfourtythree/90fb8ef5eeafdf478f522720314c60bd/raw/disable-strict-mode.sh
3. Make the script executable by running chmod +x disable-strict-mode.sh
4. Run the script by running ./disable-strict-mode.sh

And your done , changes will be made to mysql and it will be restarted

Disaffect

Answer 23

1. 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';

2. If the problem persists and you are using Laravel, set 'strict' to false in config/database.php

   'mysql' => [ ... 'strict' => false, ... ],

Polymorphism

Answer 24

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

Whortleberry

Answer 25

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:

1. Remove mysql from your server

---

# yum remove mysql mysql-server

2. Update your system (this is common everytime we install something new)

---

# sudo yum  -y update

3. 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

4. Update cache

---

# sudo yum makecache fast

5. Install MariaDB

---

# sudo yum -y install MariaDB-server MariaDB-client

6. 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).

Ephesians

Answer 26

You can also simply add GROUP_CONCAT() to the nonaggregated columns.

like GROUP_CONCAT(your_column)

Desirous

Answer 27

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 server SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); Sorce link

Sholem

Answer 28

If you are using MAMP on Mac, any change to the variable in MySQL will be erased when you restart MAMP. To make sure the change is permanent do the following:

Stop the MAMP Servers that are running.

Use a text editor to create a file named my.cnf and save it to the /Applications/MAMP/conf folder. Put the file at the root of the folder (which seems a bit odd as it contains tons of apache folders and all but that's ok). Add the following lines into 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

Save the file and close your editor. Start the MAMP servers.

This will make the change permanent.

Katusha

Answer 29

if you don't want to disable ONLY_FULL_GROUP_BY mode you just have to specify every columns you want to select in GROUP BY clause too.

Don't use wildcard (*) and make sure your columns are relevant for your needs

Institutor

Answer 30

Sometimes you just need to check whether your query has the right order and the way of order.

GROUP BY pp.id_product
FIELD(pp.id_product, 1600, 1870, 2300 );

This won't run until you add the direction (DESC or ASC) of the ordering to the query.

GROUP BY pp.id_product
FIELD(pp.id_product, 1600, 1870, 2300 ) DESC;

This will do the trick as well without messing with the session and global settings of the server.

Cheek

Answer 31

In my case I can't change the global variable of mysql. (I'm using prismadB with nextJS and rawQueryUnsafe function)

But there is another way.

select t1.field1, t1.field2, sum(t2.field3) from table1 t1 left join table2 on t1.field1=t2.field1

group by t1.field1 --> this fails

group by t1.field1, t1.field2 --> this works

you will need to add all the required fields to the group by

Tested on NextJs 14.0.4

With PrismadB 5.11.0

Mysql server 8.1.0

Corri

Answer 32

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.

Phyfe

Answer 33

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

Amundsen

This is not a solution, it's is a terrible suggestion. It's like throwing away the smoke alarm when it starts beeping from detecting smoke in your house. – Hogback