[ZBX-5837] MySQL sql-mode Value Breaks The Graphs Configuration Page Created: 2012 Nov 11  Updated: 2017 May 30  Resolved: 2012 Nov 28

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F), Server (S)
Affects Version/s: 2.0.2
Fix Version/s: 2.0.5rc1, 2.1.0

Type: Incident report Priority: Major
Reporter: Jack Williams Tire Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: mode, mysql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

RHEL 6 x68_64, MySQL 5.5.27

Attachments: PNG File noGraphs.png    

 Description   

I couldn't create graphs for hosts when configuring them. The graphs page (/graphs.php) didn't show any graphs for hosts that have them and the button to create a new graph was disabled. In addition, the Groups and Hosts select boxes didn't have any options in them (see attached screen shot). After a little research I realized that the sql mode MYSQL40 cause the query for hosts to fail.

In api/classes/CHostGroup.php on line 236 I changed ...

$sqlParts['where'][] = 'NOT h.status IN ('.HOST_STATUS_PROXY_ACTIVE.','.HOST_STATUS_PROXY_PASSIVE.')';

... to ...

$sqlParts['where'][] = 'h.status NOT IN ('.HOST_STATUS_PROXY_ACTIVE.','.HOST_STATUS_PROXY_PASSIVE.')';

... and this fixed the graphs page for me. I verified that the page works without my fix if the MySQL server is running in TRADITIONAL mode. My fix works in both modes and that's how I'm running it now.

Thanks,
Tony

 Comments   
Comment by richlv [ 2012 Nov 11 ]

hmm. given that mysql 5.0 is the lowest supported version by zabbix 2.0, not sure we'd want to play with supporting old compatibility modes...

Comment by Volker Fröhlich [ 2012 Nov 11 ]

Looking at a draft version of the ISO SQL 2003 standard, I wonder if MySQL gives a result, if you change

NOT h.status IN (5, 6)

to

NOT (h.status IN (5, 6))

The document says:

"""
RVC NOT IN IPV
is equivalent to
NOT ( RVC IN IPV )
"""

RVC: row value expression
IPV: in predicate value

Comment by Eduards Samersovs (Inactive) [ 2012 Nov 28 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-5837

Comment by Toms (Inactive) [ 2012 Nov 29 ]

TESTED

Comment by Eduards Samersovs (Inactive) [ 2012 Dec 10 ]

Fixed in versions pre-2.1.0 (beta) r32024, pre-2.0.5rc1 r32023

Generated at Tue Jan 07 18:44:53 EET 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.