背景:
由于 zabbix 监控的问题图形展示很慢, 对 zabbix 库的 events 表进行了清理, 清理过程采用了原表 rename 成 bak 表, 重建 events 表后, 将备份表部分数据导入到新表中.
后发现 zabbix 平台无法报警, 无法恢复报警, 日志类似报错:
2315:20190301:104933.609 [Z3005] query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event_recovery`, CONSTRAINT `c_event_recovery_1` FOREIGN KEY (`eventid`) REFERENCES `events_bak20190225` (`eventid`) ON DELETE CASCADE) [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4242559,4242581,null,null,null),(4242561,4242580,null,null,null),(4242447,4242580,null,null,null);
原来 rename 后, 关联的外键表的约束竟然一并修改成了归档表的关联.
处理方法如下:
查看哪些表建立外键时关联了归档表
- select distinct TABLE_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA ='zabbix' and CONSTRAINT_name != 'PRIMARY' and REFERENCED_TABLE_NAME like 'event%';
- +----------------+--------------------+-----------------------+
- | TABLE_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME |
- +----------------+--------------------+-----------------------+
- | acknowledges | c_acknowledges_2 | events |
- | alerts | c_alerts_2 | events |
- | alerts | c_alerts_5 | events |
- | event_recovery | c_event_recovery_1 | events |
- | event_recovery | c_event_recovery_2 | events |
- | event_recovery | c_event_recovery_3 | events |
- | event_tag | c_event_tag_1 | events_bak20190225 |
- | problem | c_problem_1 | events |
- | problem | c_problem_2 | events |
- +----------------+--------------------+-----------------------+
查看表结构:
- show create table event_tag\G
- *************************** 1. row ***************************
- Table: event_tag
- Create Table: CREATE TABLE `event_tag` (
- `eventtagid` bigint(20) unsigned NOT NULL,
- `eventid` bigint(20) unsigned NOT NULL,
- `tag` varchar(255) NOT NULL DEFAULT '',
- `value` varchar(255) NOT NULL DEFAULT '',
- PRIMARY KEY (`eventtagid`),
- KEY `event_tag_1` (`eventid`),
- CONSTRAINT `c_event_tag_1` FOREIGN KEY (`eventid`) REFERENCES `events_bak20190225` (`eventid`) ON DELETE CASCADE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
修改外键约束:
- alter table event_tag drop FOREIGN KEY c_event_tag_1;
- alter table event_tag add FOREIGN KEY c_event_tag_1 (`eventid`) REFERENCES `events`(`eventid`) ON DELETE CASCADE;
zabbix 好多外键, 下次清理数据直接删除吧.
来源: http://www.bubuko.com/infodetail-2974320.html