1

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

  • So iconid_off in all rows being inserted to sysmaps_elements already exists as a value for imageid in images? The script should be inserting in that order. What does line 2989 of the script contain? – dartonw May 3 '15 at 17:45
0
  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.

| improve this answer | |
0

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
| improve this answer | |

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.