Incidentu reaģēšana

MySQL kļūda 1452: Cannot Add or Update a Child Row (ārējās atslēgas ierobežojums)

Kļūda 1452 nozīmē, ka rinda norāda uz vecāka ierakstu, kas neeksistē. Parasti risinājums ir viens trūkstošs INSERT vai ielāde nepareizā secībā. Bīstamais risinājums ir izslēgt foreign_key_checks un par to aizmirst. Šeit ir, kā atrast cēloni, sakārtot datus un droši pievienot ierobežojumus lielām tabulām.

· 13 min lasīšanai · AG Data komanda

MySQL kļūda 1452, Cannot add or update a child row: a foreign key constraint fails, nozīmē, ka InnoDB atsakās saglabāt rindu, kuras ārējās atslēgas vērtībai nav atbilstošas rindas vecāka tabulā. Nekas nav salūzis: ierobežojums dara savu darbu. Noderīgais jautājums ir, kāpēc jūsu lietojumprogramma, imports vai migrācija mēģina ierakstīt atsauci uz kaut ko, kā nav.

Vispirms izlasiet visu ziņojumu

mysql> INSERT INTO orders (id, customer_id, total) VALUES (1001, 777, 49.90);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`shop`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`)
REFERENCES `customers` (`id`))

Teksts pēc vispārīgās daļas nosauc bērna tabulu, ierobežojumu, bērna kolonnu un vecāka tabulu un kolonnu. Tas ir viss, kas vajadzīgs, lai sāktu. Kļūda ir ER_NO_REFERENCED_ROW_2, SQLSTATE 23000 (MySQL kļūdu saraksts). Tās māsa, kļūda 1451 Cannot delete or update a parent row, ir tas pats ierobežojums no otras puses: jūs dzēšat vai maināt vecāka rindu, kurai vēl ir bērni.

Ja redzat īsāko kļūdu 1216 bez ierobežojuma detaļām, lietotājam, kas izpilda komandu, nav tabulas līmeņa tiesību uz vecāku tabulām, tāpēc MySQL slēpj informāciju par vecāku. Veiciet diagnostiku ar lietotāju, kas redz abas tabulas.

Biežākie cēloņi

  1. Vecāka rindas tiešām nav. Drukas kļūda ID, agrāk dzēsts klients, testa dati, kas nekad neizveidoja vecāku, vai ID no citas vides.
  2. Nepareiza ievietošanas secība. Lietojumprogramma ievieto pasūtījumu pirms klienta, vai ETL darbs ielādē bērnu tabulas pirms vecāku tabulām.
  3. Ierobežojuma pievienošana netīriem datiem. ALTER TABLE ... ADD FOREIGN KEY ar ieslēgtām pārbaudēm pārbauda esošās rindas, un mūsu pieredzē pietiek ar vienu bāreni jebkur tabulā, lai viss ALTER neizdotos ar 1452. Tabulā, kas gadiem dzīvojusi bez ierobežojumiem, bāreņi ir vienmēr.
  4. Daļēja atjaunošana un datu kopijas. Vienas tabulas atjaunošana no vakardienas rezerves kopijas datubāzē, kur vecāka tabula jau ir mainījusies, vai tabulu apakškopas kopēšana uz staging.

Diagnostika trīs vaicājumos

-- 1. which constraint, which columns (or read the error text)
SHOW CREATE TABLE orders\G

-- every foreign key that points somewhere, per schema
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'shop' AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

-- 2. does the parent row exist?
SELECT id FROM customers WHERE id = 777;

-- 3. details of the most recent foreign key error
SHOW ENGINE INNODB STATUS\G   -- section LATEST FOREIGN KEY ERROR

InnoDB gadījumā SHOW ENGINE INNODB STATUS sadaļa LATEST FOREIGN KEY ERROR parāda detaļas par pēdējo ārējās atslēgas kļūdu (ārējo atslēgu dokumentācija). Arī ierobežojumu problēmām, veidojot tabulas (kļūda 150), tā ir pirmā vieta, kur skatīties.

Ja cēlonis ir esošie dati, izveidojiet bāreņu sarakstu:

-- child rows whose parent is missing (orphans)
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL
LIMIT 100;

Pareizs risinājums

foreign_key_checks = 0 nav risinājums. Kamēr pārbaudes ir izslēgtas, InnoDB pieņem jebkuru vērtību. To atkārtota ieslēgšana neskenē tabulu, tāpēc katrs pa to laiku ierakstītais bārenis paliek tur nepamanīts, līdz par to paklūp pārskats, JOIN vai vēlāks ALTER TABLE (MySQL rokasgrāmata). Lietojiet to tikai sesijā, ko kontrolējat, un vienmēr pēc tam pārbaudiet bāreņus.

Ārējo atslēgu prasības, kas izraisa saistītas kļūdas

Lielās tabulās: kas mainās

Testa datubāzē ar desmit tūkstošiem rindu viss iepriekš minētais aizņem sekundes. Produkcijas tabulā ar simtiem miljonu rindu katram solim vajag plānu.

-- on a large table: walk the child primary key in ranges, ideally on a replica
SELECT o.id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.id >= 0 AND o.id < 1000000
  AND o.customer_id IS NOT NULL
  AND c.id IS NULL;
-- next range: 1000000 .. 2000000, and so on

-- once the data is clean: add the constraint without a table copy
SET SESSION foreign_key_checks = 0;
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
  REFERENCES customers (id),
  ALGORITHM=INPLACE, LOCK=NONE;
SET SESSION foreign_key_checks = 1;

Galvenās atziņas

MySQL InnoDB Ārējās atslēgas Kļūdas
Sadarbība ar mums

Bāreņu rindas vai neizdevusies atjaunošana?

Mēs sakārtojam referenciālās integritātes problēmas produkcijas MySQL: bāreņu auditi miljardu rindu tabulās, droša ierobežojumu ieviešana un atjaunošana pareizā secībā.

Sazināties Vairāk no emuāra