Author Topic: upgrading 2.2.9 to 3.0.2 : DB Error: already exists (Read 1506 times)

john ackers · November 03, 2009, 08:38:29 am

I am upgrading 2.2.9 to 3.0.2 and during the update I get this error.


[callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => 
                UPDATE civicrm_activity_target cat, civicrm_activity ca 
                    SET cat.activity_id = 666  
                WHERE ca.source_record_id IS NOT NULL   AND
                      ca.activity_type_id = 23          AND 
                      ca.id <> 666              AND 
                      ca.source_record_id = 73 AND 
                      ca.id = cat.activity_id [nativecode=1062 ** Duplicate entry '4203-666' for key 2]
    [type] => DB_Error

I think the problem may be related to this key on civicrm_activity_target:

UNIQUE KEY `UI_activity_target_contact_id` (`target_contact_id`,`activity_id`);

There are about 211021 rows in civicrm_activity and 208957 in civicrm_activity_target.

Any suggestions?
[/code]

john ackers · November 05, 2009, 06:26:55 am

I have got a workaround below. The additional 128 activity rows might have been caused by sending multiple test messages before the real thing. The SQL removes the rows from the activity_target table. I know of no side effects.

You should run this SQL before starting the upgrade.

Code: [Select]

CREATE TEMPORARY TABLE `civicrm_activity_keep` ( id int(10) unsigned, key k  (id)   ) ENGINE MEMORY ;
insert into civicrm_activity_keep(select max(cat.activity_id) from civicrm_activity ca inner join civicrm_activity_target cat on ca.id = cat.activity_id where ca.activity_type_id = 3  group by source_record_id, target_contact_id) ;
delete from civicrm_activity_target where activity_id not in (select id from civicrm_activity_keep);

When I run the sql I get:

mysql> CREATE TEMPORARY TABLE `civicrm_activity_keep` ( id int(10) unsigned, key k (id) ) ENGINE MEMORY ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into civicrm_activity_keep(select max(cat.activity_id) from civicrm_activity ca inner join civicrm_activity_target cat on ca.id = cat.activity_id where ca.activity_type_id = 3 group by source_record_id, target_contact_id) ;
Query OK, 208829 rows affected (4.45 sec)
Records: 208829 Duplicates: 0 Warnings: 0

mysql> delete from civicrm_activity_target where activity_id not in (select id from civicrm_activity_keep);
Query OK, 128 rows affected (0.98 sec)

John

Deepak Srivastava · November 06, 2009, 09:23:16 am

John,

Thats a bug & has been fixed under http://issues.civicrm.org/jira/browse/CRM-5333 and would be available as part of v3.0.3 release.

The problem is that update query (in some situations) is updating existing activities in a way that new result is violating the unique constraint.

If you are comfortable /w sql (looks like you do), here is a quick workaround -
1. Drop unique constraint from civicrm_activity_target and civicrm_activity_assignment table.
2. Let upgrade script complete.
3. Remove duplicate entries from the target and assignment table if any.
4. Put the unique constraint back.

CiviCRM Community Forums (archive)

News:

Author Topic: upgrading 2.2.9 to 3.0.2 : DB Error: already exists (Read 1506 times)

john ackers

upgrading 2.2.9 to 3.0.2 : DB Error: already exists

john ackers

Re: upgrading 2.2.9 to 3.0.2 : DB Error: already exists

Deepak Srivastava

Re: upgrading 2.2.9 to 3.0.2 : DB Error: already exists