Details

    • Type: Problem report
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.2.2
    • Component/s: API (A)
    • Labels:
    • Environment:
    • Team:
      Team B
    • Sprint:
      Sprint 17
    • Story Points:
      1

      Description

      When I'm tried to remove template through the web interface I see slow query in mysql process list like:

      SELECT DISTINCT t.triggerid,t.description,t.flags,t.expression,h.name as host FROM  triggers t,hosts h WHERE  EXISTS (SELECT ff.triggerid FROM functions ff,items ii WHERE ff.triggerid=t.templateid AND ii.itemid=ff.itemid AND ii.hostid='13856') AND t.flags IN ('0','1')
      

      The process is handled about 20 min and tmp tables are growing so fast up to 10 Gb.

      root@db:/ramdisk# ll
      -rw-rw---- 1 mysql mysql  9837510656 	??? 29 11:48 #sql_985_0.MAD
      -rw-rw---- 1 mysql mysql   902660096 	??? 29 11:48 #sql_985_0.MAI
      

      When I am killing this process I saw Error message like that in my dashboard.

      mysqli_query(): MySQL server has gone away [templates.php:393 ? CAPIObject->delete() ? CAPIObject->__call() ? czbxrpc::call() ? czbxrpc::callAPI() ? call_user_func() ? CTemplate->delete() ? CHostGeneral->unlink() ? DBselect() ? mysqli_query() in /nmc/httpd/zabbix/include/db.inc.php:379]
      mysqli_query(): Error reading result set's header [templates.php:393 ? CAPIObject->delete() ? CAPIObject->__call() ? czbxrpc::call() ? czbxrpc::callAPI() ? call_user_func() ? CTemplate->delete() ? CHostGeneral->unlink() ? DBselect() ? mysqli_query() in /nmc/httpd/zabbix/include/db.inc.php:379]
      Error in query [SELECT DISTINCT t.triggerid,t.description,t.flags,t.expression,h.name as host FROM  triggers t,hosts h WHERE  EXISTS (SELECT ff.triggerid FROM functions ff,items ii WHERE ff.triggerid=t.templateid AND ii.itemid=ff.itemid AND ii.hostid='13856') AND t.flags IN ('0','1')] [MySQL server has gone away]
      Error in query [SELECT DISTINCT i1.itemid,i1.flags,i1.name,i1.hostid,h.name as host FROM  items i1,items i2,hosts h WHERE  i2.itemid=i1.templateid AND i2.hostid='13856' AND i1.flags IN ('0','1') AND h.hostid=i1.hostid] [MySQL server has gone away]
      Error in query [SELECT DISTINCT g.graphid,g.name,g.flags,h.name as host FROM  graphs g,hosts h WHERE  EXISTS (SELECT ggi.graphid FROM graphs_items ggi,items ii WHERE ggi.graphid=g.templateid AND ii.itemid=ggi.itemid AND ii.hostid='13856') AND g.flags IN ('0','1')] [MySQL server has gone away]
      Error in query [SELECT DISTINCT ht1.httptestid,ht1.name,h.name as host FROM httptest ht1 INNER JOIN httptest ht2 ON ht2.httptestid=ht1.templateid INNER JOIN hosts h ON h.hostid=ht1.hostid WHERE ht2.hostid='13856'] [MySQL server has gone away]
      Error in query [SELECT at.application_templateid,at.applicationid,h.name,h.host,h.hostid FROM applications a1,application_template at,applications a2,hosts h WHERE a1.applicationid=at.applicationid AND at.templateid=a2.applicationid AND a2.hostid='13856' AND a1.hostid=h.hostid] [MySQL server has gone away]
      Error in query [DELETE FROM hosts_templates WHERE templateid='13856'] [MySQL server has gone away]
      SQL statement execution has failed "DELETE FROM hosts_templates WHERE templateid='13856'"
      Error in query [ROLLBACK] [MySQL server has gone away]
      

      Finally I can't remove template with ID=13856, and most of all I can't remove any template from the list of templates because query execution time exceeds reasonable limits.

      P.S.

      MariaDB [zabbix]> select count(*) from triggers;
      329966
      
      MariaDB [zabbix]> select count(*) from hosts;
      5506 
      

        Attachments

          Activity

            People

            • Assignee:
              sasha Alexander Vladishev
              Reporter:
              smith Sergey
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: