当前位置：首页 > news >正文

zabbix平台无法删除已停用主机的处理案例

news 2025/8/2 6:05:39

在zabbix平台上删除已停用的主机，提示“SQL描述式执行已失败: "DELETE FROM items WHERE (itemid IN （.....)”，无法删除，本文为处理情况。

一、问题现象

在zabbix平台上删除已停用的主机，提示“SQL描述式执行已失败: "DELETE FROM items WHERE (itemid IN （.....)”，无法删除。弹出信息如下：

弹窗信息为：

SQL描述式执行已失败: "DELETE FROM items WHERE (itemid IN ('46182','46183','46184','46185','46186','46187','46188','46189','48350','48351','48352','48353','48354','48355','48356','48357','48358','48359','48360','48361','125078','125079','125080'))"
Undefined array key "messages" [zabbix.php:17 → require_once() → ZBase->run() → ZBase->initMessages() in include/classes/core/ZBase.php:530]
foreach() argument must be of type array|object, null given [zabbix.php:17 → require_once() → ZBase->run() → ZBase->initMessages() in include/classes/core/ZBase.php:530]

二、问题处理

1、尝试先将主机关联的模板删除

尝试先将主机关联的模板删除，保存时出下以下问题弹窗：

信息内容如下：

pg_query(): Query failed: ERROR: update or delete on table "items" violates foreign key constraint "c_functions_1" on table "functions"
DETAIL: Key (itemid)=(48351) is still referenced from table "functions". [zabbix.php:17 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerHostUpdate->doAction() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CHost->update() → CHost->updateForce() → CHostGeneral->updateTemplates() → CHostGeneral::unlinkTemplatesObjects() → CDiscoveryRule::clearTemplateObjects() → CDiscoveryRule::deleteForce() → CDiscoveryRule::deleteAffectedItemPrototypes() → CItemPrototype::deleteForce() → CItemPrototype::deleteDiscoveredItems() → CItem::deleteForce() → DB::delete() → DBexecute() → pg_query() in include/db.inc.php:255]
Error in query [DELETE FROM items WHERE (itemid IN ('46182','46183','46184','46185','46186','46187','46188','46189','48350','48351','48352','48353','48354','48355','48356','48357','48358','48359','48360','48361','125078','125079','125080'))] [ERROR: update or delete on table "items" violates foreign key constraint "c_functions_1" on table "functions"
DETAIL: Key (itemid)=(48351) is still referenced from table "functions".]

可以看到这个错误的核心原因是：你要删除的items表中的记录（'46182','46183','46184','46185','46186','46187','46188','46189','48350','48351','48352','48353','48354','48355','48356','48357','48358','48359','48360','48361','125078','125079','125080'）被functions表通过外键约束c_functions_1引用了。数据库的外键约束会阻止删除 “被依赖” 的记录，以保证数据完整性。

三、解决办法

需要先删除functions表中引用了相关itemid的依赖记录，再删除items表中的目标记录。具体步骤如下：

1、查看`functions`表中依赖的记录

首先登录 Zabbix 数据库（PostgreSQL），执行以下 SQL 查询，找到报错itemid清单的记录：

SELECT * FROM functions WHERE itemid in ('46182','46183','46184','46185','46186','46187','46188','46189','48350','48351','48352','48353','48354','48355','48356','48357','48358','48359','48360','48361','125078','125079','125080');

执行后会返回functions表中所有关联这些itemid的记录（这些记录是导致删除失败的直接原因）。

2、删除`functions`表中的依赖记录

根据上一步查询到的结果，删除这些依赖记录

zabbix=# DELETE FROM functions WHERE functionid IN (SELECT functionid FROM functions WHERE itemid in ('46182','46183','46184','46185','46186','46187','46188','46189','48350','48351','48352','48353','48354','48355','48356','48357','48358','48359','48360','48361','125078','125079','125080'));
DELETE 37
zabbix=#