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

 Symptoms

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

  

  
   

  

   

• 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

4. 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%';

5. Delete | and add ; with the following command:
   sed 's/^| //' /tmp/output.sql | sed 's/|$//' | sed 's/[[:blank:]]*$//' | sed 's/$/;/' > /tmp/output_2.sql

6. Apply changes:
   mysql centreon < /tmp/output_2.sql

7. 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.