SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails

Question

I am trying to install Zabbix by following this guide. I ran into the following problems when trying to run data.sql after running scheme.sql successfully

mysql -D zabbix -uzabbix -pPassword < /home/zabbix/zabbix-2.4.5/database/mysql/schema.sql

mysql -D zabbix -uzabbix -pPassword < /home/zabbix/zabbix-2.4.5/database/mysql/data.sql

ERROR 1452 (23000) at line 2989: Cannot add or update a child row: a foreign key constraint fails ('zabbix'.'sysmaps_elements', CONSTRAINT 'c_sysmaps_elements_2' FOREIGN KEY ('iconid_off') REFERENCES 'images' ('imageid'))

I cannot figure out why I would not be able to add or update a child row , it looks perfectly fine to me.

The scripts can be downloaded from here

Answer 1

1. check the order of sql execution. schema.sql --> images.sql --> data.sql

2. Make sure the image data is actually entered. select * from images;

This is because images.sql is not executed because autocommit is not set in some DBs. This is because there is no "commit;" in the last sentence of images.sql. so add the statement "commit;" to images.sql.

Answer 2

You can update your data.sql with a header and a footer like the following :

-- Add these lines as a header to your MYSQL data script

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 

-- Add these lines as a footer to your MYSQL data script

 UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;    
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

This will enable you to restore data without any foreign-cheking.

updated : you can re-build you data.sql file by including the header and footer :

cat header.sql data.sql footer.sql > data_NEW.sql