Shulou(Shulou.com)06/01 Report--

This article mainly introduces the foreign key problem analysis caused by MySQL table rename, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, from the practical point of view, accumulated years of practical experience can be shared with you.

Background:

Due to the slow display of the problem graphics in zabbix monitoring, the events table in zabbix library is cleaned, and the original table is rename into bak table in the cleaning process. After rebuilding the events table, part of the data from the backup table is imported into the new table.

Later, it was found that the zabbix platform could not give an alarm and could not restore the alarm. Query failed: [1452] Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`event _ recovery`, CONSTRAINT `centry eventual recovery1` FOREIGN KEY (`eventid`) REFERENCES `events_ bak20190225` (`eventid`) ON DELETE CASCADE) [insert into event_recovery (eventid,r_eventid,correlationid,c_eventid,userid) values (4242559) 4242581 nullbottle null), (424242561moment 4242580 nullreferral nullscope null), (42424447 7ref 4242580 null null)

After the original rename, the constraint of the associated foreign key table was changed to the association of the archived table.

The method of treatment is as follows:

See which tables are associated with archive tables when establishing foreign keys

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 | caged 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 | +-- -+

View the table structure:

Show create table event_tag\ Graph * 1. Row * * Table: event_tagCreate Table: CREATE TABLE `event_ tag` (`eventtagid` bigint (20) unsigned NOT NULL, `eventid` bigint (20) unsigned NOT NULL, `tag` varchar (255i) NOT NULL DEFAULT', `value`varchar (255i) NOT NULL DEFAULT', PRIMARY KEY (`eventtagid`) KEY `eventid` (`eventid`), CONSTRAINT `centering eventual tags _ 1` FOREIGN KEY (`eventid`) REFERENCES `events_ bak20190225` (`eventid`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8

Modify foreign key constraints:

Alter table event_tag drop FOREIGN KEY censor eventual tagging 1 alter table event_tag add FOREIGN KEY c_event_tag_1 (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE

Zabbix a lot of foreign keys, next time clean up the data directly delete it.

Read the above MySQL table rename caused by foreign key analysis introduction, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.