Troubleshooting

"1451 Cannot delete or update a parent row: a foreign key constraint fails" error message

  • 11 September 2023
  • 0 replies
  • 1675 views

Userlevel 4
Badge +6

 Symptoms

 

  • When trying to delete an object (like a host, service, contact or image, etc.), you get a blank page as below:
     
    Deleting a host


     

    Deleting a service

     

Deleting an image
  • You get this type or error in /var/log/php-fpm/centreon-error.log:
    [16-Jan-2023 11:36:38 Europe/Paris] PHP Fatal error:  Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`centreon`.`host_service_relation`, CONSTRAINT `FK8v725mivdxvnvc9n3ptx6b8bg` FOREIGN KEY (`service_service_id`) REFERENCES `service` (`service_id`)) in /usr/share/centreon/www/class/centreonDB.class.php:263 Stack trace: #0 /usr/share/centreon/www/class/centreonDB.class.php(263): PDO->query() #1 /usr/share/centreon/www/include/configuration/configObject/service/DB-Func.php(396): CentreonDB->query() #2 /usr/share/centreon/www/include/configuration/configObject/service/serviceByHost.php(193): deleteServiceInDB() #3 /usr/share/centreon/www/main.get.php(272): include_once('...') #4 {main}   thrown in /usr/share/centreon/www/class/centreonDB.class.php on line 263

 

Solution

 

  1. Stop httpd, php-fpm and crond services using this command:
    systemctl stop httpd* php-fpm crond

  2. Make sure services are stopped:
    systemctl status httpd* php-fpm crond

  3. Make a mysqldump of Centreon database:
    mysqldump -h IP -u USER -pPASSWORD centreon > /tmp/dump_centreon.sql
  1. List the constraints:
    mysql -u<databaseuser> -p
    use information_schema
    select Concat('ALTER TABLE ',TABLE_NAME, ' DROP CONSTRAINT `', CONSTRAINT_NAME, '`;' ) INTO OUTFILE '/tmp/output.sql' from TABLE_CONSTRAINTS where CONSTRAINT_NAME like BINARY 'FK%';
  1. Delete | and add ; with the following command:
    sed 's/^| //' /tmp/output.sql | sed 's/|$//' | sed 's/[[:blank:]]*$//' | sed 's/$/;/' > /tmp/output_2.sql
  1. Apply changes:
    mysql centreon < /tmp/output_2.sql
  1. Start httpd, php-fpm and crond services:
    systemctl start httpd* php-fpm crond

For debian : systemctl status apache2 php8.1-fpm cron

Now everything should work regarding the configuration on the interface!

The 1451 Cannot delete or update a parent row: a foreign key constraint fails error should not appear in /var/log/php-fpm/centreon-error.log anymore.


0 replies

Be the first to reply!

Reply