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"

    XMLWordPrintable

    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 Oleksii Zagorskyi
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: