1.3.6 SQL error, server dies [OpenBSD 4.1, PostgreSQL 8.2.3]?

[schneck]

Hi,

when starting the zabbix server, I get:

268:20070503:174206 Starting zabbix_server. ZABBIX 1.3.6.
268:20070503:174206 **** Enabled features ****
268:20070503:174206 SNMP monitoring: YES
268:20070503:174206 WEB monitoring: NO
268:20070503:174206 Jabber notifications: NO
268:20070503:174206 **************************
268:20070503:174207 server #0 started [Watchdog]
10460:20070503:174207 server #7 started [Trapper]
17896:20070503:174207 server #8 started [Trapper]
13600:20070503:174207 server #9 started [Trapper]
26155:20070503:174207 server #10 started [Trapper]
14470:20070503:174207 server #11 started [Trapper]
15808:20070503:174207 server #12 started [ICMP pinger]
29093:20070503:174207 server #13 started [Alerter]
24151:20070503:174207 server #14 started [Housekeeper]
18140:20070503:174207 server #15 started [Timer]
14309:20070503:174207 server #17 started [Node watcher. Node ID:0]
12034:20070503:174207 server #18 started [Discoverer]
14669:20070503:174207 server #1 started [Poller. SNMP:ON]
16791:20070503:174207 server #4 started [Poller. SNMP:ON]
19166:20070503:174207 server #6 started [Poller. SNMP:ON]
24151:20070503:174207 Executing housekeeper
9529:20070503:174207 server #16 started [Poller for unreachable hosts. SNMP:ON]
18731:20070503:174207 server #2 started [Poller. SNMP:ON]
23634:20070503:174207 server #3 started [Poller. SNMP:ON]
27740:20070503:174207 server #5 started [Poller. SNMP:ON]
9529:20070503:174208 Query::select h.hostid,i.itemid from hosts h,items i where mod(h.hostid,1)=0 and i.nextcheck<=1178206928 and i.status in (0) and i.type not in (2,7,9) and h.status=0 and h.disable_until<=1178206928 and h.errors_from!=0 and h.hostid=i.hostid and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid>=100000000000000*0 and h.hostid<=(100000000000000*0+99999999999999) group by h.hostid order by i.nextcheck
9529:20070503:174208 Query failed:PGRES_FATAL_ERROR:ERROR: column "i.itemid" must appear in the GROUP BY clause or be used in an aggregate function

268:20070503:174208 One child process died. Exiting ...
268:20070503:174210 ZABBIX Server stopped

I'm not that well versed in SQL, but I think GROUPing requires something like count($) or similar, doesn't it?

\Bernhard.

PS: 1.3.6 compile works well on OpenBSD-4.1 now.

[Alexei]

Thanks for reporting this!

[Alexei]

The issue was fixed.

[Markus]

Sorry Alexei, the problem is still there. Now the 'order by i.nextcheck' clause is causing the same problem:

select h.hostid,min(i.itemid) from hosts h,items i where mod(h.hostid,1)=0 and i.nextcheck<=1178363670 and i.status in (0) and i.type not in (2,7,9) and h.status=0 and h.disable_until<=1178363670 and h.errors_from!=0 and h.hostid=i.hostid and i.key_ not in ('status','icmpping','icmppingsec','zabbix[log]') and h.hostid>=100000000000000*0 and h.hostid<=(100000000000000*0+99999999999999) group by h.hostid order by i.nextcheck
5229:20070505:211430 Query failed:PGRES_FATAL_ERROR:ERROR: column "i.nextcheck" must appear in the GROUP BY clause or be used in an aggregate function

Does the result have to be in sorted order?

Markus

[Alexei]

I would prefer to have it sorted, however it is not required. Remove 'oder by...' to make it work. Fixed again.