• Register
0 votes
61 views

This is incompatible with sql_mode=only_full_group_by

I installed a new version of SQL, and I received this error message. How can I fix it?
1.9k points

Please log in or register to answer this question.

1 Answer

0 votes

This is incompatible with sql_mode=only_full_group_by


SQL can work using many modes that affect the queries' syntax and validation checks. Based on the configuration of the variable sql_mode, a query can be valid and executes or can have a validation error and cannot be executed. The old versions of SQL accustomed its users to write queries that were not semantically correct. The old versions were designed to work in a "forgiving mode." Users had the chance to write any queries regardless of SQL standard compliances or semantic rules. The bad habit was corrected by introducing the sql_mode to instruct the server to work in a restrictive way to validate the query.

When running a query having “GROUP BY” against a database in MySQL, you encountered the error “this is incompatible with sql_mode=only_full_group_by." Here is the way to fix the issue.  First, open “my.cnf" and replace "vim" with a name of your choice. You can find the config file at "etc/mysql”  directory in a standard installation.

sudo vim /etc/mysql/my.cnf

Secondly, add the lines below to the end of its content: 

[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

Now save the file and exit. Restart the SQL server by running

sudo service mysql restart

If you are a Windows user, "my.cnf" will be "my.ini" in the directory C:/xampp/mysql/bin/." After adding the lines above, restart the MySQL server.

I hope it helps.

3.4k points
edited by

Related questions

0 votes
1 answer 4 views
4 views
Problem: Is there any way around this : This is incompatible with sql_mode=only_full_group_by?
asked Jun 25 Mezbah 110k points
0 votes
1 answer 6 views
6 views
Problem: Does anyone grasp the problem : This is incompatible with sql_mode=only_full_group_by mysql?
asked Jun 23 Chi Omega 159k points
0 votes
1 answer 15 views
15 views
Problem: I've tried but haven't had any success with this: Group by clause; this is incompatible with sql_mode=only_full_group_by?
asked Jun 17 akhi 106k points
0 votes
1 answer 2 views
2 views
Problem: Incompatible with sql_mode=only_full_group_by
asked Jul 6 nila 103k points
0 votes
1 answer 7 views
7 views
Problem: Please help me … how can I solve it? Disable only_full_group_by
asked Apr 4 Ifra 43.4k points
0 votes
1 answer 6 views
6 views
Problem: ERROR 1148: The used command is not allowed with this MySQL version Thanks
asked Mar 20 ummesalma 29.1k points
0 votes
2 answers 314 views
314 views
Problem : I am trying to load my data into mysql database using below code LOAD DATA LOCAL INFILE A.txt INTO DB LINES TERMINATED BY '|'; A topic of my question is related to response I get. I understand a local data offloading is off by default and I have to enable it using following command local-infile=1 but I do not know how to execute this command.
asked Jan 28, 2020 jwilliam 3.9k points
0 votes
1 answer 11 views
11 views
Problem: Operand type clash int is incompatible with date?
asked Jun 30 Nahil 107k points
0 votes
1 answer 21 views
21 views
Problem: Operand type clash: int is incompatible with date
asked Jun 29 nila 103k points
0 votes
1 answer 15 views
15 views
Problem: Where can I find a solution for this : Operand type clash: uniqueidentifier is incompatible with int
asked Jun 22 Sawyer Parviz 158k points