Answers.Body: <p>Can you do <code>select f.* from civicrm_mapping_field f left join civicrm_mapping m on f.mapping_id = m.id where m.id is null</code>. I suspect you have some fields where the mapping_id points to a mapping that doesn't exist and that query will locate them.</p>
  <p>If that query does show you some rows, then since the mapping doesn't exist anyway you could just delete those rows.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-19T00:14:55.423Z
Answers.LastActivityDate: 2021-11-19T00:14:55.423Z
Answers.UserDisplayName: Demerit
Body: <p>I tried to upgrade our CiviCRM installation to 5.43.0 but on upgrade script an error appears:</p>
  <pre><code>Type    DB_Error
  Code    -3
  Message DB Error: constraint violation
  Mode    16
  UserInfo    ALTER TABLE civicrm_mapping_field ADD CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`) ON DELETE CASCADE [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civi_crm`.`#sql-25b0_7f`, CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping` (`id`) ON DELETE CASCADE)]
  DebugInfo   ALTER TABLE civicrm_mapping_field ADD CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`) ON DELETE CASCADE [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civi_crm`.`#sql-25b0_7f`, CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping` (`id`) ON DELETE CASCADE)]
  </code></pre>
  <p>The error shown:</p>
  <pre><code>PEAR_Exception: DB Error: constraint violation in \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
  </code></pre>
  <p>The backtrace:</p>
  <pre><code>0   CRM_Core_Error::exceptionHandler(Object(DB_Error))   \sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:922
  1   PEAR_Error-&gt;__construct('DB Error: constr…', -3, 16, Array, ' ALTER T…')     \sites\all\modules\civicrm\vendor\pear\db\DB.php:997
  2   DB_Error-&gt;__construct(-3, 16, Array, ' ALTER T…')    \sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:575
  3   PEAR-&gt;_raiseError(Object(DB_mysqli), null, -3, 16, Array, ' ALTER T…', 'DB_Error', true)     \sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:223
  4   PEAR-&gt;__call('raiseError', Array)    \sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1928
  5   DB_common-&gt;raiseError(-3, null, null, ' ALTER T…', '1452 ** Cannot a…')  \sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:936
  6   DB_mysqli-&gt;mysqliRaiseError()    \sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:406
  7   DB_mysqli-&gt;simpleQuery(' ALTER T…')  \sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1234
  8   DB_common-&gt;query(' ALTER T…')    \sites\all\modules\civicrm\packages\DB\DataObject.php:2696
  9   DB_DataObject-&gt;_query(' ALTER T…')   \sites\all\modules\civicrm\packages\DB\DataObject.php:1829
  10  DB_DataObject-&gt;query(' ALTER T…')    \sites\all\modules\civicrm\CRM\Core\DAO.php:468
  11  CRM_Core_DAO-&gt;query(' ALTER T…', false)  \sites\all\modules\civicrm\CRM\Core\DAO.php:1619
  12  CRM_Core_DAO::executeQuery(' ALTER T…', Array, true, null, false, false)     \sites\all\modules\civicrm\CRM\Upgrade\Incremental\php\FiveFortyThree.php:176
  13  CRM_Upgrade_Incremental_php_FiveFortyThree::alterMappingFK(Object(CRM_Queue_TaskContext))    \sites\all\modules\civicrm\CRM\Queue\Task.php:74
  14  CRM_Queue_Task-&gt;run(Object(CRM_Queue_TaskContext))   \sites\all\modules\civicrm\CRM\Queue\Runner.php:201
  15  CRM_Queue_Runner-&gt;runNext(true)  \sites\all\modules\civicrm\CRM\Queue\Page\AJAX.php:36
  16  CRM_Queue_Page_AJAX::{closure}()     \sites\all\modules\civicrm\CRM\Queue\ErrorPolicy.php:89
  17  CRM_Queue_ErrorPolicy-&gt;call(Object(Closure))     \sites\all\modules\civicrm\CRM\Queue\Page\AJAX.php:38
  18  CRM_Queue_Page_AJAX::runNext()   \sites\all\modules\civicrm\CRM\Core\Invoke.php:279
  19  CRM_Core_Invoke::runItem(Array)  \sites\all\modules\civicrm\CRM\Core\Invoke.php:69
  20  CRM_Core_Invoke::_invoke(Array)  \sites\all\modules\civicrm\CRM\Core\Invoke.php:36
  21  CRM_Core_Invoke::invoke(Array)   \sites\all\modules\civicrm\drupal\civicrm.module:458
  22  civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext')    \includes\menu.inc:527
  23  menu_execute_active_handler()    \index.php:21
  24  {main}
  </code></pre>
  <p>The error happens on my local machine as well on the dev server and it happens when calling the upgrade script via drush as well as in browser</p>
  
Tags: <upgrade>
Title: Upgrade to 5.43.0 fails: DB Error: constraint violation on "Make mapping field foreign key cascade delete"
ThreadText: # Upgrade to 5.43.0 fails: DB Error: constraint violation on "Make mapping field foreign key cascade delete"
  
  (Asked by: username_0 on 2021-11-18)
  
  I tried to upgrade our CiviCRM installation to 5.43.0 but on upgrade script an error appears:
  Type    DB_Error
  Code    -3
  Message DB Error: constraint violation
  Mode    16
  UserInfo    ALTER TABLE civicrm_mapping_field ADD CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`) ON DELETE CASCADE [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civi_crm`.`#sql-25b0_7f`, CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping` (`id`) ON DELETE CASCADE)]
  DebugInfo   ALTER TABLE civicrm_mapping_field ADD CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping`(`id`) ON DELETE CASCADE [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civi_crm`.`#sql-25b0_7f`, CONSTRAINT `FK_civicrm_mapping_field_mapping_id` FOREIGN KEY (`mapping_id`) REFERENCES `civicrm_mapping` (`id`) ON DELETE CASCADE)]
  
  The error shown:
  PEAR_Exception: DB Error: constraint violation in \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
  
  The backtrace:
  0   CRM_Core_Error::exceptionHandler(Object(DB_Error))   \sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:922
  1   PEAR_Error->__construct('DB Error: constr…', -3, 16, Array, ' ALTER T…')     \sites\all\modules\civicrm\vendor\pear\db\DB.php:997
  2   DB_Error->__construct(-3, 16, Array, ' ALTER T…')    \sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:575
  3   PEAR->_raiseError(Object(DB_mysqli), null, -3, 16, Array, ' ALTER T…', 'DB_Error', true)     \sites\all\modules\civicrm\vendor\pear\pear-core-minimal\src\PEAR.php:223
  4   PEAR->__call('raiseError', Array)    \sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1928
  5   DB_common->raiseError(-3, null, null, ' ALTER T…', '1452 ** Cannot a…')  \sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:936
  6   DB_mysqli->mysqliRaiseError()    \sites\all\modules\civicrm\vendor\pear\db\DB\mysqli.php:406
  7   DB_mysqli->simpleQuery(' ALTER T…')  \sites\all\modules\civicrm\vendor\pear\db\DB\common.php:1234
  8   DB_common->query(' ALTER T…')    \sites\all\modules\civicrm\packages\DB\DataObject.php:2696
  9   DB_DataObject->_query(' ALTER T…')   \sites\all\modules\civicrm\packages\DB\DataObject.php:1829
  10  DB_DataObject->query(' ALTER T…')    \sites\all\modules\civicrm\CRM\Core\DAO.php:468
  11  CRM_Core_DAO->query(' ALTER T…', false)  \sites\all\modules\civicrm\CRM\Core\DAO.php:1619
  12  CRM_Core_DAO::executeQuery(' ALTER T…', Array, true, null, false, false)     \sites\all\modules\civicrm\CRM\Upgrade\Incremental\php\FiveFortyThree.php:176
  13  CRM_Upgrade_Incremental_php_FiveFortyThree::alterMappingFK(Object(CRM_Queue_TaskContext))    \sites\all\modules\civicrm\CRM\Queue\Task.php:74
  14  CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))   \sites\all\modules\civicrm\CRM\Queue\Runner.php:201
  15  CRM_Queue_Runner->runNext(true)  \sites\all\modules\civicrm\CRM\Queue\Page\AJAX.php:36
  16  CRM_Queue_Page_AJAX::{closure}()     \sites\all\modules\civicrm\CRM\Queue\ErrorPolicy.php:89
  17  CRM_Queue_ErrorPolicy->call(Object(Closure))     \sites\all\modules\civicrm\CRM\Queue\Page\AJAX.php:38
  18  CRM_Queue_Page_AJAX::runNext()   \sites\all\modules\civicrm\CRM\Core\Invoke.php:279
  19  CRM_Core_Invoke::runItem(Array)  \sites\all\modules\civicrm\CRM\Core\Invoke.php:69
  20  CRM_Core_Invoke::_invoke(Array)  \sites\all\modules\civicrm\CRM\Core\Invoke.php:36
  21  CRM_Core_Invoke::invoke(Array)   \sites\all\modules\civicrm\drupal\civicrm.module:458
  22  civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext')    \includes\menu.inc:527
  23  menu_execute_active_handler()    \index.php:21
  24  {main}
  
  The error happens on my local machine as well on the dev server and it happens when calling the upgrade script via drush as well as in browser
  
  **Tags:** upgrade
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-11-19. Score: 1 | Accepted answer
  
  Can you do `select f.* from civicrm_mapping_field f left join civicrm_mapping m on f.mapping_id = m.id where m.id is null`. I suspect you have some fields where the mapping\_id points to a mapping that doesn't exist and that query will locate them.
  
  If that query does show you some rows, then since the mapping doesn't exist anyway you could just delete those rows.
  
  
  ---
  
  