Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-14727

Overview page, even with "none" first drop-down entry, generates very huge SQL, same as for "all"

    Details

    • 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.
      The SQL kills our DB performance.

      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.
      Overview Type -> "Triggers".

        Attachments

          Activity

            People

            • Assignee:
              iivs Ivo Kurzemnieks
              Reporter:
              zalex_ua Oleksiy Zagorskyi
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: