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

Long time SQL queries in MySQL 8.0

    XMLWordPrintable

Details

    • Incident report
    • Resolution: Duplicate
    • Trivial
    • None
    • 4.0.3
    • Frontend (F)
    • None

    Description

      After update MySQL version from 5.7 to 8.0 I noted a lot of long time queries in mysql which run up to  one hour (may be more)

      Queries are like this:

      SELECT DISTINCT t.triggerid 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='20' 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 '10210' AND '10214' OR i.hostid BETWEEN '10296' AND '10323' OR i.hostid BETWEEN '10453' AND '10458' OR i.hostid BETWEEN '10461' AND '10508' OR i.hostid BETWEEN '10510' AND '10531' OR i.hostid BETWEEN '10533' AND '10546' OR i.hostid BETWEEN '10549' AND '10564' OR i.hostid BETWEEN '10573' AND '10578' OR i.hostid BETWEEN '10582' AND '10590' OR i.hostid BETWEEN '10598' AND '10604' OR i.hostid BETWEEN '10612' AND '10630' OR i.hostid BETWEEN '10633' AND '10638' OR i.hostid BETWEEN '10640' AND '10646' OR i.hostid BETWEEN '10656' AND '10675' OR i.hostid BETWEEN '10678' AND '10682' OR i.hostid BETWEEN '10698' AND '10702' OR i.hostid BETWEEN '10704' AND '10719' OR i.hostid BETWEEN '10721' AND '10729' OR i.hostid BETWEEN '10741' AND '10748' OR i.hostid BETWEEN '10756' AND '10769' OR i.hostid BETWEEN '10812' AND '10817' OR i.hostid IN ('10181','10208','10219','10220','10221','10246','10257','10261','10262','10263','10292','10293','10294','10325','10326','10334','10366','10367','10368','10380','10381','10394','10405','10406','10569','10570','10571','10606','10607','10608','10609','10648','10649','10650','10651','10684','10685','10696','10732','10773','10774','10781','10785','10787','10788','10789','10793','10794','10795','10797','10798','10799','10800','10803','10833','10861','10862')) 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')

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              localnnov Andrey Golodyaev
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: