Federico Razzoli made good summary about Foreign Key bugs in MySQL and MariaDB
Useful links for MariaDB:
How to check if there are issues with foreign keys?
Just run command in DB console:
show engine innodb status
Example output for that command:
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2021-01-29 18:53:28 7fe626b2c700 Transaction:
TRANSACTION 687228757, ACTIVE 0 sec inserting
mysql tables in use 104, locked 104
3 lock struct(s), heap size 1184, 1 row lock(s), undo log entries 1
MySQL thread id 980, OS thread handle 0x7fe626b2c700, query id 6632 localhost lms update
INSERT INTO invoicecontents (docid, itemid,
value, taxid, taxcategory, prodid, content, count, pdiscount, vdiscount, description, tariffid)
VALUES ('0', 2, '2', '2', 0, '', 'szt.', '1', '0', '0', 'Aktywacja Stb', NULL)
Foreign key constraint fails for table `lms`.`invoicecontents`:
,
CONSTRAINT `invoicecontents_docid_fkey` FOREIGN KEY (`docid`) REFERENCES `documents` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `docid` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 80000000; asc ;;
1: len 6; hex 000002493403; asc I4 ;;
But in parent table `lms`.`documents`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 57; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000278f14df; asc ' ;;
2: len 7; hex 14000001a730ce; asc 0 ;;
3: len 1; hex 81; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 80000001; asc ;;
6: len 0; hex ; asc ;;
7: len 4; hex cd1e6070; asc `p;;
8: len 4; hex 80000003; asc ;;
9: SQL NULL;
10: len 15; hex 534f43484120437a6573c582617761; asc SOCHA Czes awa;;
11: len 28; hex 4b6170656c616ec3b37720576f6a736b6f77796368203132612f3138; asc Kapelan w Wojskowych 12a/18;;
12: len 6; hex 34342d313934; asc 44-194;;
13: len 7; hex 4b6e7572c3b377; asc Knur w;;
14: len 0; hex ; asc ;;
15: len 11; hex 3435303932333039333831; asc 45092309381;;
16: len 2; hex 800e; asc ;;
17: len 1; hex 81; asc ;;
18: SQL NULL;
19: len 0; hex ; asc ;;
20: len 4; hex 80000001; asc ;;
21: SQL NULL;
22: len 2; hex 8002; asc ;;
23: len 4; hex cd473ef0; asc G> ;;
24: len 30; hex 414c46412d53595354454d204d2e205069776f776172736b692c20412e20; asc ALFA-SYSTEM M. Piwowarski, A. ; (total 51 bytes);
25: len 11; hex 4c6f746e696bc3b3772036; asc Lotnik w 6;;
26: len 7; hex 4b6e7572c3b377; asc Knur w;;
27: len 6; hex 34342d313936; asc 44-196;;
28: len 4; hex 80000002; asc ;;
29: len 13; hex 3936392d31332d30382d303137; asc 969-13-08-017;;
30: len 9; hex 323737383630383235; asc 277860825;;
31: len 12; hex 383435343030303138303032; asc 845400018002;;
32: len 0; hex ; asc ;;
33: len 30; hex 572070727a797061646b752070797461c5842070726f73696d79206f206b; asc W przypadku pyta prosimy o k; (total 61 bytes);
34: len 0; hex ; asc ;;
35: len 0; hex ; asc ;;
36: len 10; hex 416c666153797374656d; asc AlfaSystem;;
37: len 9; hex 312f30312f32303131; asc 1/01/2011;;
38: len 2; hex 8000; asc ;;
39: len 1; hex 80; asc ;;
40: SQL NULL;
41: SQL NULL;
42: SQL NULL;
43: SQL NULL;
44: len 1; hex 80; asc ;;
45: SQL NULL;
46: len 1; hex 80; asc ;;
47: SQL NULL;
48: len 4; hex 80000000; asc ;;
49: len 2; hex 8000; asc ;;
50: len 3; hex 504c4e; asc PLN;;
51: len 9; hex 800000010000000000; asc ;;
52: SQL NULL;
53: len 4; hex 80000000; asc ;;
54: SQL NULL;
55: len 4; hex 80000000; asc ;;
56: len 2; hex 8000; asc ;;
How to temporary disable/enable foreign key check?
// disable
SET foreign_key_checks = 0
// enable
SET foreign_key_checks = 1
Foreign key check was the real case with issue https://github.com/chilek/lms-plus/issues/1645
