MariaDB: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

Question

This question has already been asked before, but none of the answers suggested were applicable to my situation.

Issue:

I am prevented from inserting data into a column because of a foreign key violation. However, the foreign key is a valid reference.

Failing query:

INSERT INTO `insert-table`(`not-important`, `foobar-id`) VALUES ('whatever', 'This exists in both tables');

Error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (mydb.insert-table, CONSTRAINT FK_foobar FOREIGN KEY (foobar-id) REFERENCES foobar-table (foobar-id))

SELECT COUNT(1) FROM `foobar-table` WHERE `foobar-id` = 'This exists in both tables';
1

Schema:

mysql> SHOW CREATE TABLE `insert-table`;
CREATE TABLE `insert-table` (
  `foobar-id` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  `not-important` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`foobar-id`,`not-important`),
  CONSTRAINT `FK_foobar` FOREIGN KEY (`foobar-id`) REFERENCES `foobar-table` (`foobar-id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

mysql> SHOW CREATE TABLE `foobar-table`;
CREATE TABLE `foobar-table` (
  `foobar-id` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`foobar-id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Troubleshooting:

1. the data is there (no typo, see the error section)
2. both columns have the same type declaration (see above)
3. the engine used is already InnoDB (some websites recommend to switch to it)
4. I tried to remove the column names from the statement in case a bug was making MySQL ignore that (since they're both of the same type)
5. SELECT VERSION(); 5.6.10
6. SHOW VARIABLES LIKE 'innodb_version'; 1.2.10
7. Run (with the PROCESS privilege):

SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-01-10 15:24:39 2b1f59283700 Transaction:
TRANSACTION 17469656069, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s)
MySQL thread id 147376, OS thread handle 0x2b1f59283700, query id 152047035 123.45.67.89 theDbUser update
INSERT INTO `insert-table` VALUES (‘This exists in the other table’, ‘whatever’)
Foreign key constraint fails for table `mydb`.`insert-table`:
,
 CONSTRAINT `FK_foobar` FOREIGN KEY (`foobar-id`) REFERENCES `foobar-table` (`foobar-id`)
Trying to add to index `PRIMARY` tuple:
DATA TUPLE: 4 fields;
0: len=22; bufptr=0x2b2ea459a812; hex= 546869732065786973747320696e20626f7468207461626c6573; asc This exists in both tables;;
1: len=25; bufptr=0x2b2ea459ab0c; hex= 7768617465766572; asc whatever;;
2: len=6; bufptr=0x2b3a97464610; hex= 00041145cc05; asc    E  ;;
3: len=7; bufptr=0x2b3a97464618; hex= 00000000000000; asc        ;;

But the parent table `mydb`.`foobar-table`
or its .ibd file does not currently exist!

Related:

• StackOverflow: MySQL 5.5 foreign key constraint fails when foreign key exists : links to bug below
• https://bugs.mysql.com/bug.php?id=60309 fixed in 5.5 (2011), should not apply to MySQL 5.6; only for OSX
• Foreign Key Constraint fails : suggests to temporarily disable foreign key checks. Not sustainable in my case.
• "Cannot add or update a child row" when deleting a record? : no accepted answer
• SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails : same

Answer 1

Obviously this doesn't happen on all foreign keys so we suspect there was some corruption affecting that foreign key.

The solution was to drop and recreate the foreign key.