[ZBX-14727] Overview page, even with "none" first drop-down entry, generates very huge SQL, same as for "all" Created: 2018 Aug 14 Updated: 2024 Apr 10 Resolved: 2018 Oct 08 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Frontend (F) |
Affects Version/s: | 3.0.20, 3.4.12, 4.0.0alpha9 |
Fix Version/s: | 3.0.23rc1, 3.4.15rc1, 4.0.1rc1, 4.2.0alpha1, 4.2 (plan) |
Type: | Problem report | Priority: | Critical |
Reporter: | Oleksii Zagorskyi | Assignee: | Ivo Kurzemnieks |
Resolution: | Fixed | Votes: | 0 |
Labels: | dropdown, overview, performance, sql | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified |
Issue Links: |
|
||||
Team: | Team B | ||||
Team: | Team B | ||||
Sprint: | Sprint 40, Sprint 41, Sprint 42, Sprint 43, Sprint 44 | ||||
Story Points: | 0.5 |
Description |
This SQL in generated even with "none" first drop-down entry, the same as for "all" mode. SELECT DISTINCT t.triggerid,t.description,t.expression,t.priority,t.url,t.value,t.lastchange,t.flags FROM triggers t,functions f,items i WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid IN ('11','13') WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<'2' OR MIN(permission) IS NULL OR MIN(permission)=0) AND i.hostid IN ('10084','10106','10123','10126','10133','10138','10160','10161','10172','10173') AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND NOT EXISTS (SELECT NULL FROM functions f,items i,hosts h WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=h.hostid AND (i.status<>0 OR h.status<>0)) AND t.status=0 AND t.flags IN ('0','4') AND ((t.value=1) OR ((t.value=0) AND (t.lastchange>1534258697))) ORDER BY t.description Looks like all the hosts are selected. And in real production it leads to so huge SQLs like: SELECT DISTINCT t.triggerid,t.description,t.expression,t.priority,t.url,t.value,t.lastchange,t.flags FROM triggers t,functions f,items i WHERE NOT EXISTS (SELECT NULL FROM functions f,items i,hosts_groups hgg LEFT JOIN rights r ON r.id=hgg.groupid AND r.groupid='27' WHERE t.triggerid=f.triggerid AND f.itemid=i.itemid AND i.hostid=hgg.hostid GROUP BY i.hostid HAVING MAX(permission)<'2' OR MIN(permission) IS NULL OR MIN(permission)=0) AND (i.hostid BETWEEN '30821' AND '30826' OR i.hostid BETWEEN '30832' AND '30839' OR i.hostid BETWEEN '30890' AND '30894' OR i.hostid BETWEEN '30953' AND '30958' OR i.hostid BETWEEN '30981' AND '30985' OR i.hostid BETWEEN '31021' AND '31026' OR i.hostid BETWEEN '31093' AND '31098' OR i.hostid BETWEEN '31148' AND '31154' OR i.hostid BETWEEN '31157' AND '31161' OR i.hostid BETWEEN '31195' AND '31199' OR i.hostid BETWEEN '31342' AND '31346' OR i.hostid BETWEEN '31535' AND '31546' OR i.hostid BETWEEN '31564' AND '31570' OR i.hostid BETWEEN '31575' AND '31580' OR i.hostid BETWEEN '31583' AND '31589' OR i.hostid BETWEEN '31612' AND '31616' OR i.hostid BETWEEN '31618' AND '31623' OR i.hostid BETWEEN '31673' AND '31677' OR i.hostid BETWEEN '31774' AND '31779' OR i.hostid BETWEEN '31785' AND '31793' OR i.hostid BETWEEN '31797' AND '31806' OR i.hostid BETWEEN '31845' AND '31850' OR i.hostid BETWEEN '31886' AND '31890' OR i.hostid BETWEEN '31895' AND '31900' OR i.hostid BETWEEN '32086' AND '32090' OR i.hostid BETWEEN '32117' AND '32121' OR i.hostid BETWEEN '32229' AND '32238' OR i.hostid BETWEEN '32250' AND '32255' OR i.hostid BETWEEN '32283' AND '32288' OR i.hostid BETWEEN '32296' AND '32301' OR i.hostid BETWEEN '32310' AND '32316' OR i.hostid BETWEEN '32362' AND '32366' OR i.hostid BETWEEN '32424' AND '32430' OR i.hostid BETWEEN '32492' AND '32496' OR i.hostid BETWEEN '32589' AND '32594' OR i.hostid BETWEEN '32599' AND '32603' OR i.hostid BETWEEN '32704' AND '32709' OR i.hostid BETWEEN '32786' AND '32790' OR i.hostid BETWEEN '32889' AND '32896' OR i.hostid BETWEEN '32953' AND '32957' OR i.hostid BETWEEN '32983' AND '32987' OR i.hostid BETWEEN '33003' AND '33008' OR i.hostid BETWEEN '33092' AND '33098' OR i.hostid BETWEEN '33126' AND '33130' OR i.hostid BETWEEN '33233' AND '33237' OR i.hostid BETWEEN '33358' AND '33362' OR i.hostid BETWEEN '33417' AND '33421' OR i.hostid BETWEEN '33533' AND '33538' OR i.hostid BETWEEN '33549' AND '33553' OR i.hostid BETWEEN '33609' AND '33613' OR i.hostid BETWEEN '33667' AND '33671' OR i.hostid BETWEEN '33784' AND '33789' OR i.hostid BETWEEN '33804' AND '33808' OR i.hostid BETWEEN '33826' AND '33832' OR i.hostid BETWEEN '33929' AND '33933' OR i.hostid BETWEEN '33954' AND '33958' OR i.hostid BETWEEN '33968' AND '33972' OR i.hostid BETWEEN '34010' AND '34014' OR i.hostid BETWEEN '34144' AND '34148' OR i.hostid BETWEEN '34171' AND '34177' OR i.hostid BETWEEN '34204' AND '34211' OR i.hostid BETWEEN '34221' AND '34225' OR i.hostid BETWEEN '34247' AND '34251' OR i.hostid BETWEEN '34266' AND '34271' OR i.hostid BETWEEN '34328' AND '34333' OR i.hostid BETWEEN '34371' AND '34376' OR i.hostid BETWEEN '34472' AND '34476' OR i.hostid BETWEEN '34510' AND '34514' OR i.hostid BETWEEN '34549' AND '34553' OR i.hostid BETWEEN '34604' AND '34611' OR i.hostid BETWEEN '34650' AND '34655' OR i.hostid BETWEEN '34764' AND '34771' OR i.hostid BETWEEN '34819' AND '34823' OR i.hostid BETWEEN '34868' AND '34872' OR i.hostid BETWEEN '34875' AND '34881' OR i.hostid BETWEEN '34923' AND '34929' OR i.hostid BETWEEN '34950' AND '34955' OR i.hostid BETWEEN '35016' AND '35020' OR i.hostid BETWEEN '35022' AND '35026' OR i.hostid BETWEEN '35153' AND '35158' OR i.hostid BETWEEN '35205' AND '35209' OR i.hostid BETWEEN '35218' AND '35225' OR i.hostid BETWEEN '35267' AND '35271' OR i.hostid BETWEEN '35287' AND '35291' OR i.hostid BETWEEN '35301' AND '35305' OR i.hostid BETWEEN '35351' AND '35355' OR i.hostid BETWEEN '35377' AND '35381' OR i.hostid BETWEEN '35390' AND '35394' OR i.hostid BETWEEN '35405' AND '35413' OR i.hostid BETWEEN '35421' AND '35425' OR i.hostid BETWEEN '35437' AND '35443' OR i.hostid BETWEEN '35535' AND '35543' OR i.hostid BETWEEN '35554' AND '35558' OR i.hostid BETWEEN '35569' AND '35574' OR i.hostid BETWEEN '35638' AND '35643' OR i.hostid BETWEEN '35979' AND '35985' OR i.hostid BETWEEN '36035' AND '36041' OR i.hostid BETWEEN '36043' AND '36047' OR i.hostid BETWEEN '36112' AND '36116' OR i.hostid BETWEEN '36135' AND '36139' OR i.hostid BETWEEN '36232' AND '36236' OR i.hostid BETWEEN '36300' AND '36307' OR i.hostid BETWEEN '36323' AND '36327' OR i.hostid BETWEEN '36331' AND '36336' OR i.hostid BETWEEN '36375' AND '36379' OR i.hostid BETWEEN '36405' AND '36409' OR i.hostid BETWEEN '36447' AND '36451' OR i.hostid BETWEEN '36466' AND '36471' OR i.hostid BETWEEN '36498' AND '36503' OR i.hostid BETWEEN '36570' AND '36574' OR i.hostid BETWEEN '36618' AND '36626' OR i.hostid BETWEEN '36630' AND '36638' OR i.hostid BETWEEN '36662' AND '36666' OR i.hostid BETWEEN '36695' AND '36699' OR i.hostid BETWEEN '36803' AND '36808' OR i.hostid BETWEEN '36887' AND '36892' OR i.hostid BETWEEN '36979' AND '36983' OR i.hostid BETWEEN '36998' AND '37002' OR i.hostid BETWEEN '37053' AND '37057' OR i.hostid BETWEEN '37121' AND '37125' OR i.hostid BETWEEN '37205' AND '37209' OR i.hostid BETWEEN '37277' AND '37283' OR i.hostid BETWEEN '37332' AND '37336' OR i.hostid BETWEEN '37375' AND '37380' OR i.hostid BETWEEN '37486' AND '37491' OR i.hostid BETWEEN '37507' AND '37511' OR i.hostid BETWEEN '37518' AND '37526' OR i.hostid BETWEEN '37533' AND '37537' OR i.hostid BETWEEN '37539' AND '37543' OR i.hostid BETWEEN '37549' AND '37555' OR i.hostid BETWEEN '37565' AND '37569' OR i.hostid BETWEEN '37619' AND '37625' OR i.hostid BETWEEN '37684' AND '37689' OR i.hostid BETWEEN '37691' AND '37695' OR i.hostid BETWEEN '37754' AND '37761' OR i.hostid BETWEEN '37767' AND '37772' OR i.hostid BETWEEN '37788' AND '37795' OR i.hostid BETWEEN '37864' AND '37870' OR i.hostid BETWEEN '37976' AND '37980' OR i.hostid BETWEEN '37983' AND '37988' OR i.hostid BETWEEN '38019' AND '38023' OR i.hostid BETWEEN '38077' AND '38081' OR i.hostid BETWEEN '38118' AND '38127' OR i.hostid BETWEEN '38159' AND '38165' OR i.hostid BETWEEN '38259' AND '38264' OR i.hostid BETWEEN '38407' AND '38411' OR i.hostid BETWEEN '38509' AND '38514' OR i.hostid BETWEEN '38580' AND '38589' OR i.hostid BETWEEN '38638' AND '38642' OR i.hostid BETWEEN '38646' AND '38650' OR i.hostid BETWEEN '38669' AND '38674' OR i.hostid BETWEEN '38676' AND '38684' OR i.hostid BETWEEN '38686' AND '38693' OR i.hostid BETWEEN '38719' AND '38726' OR i.hostid BETWEEN '38870' AND '38875' OR i.hostid BETWEEN '38957' AND '38961' OR i.hostid BETWEEN '38998' AND '39003' OR i.hostid BETWEEN '39013' AND '39019' OR i.hostid BETWEEN '39031' AND '39037' OR i.hostid BETWEEN '39057' AND '39061' ................. which we were not able to copy as complete in any way. I'm sure that the SQL should not be generated when the mode is "none". "Zabbix Admin" user level is used, but basically the same applies to Super Admin. |
Comments |
Comment by Ivo Kurzemnieks [ 2018 Aug 29 ] |
RESOLVED in svn://svn.zabbix.com/branches/dev/ZBX-14727 |
Comment by Ivo Kurzemnieks [ 2018 Oct 03 ] |
Fixed in:
|