Upgrade fails: 5.17.5 to 5.21.12

Question

We're upgrading from 5.17.5 to 5.21.12 and it fails partway through. Here's what we get:

[Error: Re-Create Foreign key between civicrm_dashboard and civicrm_domain correctly]
ERROR FIELD ERROR VALUE
Type    DB_Error
Code    -3
Message DB Error: constraint violation
Mode    16
UserInfo    ALTER TABLE civicrm_dashboard ADD CONSTRAINT FK_civicrm_dashboard_domain_id FOREIGN KEY ( `domain_id` ) REFERENCES civicrm_domain ( id ) ON DELETE CASCADE [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`username_civicrm`.`#sql-2966_c5af6`, CONSTRAINT `FK_civicrm_dashboard_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE)]
DebugInfo   ALTER TABLE civicrm_dashboard ADD CONSTRAINT FK_civicrm_dashboard_domain_id FOREIGN KEY ( `domain_id` ) REFERENCES civicrm_domain ( id ) ON DELETE CASCADE [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`username_civicrm`.`#sql-2966_c5af6`, CONSTRAINT `FK_civicrm_dashboard_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`) ON DELETE CASCADE)]


PEAR_Exception: DB Error: constraint violation in /home/username/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php on line 922
- DB_Error: DB Error: constraint violation in unknown on line unknown
Exception trace

No. Function Location

0   CRM_Core_Error::exceptionHandler(Object(DB_Error))  /home/username/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php:922

1   PEAR_Error->__construct('DB Error: constr…', -3, 16, Array, 'ALTER TABLE civi…')    /home/username/public_html/sites/all/modules/civicrm/packages/DB.php:987

2   DB_Error->__construct(-3, 16, Array, 'ALTER TABLE civi…')   /home/username/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php:575

3   PEAR->_raiseError(Object(DB_mysqli), null, -3, 16, Array, 'ALTER TABLE civi…', 'DB_Error', true)    /home/username/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php:223

4   PEAR->__call('raiseError', Array)   /home/username/public_html/sites/all/modules/civicrm/packages/DB/common.php:1920

5   DB_common->raiseError(-3, null, null, 'ALTER TABLE civi…', '1452 ** Cannot a…') /home/username/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php:933

6   DB_mysqli->mysqliRaiseError()   /home/username/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php:403

7   DB_mysqli->simpleQuery('ALTER TABLE civi…') /home/username/public_html/sites/all/modules/civicrm/packages/DB/common.php:1229

8   DB_common->query('ALTER TABLE civi…')   /home/username/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php:2416

9   DB_DataObject->_query('ALTER TABLE civi…')  /home/username/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php:1607

10  DB_DataObject->query('ALTER TABLE civi…')   /home/username/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php:420

11  CRM_Core_DAO->query('ALTER TABLE civi…', false) /home/username/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php:1419

12  CRM_Core_DAO::executeQuery('ALTER TABLE civi…', Array, true, null, false, false)    /home/username/public_html/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FiveEighteen.php:113

13  CRM_Upgrade_Incremental_php_FiveEighteen::recreateDashboardFK(Object(CRM_Queue_TaskContext))    /home/username/public_html/sites/all/modules/civicrm/CRM/Queue/Task.php:72

14  CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))  /home/username/public_html/sites/all/modules/civicrm/CRM/Queue/Runner.php:201

15  CRM_Queue_Runner->runNext(true) /home/username/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:36

16  CRM_Queue_Page_AJAX::{closure}()    /home/username/public_html/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php:90

17  CRM_Queue_ErrorPolicy->call(Object(Closure))    /home/username/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:38

18  CRM_Queue_Page_AJAX::runNext()  /home/username/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:234

19  CRM_Core_Invoke::runItem(Array) /home/username/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:68

20  CRM_Core_Invoke::_invoke(Array) /home/username/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:36

21  CRM_Core_Invoke::invoke(Array)  /home/username/public_html/sites/all/modules/civicrm/drupal/civicrm.module:456

22  civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext')   /home/username/public_html/includes/menu.inc:527

23  menu_execute_active_handler()   /home/username/public_html/index.php:21

24  {main}   

Answer 1

You might have some leftover entries in civicrm_dashboard that point to a different domain id that no longer exists. It should be safe to delete those rows. i.e. the ones that have a domain_id that doesn't match an id in civicrm_domain.

Answer 2

As said by @demerit its because civicrm_dashboard has some orphan data. You can get rid of it by deleting the entries from the table as it won't be used anywhere. You can run below query after taking backup of database to get rid of cruft records.

DELETE cd FROM civicrm_dashboard cd
LEFT JOIN civicrm_domain cdm ON cdm.id = cd.domain_id
WHERE cdm.id IS NULL;