ONLY_FULL_GROUP_BY not set but still have Error 11

Question

I am finalizing the implementation of my website but now I have a problem that occurs online that I have not locally.

I get this error:

failed: Mixing of GROUP columns (MIN (), MAX (), COUNT (), ...) with no GROUP columns is illegal if there is no GROUP BY clause

result of a SQL query

I searched in lot of forums on the net, most users advise to change the query that I can not / do not want, or they say it was probably in the sql-mode: enabled ONLY_FULL_GROUP_BY of the server

My sql-mode is empty on my server online (I can see with the query select @@sql_mode;) More to be sure, I put sql_mode='' in my.cnf.

But the problem remains.

Is this due to my version of mysql 5.0.44 on my server online and locally 5.1.32 (which I do not have this bug ...)?

Answer 1

Yes. You are right. This happens because of MySQL version.
Check my answer here

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, 5.5.28 and 5.1.32 (as you mentioned in question). 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