3

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:

3

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.

  • 1
    Corruption was the case for me. Fixed by mysqldump of the database and a re-import using the .\ command. – Jahmic Jul 16 '18 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.