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.
解决方案
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
UPDATE
This is answer to your comment.
But i would like to know more about disabling the Group BY mode in mysql db.
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;
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