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

Transactions deadlock detected during removing host via zabbix API.

    XMLWordPrintable

Details

    • Problem report
    • Status: Confirmed
    • Major
    • Resolution: Unresolved
    • 6.0.12, 6.2.6, 6.4.0beta5
    • None
    • API (A)
    • None

    Description

      Steps to reproduce:
      remove host via zabbix API (name dt-sl-a225.x.x.x)

      2023-01-04 11:39:35,319 - RemoveHost: Connected to https://zabbix.xxx.net/api_jsonrpc.php API Version 5.0.30
      2023-01-04 11:39:35,319 - pyzabbix.api: Sending: {'jsonrpc': '2.0', 'method': 'host.get', 'params': {'filter': {'host': 'dt-sl-a225.x.x.x'}}, 'id': 3, 'auth': 'deec975ad2141b28897093a1edcdd08c'}
      2023-01-04 11:39:35,361 - pyzabbix.api: Response Body: {'jsonrpc': '2.0', 'result': [{'hostid': '61375', 'proxy_hostid': '55636', 'host': 'dt-sl-a225.x.x.x, 'status': '0', 'disable_until': '0', 'error': 'Received empty response from Zabbix Agent at [10.x.y.x]. Assuming that agent dropped connection because of access permissions.', 'available': '2', 'errors_from': '0', 'lastaccess': '0', 'ipmi_authtype': '6', 'ipmi_privilege': '2', 'ipmi_username': 'ipmi', 'ipmi_password': 'xxx', 'ipmi_disable_until': '0', 'ipmi_available': '1', 'snmp_disable_until': '0', 'snmp_available': '0', 'maintenanceid': '0', 'maintenance_status': '0', 'maintenance_type': '0', 'maintenance_from': '0', 'ipmi_errors_from': '0', 'snmp_errors_from': '0', 'ipmi_error': '', 'snmp_error': '', 'jmx_disable_until': '0', 'jmx_available': '0', 'jmx_errors_from': '0', 'jmx_error': '', 'name': 'dt-sl-a225.x.x.x', 'flags': '0', 'templateid': '0', 'description': '', 'tls_connect': '1', 'tls_accept': '1', 'tls_issuer': '', 'tls_subject': '', 'tls_psk_identity': '', 'tls_psk': '', 'proxy_address': '', 'auto_compress': '1', 'inventory_mode': '-1'}], 'id': 3}
      2023-01-04 11:39:35,361 - pyzabbix.api: Sending: {'jsonrpc': '2.0', 'method': 'host.delete', 'params': ('61375',), 'id': 4, 'auth': 'deec975ad2141b28897093a1edcdd08c'}
      *Result:*
      
      2023-01-04 11:39:36,908 - pyzabbix.api: Response Body: {'jsonrpc': '2.0', 'error': {'code': -32500, 'message': 'Application error.', 'data': 'SQL statement execution has failed "DELETE FROM screens_items WHERE (resourceid IN (\'2495229\',\'2495230\',\'2495231\',\'2495232\',\'2495233\',\'2495234\',\'2495235\',\'2495236\',\'2495237\',\'2495238\',\'2495239\',\'2495240\',\'2495241\',\'2495242\',\'2495243\',\'2495244\',\'2495245\')) AND resourcetype=\'0\'"'}, 'id': 4}
      

      in mysqld.log

      2023-01-04T09:39:36.905709Z 16666922 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2023-01-04T09:39:36.905732Z 16666922 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240182034291, ACTIVE 1 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 8 lock struct(s), heap size 1136, 56 row lock(s)
      MySQL thread id 16666928, OS thread handle 47507873167104, query id 21628529564 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('2495229','2495230','2495231','2495232','2495233','2495234','2495235','2495236','2495237','2495238','2495239','2495240','2495241','2495242','2495243','2495244','2495245')) AND resourcetype='0'
      2023-01-04T09:39:36.905766Z 16666922 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240182034291 lock_mode X waiting
      2023-01-04T09:39:36.905791Z 16666922 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240182033969, ACTIVE 1 sec fetching rows
      mysql tables in use 1, locked 1
      4383 lock struct(s), heap size 450768, 432658 row lock(s), undo log entries 1220
      MySQL thread id 16666922, OS thread handle 47512274999040, query id 21628531699 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('18490570','18490571','18490572','18490573','18490574','18490575','18490576','18490577','18490578','18490579','18490580','18490581','18490582','18490583','18490584','18490585','18490586','18490587','18490588','18490589','18490590','18490591','18490592','18490593','18490594','18490595','18490597','18490598','18490599','18490600','18490601','18490602','18490603','18490604','18490605','18490606','18490607','18490608','18490609','18490610','18490611','18490612','18490613','18490614','18490615','18490616','18490617','18490618','18490619','18490620','18490621','18490622','18490623','18490624','18490625','18490626','18490627','18490628','18490629','18490630','18490631','18490632','18490633','18490634','18490635','18490636','18490637','18490638','18490639','18490640','18490641','18490642','18490643','18490644','18490645','18490646','1849
      2023-01-04T09:39:36.905824Z 16666922 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240182033969 lock_mode X
      2023-01-04T09:39:36.905841Z 16666922 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 566 n bits 192 index PRIMARY of table `zabbix`.`graphs` trx id 240182033969 lock_mode X waiting
      2023-01-04T09:39:36.905860Z 16666922 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      
      [[email protected] mysql]# grep deadlock *
      mysqld.log:2023-01-04T09:39:36.905709Z 16666922 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20221231:2022-12-30T09:59:41.922354Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20221231:2022-12-30T09:59:41.933497Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20221231:2022-12-30T09:59:42.153801Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20221231:2022-12-30T09:59:44.199766Z 16252801 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20230104:2023-01-03T04:14:16.391833Z 16507723 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20230104:2023-01-03T05:14:25.482265Z 16511975 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20230104:2023-01-03T05:14:25.483232Z 16511975 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      mysqld.log-20230104:2023-01-03T05:14:25.483749Z 16511975 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      

      So we have repeatable steps for it. But then we try to remove it one more time - it will be allowed. So only sometimes this deadlocks are exist.

      mysql]# grep -v 'Aborted connection' mysqld.log-20221231 | grep -v 'Dumping buffer' | grep -v 'Buffer pool(s) dump'
      2022-12-30T09:59:41.922354Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:41.922375Z 16252766 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046741323, ACTIVE 4 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 12 lock struct(s), heap size 1136, 50 row lock(s)
      MySQL thread id 16252771, OS thread handle 47512290137856, query id 20650523891 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('1907105','1907106','1907107','1907108','1907216','1907217','1907218','1907219','1907220','1907221','1907222','1907223','1907224','1907225','1907226','2347713','2347714','2347715','2347716')) AND resourcetype='0'
      2022-12-30T09:59:41.922405Z 16252766 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741323 lock_mode X waiting
      2022-12-30T09:59:41.922427Z 16252766 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046741081, ACTIVE 5 sec fetching rows
      mysql tables in use 1, locked 1
      2701 lock struct(s), heap size 319696, 254652 row lock(s), undo log entries 1445
      MySQL thread id 16252766, OS thread handle 47512295544576, query id 20650533254 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('9393176','9393177','9393178','9393179','9393180','9393181','9393182','9393183','9393184','9393185','9393187','9393188','9393189','9393190','9393191','9393192','9393193','9393194','9393195','9393196','9393197','9393198','9393199','9393200','9393201','9393202','9393203','9393204','9393205','9393206','9393207','9393208','9393209','9393210','9393211','9393212','9393213','9393214','9393215','9393216','9393217','9393218','9393219','9393220','9393221','9393223','9393224','9393225','9393226','9393227','9393228','9393229','9393230','9393231','9393232','9393233','9393234','9393235','9393236','9393237','9393238','9393239','9393240','9393241','9393242','9393243','9393244','9393245','9393246','9393247','9393248','9393249','9393250','9393251','9393252','9393253','9393254','9393255','9393256','9393257','9393258','9393259','9393260','9393261','
      2022-12-30T09:59:41.922463Z 16252766 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741081 lock_mode X
      2022-12-30T09:59:41.922478Z 16252766 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 1776 n bits 176 index PRIMARY of table `zabbix`.`graphs` trx id 240046741081 lock_mode X waiting
      2022-12-30T09:59:41.922495Z 16252766 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      2022-12-30T09:59:41.933497Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:41.933509Z 16252766 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046741879, ACTIVE 2 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 13 lock struct(s), heap size 1136, 121 row lock(s)
      MySQL thread id 16252778, OS thread handle 47507873978112, query id 20650528410 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('1975694','1975695','1975696','1975697','1975698','1975699','1975746','1975747','1975749','1975750','1975751','1975752','1975753','1975754','1975755','2167877','2167878','2167879','2167880','2167881','2167882','2167883','2186565','2186566','2186567','2186568','2186569','2186570','2186571','2186572','2186573','2186583','2186584','2186585','2186586','2186587','2186588','2186589','2186590','2186591','2189203','2340050','2340051','2340052','2340053','2340054','2340055')) AND resourcetype='0'
      2022-12-30T09:59:41.933535Z 16252766 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741879 lock_mode X waiting
      2022-12-30T09:59:41.933552Z 16252766 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046741081, ACTIVE 5 sec fetching rows
      mysql tables in use 1, locked 1
      2811 lock struct(s), heap size 319696, 263985 row lock(s), undo log entries 1445
      MySQL thread id 16252766, OS thread handle 47512295544576, query id 20650533254 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('9393176','9393177','9393178','9393179','9393180','9393181','9393182','9393183','9393184','9393185','9393187','9393188','9393189','9393190','9393191','9393192','9393193','9393194','9393195','9393196','9393197','9393198','9393199','9393200','9393201','9393202','9393203','9393204','9393205','9393206','9393207','9393208','9393209','9393210','9393211','9393212','9393213','9393214','9393215','9393216','9393217','9393218','9393219','9393220','9393221','9393223','9393224','9393225','9393226','9393227','9393228','9393229','9393230','9393231','9393232','9393233','9393234','9393235','9393236','9393237','9393238','9393239','9393240','9393241','9393242','9393243','9393244','9393245','9393246','9393247','9393248','9393249','9393250','9393251','9393252','9393253','9393254','9393255','9393256','9393257','9393258','9393259','9393260','9393261','
      2022-12-30T09:59:41.933577Z 16252766 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741081 lock_mode X
      2022-12-30T09:59:41.933592Z 16252766 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 1956 n bits 176 index PRIMARY of table `zabbix`.`graphs` trx id 240046741081 lock_mode X waiting
      2022-12-30T09:59:41.933627Z 16252766 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      2022-12-30T09:59:42.153801Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:42.153825Z 16252766 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046742405, ACTIVE 1 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 7 lock struct(s), heap size 1136, 48 row lock(s)
      MySQL thread id 16252785, OS thread handle 47512274458368, query id 20650532143 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('2514131','2514132','2514298','2514299','2514300','2514301','2514302','2514303','2514304','2514305','2514306','2514307','2514308')) AND resourcetype='0'
      2022-12-30T09:59:42.153865Z 16252766 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046742405 lock_mode X waiting
      2022-12-30T09:59:42.153903Z 16252766 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046741081, ACTIVE 6 sec fetching rows
      mysql tables in use 1, locked 1
      4921 lock struct(s), heap size 532688, 445447 row lock(s), undo log entries 1445
      MySQL thread id 16252766, OS thread handle 47512295544576, query id 20650533254 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('9393176','9393177','9393178','9393179','9393180','9393181','9393182','9393183','9393184','9393185','9393187','9393188','9393189','9393190','9393191','9393192','9393193','9393194','9393195','9393196','9393197','9393198','9393199','9393200','9393201','9393202','9393203','9393204','9393205','9393206','9393207','9393208','9393209','9393210','9393211','9393212','9393213','9393214','9393215','9393216','9393217','9393218','9393219','9393220','9393221','9393223','9393224','9393225','9393226','9393227','9393228','9393229','9393230','9393231','9393232','9393233','9393234','9393235','9393236','9393237','9393238','9393239','9393240','9393241','9393242','9393243','9393244','9393245','9393246','9393247','9393248','9393249','9393250','9393251','9393252','9393253','9393254','9393255','9393256','9393257','9393258','9393259','9393260','9393261','
      2022-12-30T09:59:42.153934Z 16252766 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741081 lock_mode X
      2022-12-30T09:59:42.153950Z 16252766 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 7469 n bits 184 index PRIMARY of table `zabbix`.`graphs` trx id 240046741081 lock_mode X waiting
      2022-12-30T09:59:42.153982Z 16252766 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      2022-12-30T09:59:44.199766Z 16252801 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:44.199789Z 16252801 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046743094, ACTIVE 1 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 11 lock struct(s), heap size 1136, 50 row lock(s)
      MySQL thread id 16252804, OS thread handle 47507851220736, query id 20650537262 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('2177027','2177028','2177029','2177030','2177040','2177041','2177042','2177043','2177044','2177045','2177046','2177047','2177048','2177049','2177066','2177067','2177068','2177069','2177070','2177071','2358597','2358598','2358599','2358600')) AND resourcetype='0'
      2022-12-30T09:59:44.199824Z 16252801 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046743094 lock_mode X waiting
      2022-12-30T09:59:44.199848Z 16252801 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046743042, ACTIVE 1 sec fetching rows
      mysql tables in use 1, locked 1
      2794 lock struct(s), heap size 286928, 316482 row lock(s), undo log entries 243
      MySQL thread id 16252801, OS thread handle 47512294733568, query id 20650537523 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('12916071','12916072','12916073','12916074','12916075','12916076','12916077','12916078','12916079','12916080','12916081','12916082','12916083','12916084','12916085','12916086','12916087','12916088','12916089','12916090','12916091','12916092','12916093','12916094','12916095','12916096','12916097','12916098','12916099','12916100','12916101','12916102','12916103','12916104','12916105','12916106','12916107','12916108','12916109','12916110','12916135','12916136','12916137','12916138','12916139','12916140','12916141','12916142','12916143','12916144','12916145','12916146','12916147','12916148','12916149','12916150','12916151','12916152','12916159','12916160','12916161','12916162','12916163','12916164','12916165','12916166','12916167','12916168','12916169','12916170','12916171','12916172','12916173','12916174','12916175','12916176','1291
      2022-12-30T09:59:44.199886Z 16252801 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046743042 lock_mode X
      2022-12-30T09:59:44.199904Z 16252801 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 2974 n bits 176 index PRIMARY of table `zabbix`.`graphs` trx id 240046743042 lock_mode X waiting
      2022-12-30T09:59:44.199923Z 16252801 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      [[email protected] mysql]#
      [[email protected] mysql]# grep -v 'Aborted connection' mysqld.log-20221231 | grep -v 'Dumping buffer' | grep -v 'Buffer pool(s) dump'
      2022-12-30T09:59:41.922354Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:41.922375Z 16252766 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046741323, ACTIVE 4 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 12 lock struct(s), heap size 1136, 50 row lock(s)
      MySQL thread id 16252771, OS thread handle 47512290137856, query id 20650523891 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('1907105','1907106','1907107','1907108','1907216','1907217','1907218','1907219','1907220','1907221','1907222','1907223','1907224','1907225','1907226','2347713','2347714','2347715','2347716')) AND resourcetype='0'
      2022-12-30T09:59:41.922405Z 16252766 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741323 lock_mode X waiting
      2022-12-30T09:59:41.922427Z 16252766 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046741081, ACTIVE 5 sec fetching rows
      mysql tables in use 1, locked 1
      2701 lock struct(s), heap size 319696, 254652 row lock(s), undo log entries 1445
      MySQL thread id 16252766, OS thread handle 47512295544576, query id 20650533254 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('9393176','9393177','9393178','9393179','9393180','9393181','9393182','9393183','9393184','9393185','9393187','9393188','9393189','9393190','9393191','9393192','9393193','9393194','9393195','9393196','9393197','9393198','9393199','9393200','9393201','9393202','9393203','9393204','9393205','9393206','9393207','9393208','9393209','9393210','9393211','9393212','9393213','9393214','9393215','9393216','9393217','9393218','9393219','9393220','9393221','9393223','9393224','9393225','9393226','9393227','9393228','9393229','9393230','9393231','9393232','9393233','9393234','9393235','9393236','9393237','9393238','9393239','9393240','9393241','9393242','9393243','9393244','9393245','9393246','9393247','9393248','9393249','9393250','9393251','9393252','9393253','9393254','9393255','9393256','9393257','9393258','9393259','9393260','9393261','
      2022-12-30T09:59:41.922463Z 16252766 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741081 lock_mode X
      2022-12-30T09:59:41.922478Z 16252766 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 1776 n bits 176 index PRIMARY of table `zabbix`.`graphs` trx id 240046741081 lock_mode X waiting
      2022-12-30T09:59:41.922495Z 16252766 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      2022-12-30T09:59:41.933497Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:41.933509Z 16252766 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046741879, ACTIVE 2 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 13 lock struct(s), heap size 1136, 121 row lock(s)
      MySQL thread id 16252778, OS thread handle 47507873978112, query id 20650528410 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('1975694','1975695','1975696','1975697','1975698','1975699','1975746','1975747','1975749','1975750','1975751','1975752','1975753','1975754','1975755','2167877','2167878','2167879','2167880','2167881','2167882','2167883','2186565','2186566','2186567','2186568','2186569','2186570','2186571','2186572','2186573','2186583','2186584','2186585','2186586','2186587','2186588','2186589','2186590','2186591','2189203','2340050','2340051','2340052','2340053','2340054','2340055')) AND resourcetype='0'
      2022-12-30T09:59:41.933535Z 16252766 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741879 lock_mode X waiting
      2022-12-30T09:59:41.933552Z 16252766 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046741081, ACTIVE 5 sec fetching rows
      mysql tables in use 1, locked 1
      2811 lock struct(s), heap size 319696, 263985 row lock(s), undo log entries 1445
      MySQL thread id 16252766, OS thread handle 47512295544576, query id 20650533254 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('9393176','9393177','9393178','9393179','9393180','9393181','9393182','9393183','9393184','9393185','9393187','9393188','9393189','9393190','9393191','9393192','9393193','9393194','9393195','9393196','9393197','9393198','9393199','9393200','9393201','9393202','9393203','9393204','9393205','9393206','9393207','9393208','9393209','9393210','9393211','9393212','9393213','9393214','9393215','9393216','9393217','9393218','9393219','9393220','9393221','9393223','9393224','9393225','9393226','9393227','9393228','9393229','9393230','9393231','9393232','9393233','9393234','9393235','9393236','9393237','9393238','9393239','9393240','9393241','9393242','9393243','9393244','9393245','9393246','9393247','9393248','9393249','9393250','9393251','9393252','9393253','9393254','9393255','9393256','9393257','9393258','9393259','9393260','9393261','
      2022-12-30T09:59:41.933577Z 16252766 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741081 lock_mode X
      2022-12-30T09:59:41.933592Z 16252766 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 1956 n bits 176 index PRIMARY of table `zabbix`.`graphs` trx id 240046741081 lock_mode X waiting
      2022-12-30T09:59:41.933627Z 16252766 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      2022-12-30T09:59:42.153801Z 16252766 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:42.153825Z 16252766 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046742405, ACTIVE 1 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 7 lock struct(s), heap size 1136, 48 row lock(s)
      MySQL thread id 16252785, OS thread handle 47512274458368, query id 20650532143 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('2514131','2514132','2514298','2514299','2514300','2514301','2514302','2514303','2514304','2514305','2514306','2514307','2514308')) AND resourcetype='0'
      2022-12-30T09:59:42.153865Z 16252766 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046742405 lock_mode X waiting
      2022-12-30T09:59:42.153903Z 16252766 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046741081, ACTIVE 6 sec fetching rows
      mysql tables in use 1, locked 1
      4921 lock struct(s), heap size 532688, 445447 row lock(s), undo log entries 1445
      MySQL thread id 16252766, OS thread handle 47512295544576, query id 20650533254 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('9393176','9393177','9393178','9393179','9393180','9393181','9393182','9393183','9393184','9393185','9393187','9393188','9393189','9393190','9393191','9393192','9393193','9393194','9393195','9393196','9393197','9393198','9393199','9393200','9393201','9393202','9393203','9393204','9393205','9393206','9393207','9393208','9393209','9393210','9393211','9393212','9393213','9393214','9393215','9393216','9393217','9393218','9393219','9393220','9393221','9393223','9393224','9393225','9393226','9393227','9393228','9393229','9393230','9393231','9393232','9393233','9393234','9393235','9393236','9393237','9393238','9393239','9393240','9393241','9393242','9393243','9393244','9393245','9393246','9393247','9393248','9393249','9393250','9393251','9393252','9393253','9393254','9393255','9393256','9393257','9393258','9393259','9393260','9393261','
      2022-12-30T09:59:42.153934Z 16252766 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046741081 lock_mode X
      2022-12-30T09:59:42.153950Z 16252766 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 7469 n bits 184 index PRIMARY of table `zabbix`.`graphs` trx id 240046741081 lock_mode X waiting
      2022-12-30T09:59:42.153982Z 16252766 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      2022-12-30T09:59:44.199766Z 16252801 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2022-12-30T09:59:44.199789Z 16252801 [Note] InnoDB:
      *** (1) TRANSACTION:
      
      TRANSACTION 240046743094, ACTIVE 1 sec starting index read
      mysql tables in use 1, locked 1
      LOCK WAIT 11 lock struct(s), heap size 1136, 50 row lock(s)
      MySQL thread id 16252804, OS thread handle 47507851220736, query id 20650537262 localhost zabbix_web updating
      DELETE FROM screens_items WHERE (resourceid IN ('2177027','2177028','2177029','2177030','2177040','2177041','2177042','2177043','2177044','2177045','2177046','2177047','2177048','2177049','2177066','2177067','2177068','2177069','2177070','2177071','2358597','2358598','2358599','2358600')) AND resourcetype='0'
      2022-12-30T09:59:44.199824Z 16252801 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046743094 lock_mode X waiting
      2022-12-30T09:59:44.199848Z 16252801 [Note] InnoDB: *** (2) TRANSACTION:
      
      TRANSACTION 240046743042, ACTIVE 1 sec fetching rows
      mysql tables in use 1, locked 1
      2794 lock struct(s), heap size 286928, 316482 row lock(s), undo log entries 243
      MySQL thread id 16252801, OS thread handle 47512294733568, query id 20650537523 localhost zabbix_web updating
      UPDATE graphs SET ymin_type='0',ymin_itemid=NULL WHERE (ymin_itemid IN ('12916071','12916072','12916073','12916074','12916075','12916076','12916077','12916078','12916079','12916080','12916081','12916082','12916083','12916084','12916085','12916086','12916087','12916088','12916089','12916090','12916091','12916092','12916093','12916094','12916095','12916096','12916097','12916098','12916099','12916100','12916101','12916102','12916103','12916104','12916105','12916106','12916107','12916108','12916109','12916110','12916135','12916136','12916137','12916138','12916139','12916140','12916141','12916142','12916143','12916144','12916145','12916146','12916147','12916148','12916149','12916150','12916151','12916152','12916159','12916160','12916161','12916162','12916163','12916164','12916165','12916166','12916167','12916168','12916169','12916170','12916171','12916172','12916173','12916174','12916175','12916176','1291
      2022-12-30T09:59:44.199886Z 16252801 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
      
      RECORD LOCKS space id 18938 page no 6 n bits 200 index PRIMARY of table `zabbix`.`screens_items` trx id 240046743042 lock_mode X
      2022-12-30T09:59:44.199904Z 16252801 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      
      RECORD LOCKS space id 20760 page no 2974 n bits 176 index PRIMARY of table `zabbix`.`graphs` trx id 240046743042 lock_mode X waiting
      2022-12-30T09:59:44.199923Z 16252801 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
      
      

      Expected:
      expected that hosts are removed from zabbix via zabbix API without error and deadlocks in database.

      Attachments

        Issue Links

          Activity

            People

              zabbix.dev Zabbix Development Team
              anton.samets Anton Samets
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: