Welcome, Guest. Please login or register.

Author Topic: Error after discovery "DBD::mysql::st execute failed: Mixing of GROUP columns"  (Read 4350 times)

tristanbob

  • Full Member
  • ***
  • Posts: 153
    • View Profile
New install of Nedi 1.0.6-RC1.  Initialized the database, and tried to discover a single device (Cisco 3750).  Check out the errors at the bottom:

root@nedi:/opt/nedi-new# ./nedi.pl -a 192.168.20.1

Manual-Discovery (1.0.6 rc1) with 1 seed at Wed Apr 13 17:10:39 2011
-------------------------------------------------------------------------------
Device                          Status                          Todo/Done-Time
===============================================================================
192.168.20.1    te-212-gw1   v25St i83  Jv p0/0    Fp80Fp978Fp79Fp985Fp180Fp972Fp3Fp192Fp980f266   0/1-12s
===============================================================================
DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause at ./inc/libdb-msq.pm line 1139.
DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause at ./inc/libdb-msq.pm line 1139.

root@nedi:/opt/nedi-new#
Please visit "Other"->"Invoices" on your NeDi installation to make an annual contribution and support Nedi!

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2728
    • View Profile
    • NeDi
What's the mysql version?
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

tristanbob

  • Full Member
  • ***
  • Posts: 153
    • View Profile
Server version: 5.0.51a-3ubuntu5.5
Please visit "Other"->"Invoices" on your NeDi installation to make an annual contribution and support Nedi!

bugfree

  • Newbie
  • *
  • Posts: 3
    • View Profile
same error for me to

mysql version 5.0.24a 

DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause at /var/nedi/inc/libdb-msq.pm line 1139.
DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause at /var/nedi/inc/libdb-msq.pm line 1139.

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2728
    • View Profile
    • NeDi
The old one working:
select count(*),lastchk from monitoring where status = 0 group by lastchk order by lastchk desc

The new query in question is ( $misc::latw replaced by 100):
select count(status),lastseen from monitoring WHERE checks != '' AND latency < 100 AND status = 0

Play with it (easy in other-export), to see what exactly breaks on mysql 5.0. Add "group by lastseen" to verify it's what it really wants. Problem is, I simplified php queries as well  :-\

A related topic:
http://bugs.mysql.com/bug.php?id=29421
« Last Edit: April 16, 2011, 03:07:17 AM by rickli »
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

tristanbob

  • Full Member
  • ***
  • Posts: 153
    • View Profile
Remo,

If I add "group by lastseen" the query completes correctly.  Is this something you can change in the next RC?

What file are these SQL statements in?

Thanks,

Tristan
Please visit "Other"->"Invoices" on your NeDi installation to make an annual contribution and support Nedi!

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2728
    • View Profile
    • NeDi
I found something:

http://dev.mysql.com/doc/refman/5.1/en/counting-rows.html

    If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs:

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

    mysql> SELECT owner, COUNT(*) FROM pet;
    ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
    with no GROUP columns is illegal if there is no GROUP BY clause

    If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single group, but the value selected for each named column is indeterminate. The server is free to select the value from any row:

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

    mysql> SELECT owner, COUNT(*) FROM pet;
    +--------+----------+
    | owner  | COUNT(*) |
    +--------+----------+
    | Harold |        8 |
    +--------+----------+
    1 row in set (0.00 sec)

Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

coolcat

  • Guest
Has there been a fix for this??

I have the same issue.
Running:
RH EL5
MySQL 5.0.45-7
Nedi 1.0.7-338

Have also tried with Nedi 1.0.6 without any improvment.


/Pierre
« Last Edit: January 10, 2012, 02:02:32 PM by coolcat »

pc_sg

  • Guest
Hi Remo,

could you show us which line modify to corrent the 'ONLY_FULL_GROUP_BY' problem in the failing queries?

I presume that my TopRRD graph (still) missing depends on it:
Code: [Select]
TopRRD       ------------------------------------------------------------------
DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause at /var/nedi/inc/libdb-msq.pm line 1183.
DBD::mysql::st execute failed: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause at /var/nedi/inc/libdb-msq.pm line 1183.


TIA again

Paolo