CiviCRM Community Forums (archive)

I get a 'DB Error: already exists' error when upgrading to 3.3.0 on Drupal. The problem appears with 'odd' participant records that I think are remnants of an earlier bug. Here, I discuss the problem and my workaround.

Symptoms

The upgrade to 3.3.0 fails with the following error logged...

Dec 05 15:02:05  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [ code ] => -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_line_item` ADD UNIQUE INDEX UI_line_item_value (`entity_table`,`entity_id`,`price_field_value_id`,`price_field_id`) [nativecode=1062 ** Duplicate entry 'civicrm_participant-12752-3-3' for key 'UI_line_item_value']
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_line_item` ADD UNIQUE INDEX UI_line_item_value (`entity_table`,`entity_id`,`price_field_value_id`,`price_field_id`) [nativecode=1062 ** Duplicate entry 'civicrm_participant-12752-3-3' for key 'UI_line_item_value']
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_line_item` ADD UNIQUE INDEX UI_line_item_value (`entity_table`,`entity_id`,`price_field_value_id`,`price_field_id`) [nativecode=1062 ** Duplicate entry 'civicrm_participant-12752-3-3' for key 'UI_line_item_value']"]
)


Dec 05 15:02:05  [info] $backTrace = /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 205
, handle,
/var/www/citybibleforum/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
/var/www/citybibleforum/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Utils/File.php, query, 260
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Upgrade/Form.php, sourceSQLFile, 154
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Upgrade/Form.php, source, 275
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Upgrade/Form.php, processLocales, 291
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Upgrade/Page/Upgrade.php, processSQL, 212
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Invoke.php, run, 219
/var/www/citybibleforum/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 356
, civicrm_invoke,
/var/www/citybibleforum/includes/menu.inc, call_user_func_array, 348
/var/www/citybibleforum/index.php, menu_execute_active_handler, 18
Analysis

The problem appears to be related to civicrm_participants with 2 or more civicrm_line_items when only one is expected. I suspect the additional line_items are the by-product of a bug (ie, it doesn't make sense that a $15 event participation should have 2 $15 line-items).

Workaround

Edit the affected participations and delete the duplicated contribution from the participation. The following SQL shows you which contacts have the problem (when you look at their participations you'll soon see the ones with multiple line-items).

select distinct
  c.id,
  c.display_name
from
  civicrm_line_item a,
  civicrm_line_item b,
  civicrm_contact c,
  civicrm_participant p
where
  a.id < b.id
  and a.entity_table = b.entity_table
  and a.entity_table = 'civicrm_participant'
  and a.entity_id = b.entity_id
  and a.price_field_id = b.price_field_id
  and a.entity_id = p.id
  and p.contact_id = c.id