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

I received such error ‘Cannot add or update a child row: a foreign key constraint fails’ when trying to import a new Puppet class (sys_yumversionlock) into Foreman via the smart proxy. Based on the error message, it should be database related. I am not a DBA, but I am interested to give a try, as I love troubleshooting 🙂

I logged into the Foreman database which is MySQL 5.1 in my case.

First, I confirmed the ID is in the referenced table which is ‘puppetclasses’.

mysql> select * from puppetclasses where name=”sys_yumversionlock”;
+——+——————–+———————+———————+
| id | name | created_at | updated_at |
+——+——————–+———————+———————+
| 1613 | sys_yumversionlock | 2015-01-20 23:11:04 | 2015-01-20 23:11:04 |
+——+——————–+———————+———————+
1 row in set (0.01 sec)

mysql> select * from environment_classes where puppetclass_id=1613;
Empty set (0.01 sec)

Then, I tried to manually insert the record into the table ‘environment_classes’. I get the same error, so I can tell the error should have nothing to do with the Foreman front end.

mysql> insert into environment_classes (puppetclass_id,environment_id) values (1613,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreman`.`environment_classes`, CONSTRAINT `environment_classes_puppetclass_id_fk` FOREIGN KEY (`puppetclass_id`) REFERENCES `puppetclasses` (`id`))

To understand the relationship between the tables, I checked its ‘create table’ statement. As listed below, table ‘environment_classes’ foreign keys refers the the primary key of 3 tables: “puppetclasses”, “environments” and “lookup_keys”

Also I noticed that table ‘environment_classes’ use InooDB as the storage engine, the other 3 use MyISAM.

mysql> show create table environment_classes \G
*************************** 1. row ***************************
Table: environment_classes
Create Table: CREATE TABLE `environment_classes` (
`puppetclass_id` int(11) NOT NULL,
`environment_id` int(11) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`lookup_key_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_environments_puppetclasses_on_puppetclass_id` (`puppetclass_id`),
KEY `index_environments_puppetclasses_on_environment_id` (`environment_id`),
KEY `environment_classes_lookup_key_id_fk` (`lookup_key_id`),
CONSTRAINT `environment_classes_puppetclass_id_fk` FOREIGN KEY (`puppetclass_id`) REFERENCES `puppetclasses` (`id`),
CONSTRAINT `environment_classes_environment_id_fk` FOREIGN KEY (`environment_id`) REFERENCES `environments` (`id`),
CONSTRAINT `environment_classes_lookup_key_id_fk` FOREIGN KEY (`lookup_key_id`) REFERENCES `lookup_keys` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3668 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)
mysql> show create table puppetclasses \G
*************************** 1. row ***************************
Table: puppetclasses
Create Table: CREATE TABLE `puppetclasses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_puppetclasses_on_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=1614 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql> show innodb status \G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
150121 11:31:20 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 278, signal count 278
Mutex spin waits 0, rounds 3283, OS waits 162
RW-shared spins 224, OS waits 112; RW-excl spins 4, OS waits 4
————————
LATEST FOREIGN KEY ERROR
————————
150121 11:29:07 Transaction:
TRANSACTION 0 118091, ACTIVE 0 sec, process no 52055, OS thread id 140606043965184 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 1
MySQL thread id 33516, query id 1029486164 localhost root update
insert into environment_classes (puppetclass_id,environment_id) values (1613,1)
Foreign key constraint fails for table `foreman`.`environment_classes`:
,
CONSTRAINT `environment_classes_puppetclass_id_fk` FOREIGN KEY (`puppetclass_id`) REFERENCES `puppetclasses` (`id`)
Trying to add to index `index_environments_puppetclasses_on_puppetclass_id` tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 8000064d; asc M;; 1: len 4; hex 80000e57; asc W;;

But the parent table `foreman`.`puppetclasses`
or its .ibd file does not currently exist!

Thanks to my best friend Goolge, I found the following info:

1) MyISAM does not support foreign key in MySQL 5.1 – http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html

2) Foreign keys were introduced in the database in Foreman 1.3 – http://www.theforeman.org/manuals/1.3/

Now I guess the issue was caused by the upgrade of the Foreman. It was upgraded to 1.3 couple weeks ago. And my conclusion is the previous version Foreman does not use foreign key, so it works fine with MySQL 5.1. But once upgraded to 1.3, it introduced foreign key into the database. And in MySQL 5.1, only InooDB supports foreign key. So the fix is simple, just change the engine to InnoDB for relevant tables. And there is no outage required.

After I implemented the following sql statement, the new Puppet can be imported to Foreman successfully. Oh yeah!!

ALTER TABLE puppetclasses ENGINE = InnoDB;
ALTER TABLE environments ENGINE = InnoDB;
ALTER TABLE lookup_keys ENGINE = InnoDB;