MySQL : SQL Error: 1140, SQLState: 42000

Question

There are two databases are configured in my project, the one is running on my local machine and other is running on Remote server. I don't have any issue accessing the remote DB server but getting the following error while accessing the local MySQL DB.

 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1140, SQLState: 42000
 ERROR [org.hibernate.util.JDBCExceptionReporter] Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

I saw the MySQL documentation but couldn't fully understand the root cause. what i think, as i can fetch the same data from remote DB then java code and query is correct.

Answer 1

The following query is invalid with ONLY_FULL_GROUP_BY enabled. The first is invalid because name in the select list is not named in the GROUP BY clause

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)

with no GROUP columns is illegal if there is no GROUP BY clause

You can re-write query to work fine as

mysql> SELECT name, MAX(age) FROM t GROUP BY name;

or

Turn off the ONLY_FULL_GROUP_BY and the error should disappear.

Links may help you

1. You can search MySQL errors here
2. group-by-functions
3. similar post on stackoverflow.com
4. another forum where similar problem discussed
5. server-sql-mode

UPDATE

This is answer to your comment.

But i would like to know more about disabling the Group BY mode in mysql db.  

Server SQL Modes

• The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. This capability enables each application to tailor the server's operating mode to its own requirements.

• To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.

• To change the SQL mode at runtime, use a

  SET [GLOBAL|SESSION] sql_mode='modes'
  statement to set the sql_mode system variable. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

• To determine the current global or session sql_mode value, use the following statements:

  SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

• You can refer sql_mode table

---

I followed the manuals, Added the ONLY_FULL_GROUP_BY in sql-mode
but no difference.  

This happens because of MySQL version. What is MySQL version of your local computer?

How to check MySQL version?

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 5.5.28    |
+-----------+
1 row in set (0.00 sec)    

For testing sql_mode ONLY_FULL_GROUP_BY, I created table patient with two columns id, name and inserted records. Remember sql_mode ONLY_FULL_GROUP_BY is not default set, you need to set if you want.

1)MySQL version 5.0.45-community-nt

SELECT name, MAX(id) FROM patient;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause  

It failed, there was no point in setting the sql_mode to ONLY_FULL_GROUP_BY as it won't allow nonaggregated columns that are not named in the GROUP BY clause.

2)MySQL version 5.1.40-community

mysql> SELECT name, MAX(id) from patient;
+----------+--------+
| MAX(id)  | name   |
+----------+--------+
|       33 | aniket |
+----------+--------+
1 row in set (0.03 sec)  

Then after setting sql_mode ONLY_FULL_GROUP_BY

mysql> set sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, MAX(id) from patient;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause  

3)MySQL version 5.5.28

mysql> SELECT name, MAX(id) from patient;
+----------+--------+
| MAX(id)  | name   |
+----------+--------+
|       33 | aniket |
+----------+--------+
1 row in set (0.03 sec)  

Then after setting sql_mode ONLY_FULL_GROUP_BY

mysql> set sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT name, MAX(id) from patient;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause  

Conclusion

As you can see query failed on version 5.0.45, and succeed on/after 5.1.40 and 5.5.28 Before MySQL version 5.1.10(not sure) query without GROUP BY fails irrespective of sql_mode ONLY_FULL_GROUP_BY set or not.

Some interesting bugs and sql_mode faq link

1. ONLY_FULL_GROUP_BY sql mode is overly restrictive
2. sql-mode: only full group by mode not working
3. MySQL 5.0 FAQ: Server SQL Mode

Answer 2

You need to include a GROUP BY clause (you didn't post your query, but I'm guessing you are missing one based on the error). The reason this is only an issue on your localhost but not the remote is the remote server probably has the ONLY_FULL_GROUP_BY turned off but your local db has it on.

Ideally you should specify a GROUP BY clause but you could also just toggle your local db's ONLY_FULL_GROUP_BY setting.