Bug #61666 #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns
Submitted: 28 Jun 2011 8:44 Modified: 28 Jun 2011 9:01
Reporter: Devang Modi Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql Ver 14.12 Distrib 5.0.45 OS:Linux (Red Hat Enterprise Linux Server release 5.3 (Tikanga))
Assigned to: CPU Architecture:Any
Tags: #1140, GROUP, Mixing

[28 Jun 2011 8:44] Devang Modi
Description:
Dear Sir;
I do not know whether this is called bug or limitation.
See
We have two servers production and test 

On production we are using MySQL with below specifications
RHEL: Red Hat Enterprise Linux Server release 5.3 (Tikanga)
MySQL : mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

On test server we are using MySQL with below specifications
RHEL: Red Hat Enterprise Linux Server release 5.6 (Tikanga)
MySQL: mysql  Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1 

On production server when i am issuing below sql query it is giving me an error
Query: Select type, count(*) from location
Error: #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns 

On test server the same query is not giving us error.

In the same line when i referred MySQL documentation , I was told that we need to set SQL_MODE parameter on test server to generate same error on both servers so I set it.

SQL_MODE=ONLY_FULL_GROUP_BY

But exactly after this an another query with Having clause start giving error on test server.
This is also identified in the document.
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by

We never set such parameter on Production server.

Can you please share with me why such difference is their.
What made such difference between two MySQL version.
How we can solve this issue?
 

How to repeat:
Kindly refer description
[28 Jun 2011 8:48] Devang Modi
Dear Sir;

Sorry I forgot to mention second error that we faced after adding SQL_MODE parameter.

Error is 
"non-grouping field '' is used in HAVING clause"

Devang
[28 Jun 2011 9:01] Valeriy Kravchuk
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.