Author Topic: Civi 2.2 FK to Civi 3.1 (Read 2100 times)

emilyf · May 25, 2010, 03:51:35 am

I'm trying to upgrade from Civi 2.2 to 3.1.

When I upgrade, I get major FK errors like such:

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

    [code] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => -- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './naaeenet_civicrm/civicrm_group_organization' to './naaeenet_civicrm/#sql2-248a-1821c' (errno: 152)]
    [type] => DB_Error
    [user_info] => -- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './naaeenet_civicrm/civicrm_group_organization' to './naaeenet_civicrm/#sql2-248a-1821c' (errno: 152)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="-- CRM-4610
    ALTER TABLE `civicrm_group_organization`
        DROP FOREIGN KEY `FK_civicrm_group_organization_group_id`,
        DROP FOREIGN KEY `FK_civicrm_group_organization_organization_id` [nativecode=1025 ** Error on rename of './naaeenet_civicrm/civicrm_group_organization' to './naaeenet_civicrm/#sql2-248a-1821c' (errno: 152)]"]
)

I'm currently trying to parse my data into a clean 2.2 db and then try this upgrade again. Upon importing a dump of my data, I'm getting this error: ERROR 1062 (23000) at line 389: Duplicate entry '0-10024' for key 2

I checked this line and it's related to the civicrm_group_contact table. I ran this mySQL query on the table:

Code: [Select]

SELECT *
FROM `civicrm_group_contact`
WHERE group_id =10024
AND contact_id =0

And it returned 89 results. I'm assuming these are all bad records. Can I just delete them outright from the table or will that cause other issues?

Thanks much.
[/code]

emilyf · May 26, 2010, 03:45:09 am

OK, I have deleted those entries but continue to get duplicate key errors when i try to import my source data into my clean 2.2 structure database. The problem table is civicrm_group_contact and it has over 20,000 records it in. I began to manually delete the dupes, but it seems like there might be thousands. The error is always like this:
ERROR 1062 (23000) at line 389: Duplicate entry '10484-10049' for key 2

And again it's with the civicrm_group_contact table.

Is there a mySQL statement I can write that will go through and delete all of these in one pass so I don't have to manually delete them one at a time? This is incredibly time consuming!!

Deepak Srivastava · May 26, 2010, 07:42:11 am

Here is the query that 'll remove duplicate entries -

Code: [Select]

 DELETE gc.* FROM civicrm_group_contact gc 
      INNER JOIN ( SELECT id, group_id, contact_id 
                           FROM civicrm_group_contact 
                           GROUP BY group_id, contact_id HAVING count(*) > 1 ) dup_gc 
                   ON ( gc.group_id = dup_gc.group_id
                           AND gc.contact_id = dup_gc.contact_id 
                           AND gc.id <> dup_gc.id )

(Assuming you have backups in case anything goes wrong.)

emilyf · May 26, 2010, 05:58:53 pm

thanks deepak. that's just the query i needed. Perfect.

onto the next issue. still getting FK constraint violations when i try to upgrade from 2.2.2 to 3.1. here's what i've done:

- after running your query I successfully upgraded to version 2.2.9 without any problem.
- I then followed the normal procedure for cleaning my data -- i dumped the structure for my custom tables and my data using -c -e -n -t. I then sourced civicrm.mysql file from my 2.2.9 civicrm directory. I then imported my custom table structure, and my data dump. All was successful. I cleared all my template_c directories and checked my install and everything works appropriately.
- I checked my version value on civicrm_domain table and it says 2.2.9.
- I attempted to upgrade to latest 3.0 version as well as 3.1 version (separately of course, was hoping 3.0 might work which is why i gave that a shot). I still get foreign key constraints errors on the upgrade:

Code: [Select]

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

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_uf_match`
        ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
        ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
        ADD UNIQUE `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
        ADD UNIQUE `UI_contact_domain_id` (`contact_id`,`domain_id`) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`naaeenet_civicrm/#sql-2565_de4`, CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_uf_match`
        ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
        ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
        ADD UNIQUE `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
        ADD UNIQUE `UI_contact_domain_id` (`contact_id`,`domain_id`) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`naaeenet_civicrm/#sql-2565_de4`, CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_uf_match`
        ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`),
        ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
        ADD UNIQUE `UI_uf_name_domain_id` (`uf_name`,`domain_id`),
        ADD UNIQUE `UI_contact_domain_id` (`contact_id`,`domain_id`) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`naaeenet_civicrm/#sql-2565_de4`, CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
)

OK, so next I tried to manually run this statement on my partially upgraded db as a test:

ALTER TABLE `civicrm_uf_match`
ADD CONSTRAINT `FK_civicrm_uf_match_domain_id` FOREIGN KEY (`domain_id`) REFERENCES `civicrm_domain` (`id`)

It works successfully. So it's not a permissions issue. What can I do next??[/code]

Deepak Srivastava · May 27, 2010, 08:28:51 am

Sounds like there are orphan contact entries in civicrm_uf_match table. Try removing those entries.

emilyf · May 28, 2010, 02:54:38 am

I am unable to find orphan entires. There are 4,792 entries in my civicrm_uf_match table. I've run the following:
-Running

Code: [Select]

SELECT DISTINCT uf_id FROM civicrm_uf_match returns 4,792 entires.

- Running

Code: [Select]

SELECT DISTINCT uf_id, contact_id FROM civicrm_uf_match GROUP BY uf_id, contact_id returns 4,792 entries.

- Running

Code: [Select]

SELECT um.* FROM civicrm_uf_match um 
      INNER JOIN ( SELECT id, uf_id, contact_id 
                           FROM civicrm_uf_match 
                           GROUP BY uf_id, contact_id HAVING count(*) > 1 ) dup_um 
                   ON ( um.uf_id = dup_um.uf_id
                           AND um.contact_id = dup_um.contact_id 
                           AND um.id <> dup_um.id )

returns nothing.

- Running

Code: [Select]

SELECT * FROM civicrm_uf_match WHERE contact_id is null and

Code: [Select]

SELECT * FROM civicrm_uf_match WHERE contact_id = 0 returns no results.

I don't know what other queries I would try to find orphaned queries if they exist. Just in case, here's a dump of my civicrm_uf_match structure:

Code: [Select]

CREATE TABLE IF NOT EXISTS `civicrm_uf_match` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'System generated ID.',
  `uf_id` int(10) unsigned NOT NULL COMMENT 'UF ID',
  `uf_name` varchar(128) collate utf8_unicode_ci default NULL COMMENT 'UF Name',
  `contact_id` int(10) unsigned default NULL COMMENT 'FK to Contact ID',
  `language` varchar(5) collate utf8_unicode_ci default NULL COMMENT 'UI language preferred by the given user/contact',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `UI_uf_name` (`uf_name`),
  UNIQUE KEY `UI_contact` (`contact_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5686 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `civicrm_uf_match`
--
ALTER TABLE `civicrm_uf_match`
  ADD CONSTRAINT `FK_civicrm_uf_match_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE;

Deepak Srivastava · May 30, 2010, 09:22:56 pm

Does the problem still exist ? By orphan entries i meant contact ids present in civicrm_uf_match table but not in civicrm_contact table. Query to find such entries would be like -

Code: [Select]

select * from civicrm_uf_match where not exists (select id from civicrm_contact where id = contact_id);

emilyf · June 01, 2010, 03:42:45 pm

Thanks, Deepak. I finally got this upgraded. I used that query, then tried the upgrade again and got issues with the civicrm_mailings table. I removed all the mailings manually in the db and all tables that referenced them. Then I got FK issues with the activity tables. I then ran various versions of your query on those tables, and then it finally upgraded successfully to 3.1.5.

Thank you so much!

Donald Lobo · June 01, 2010, 05:35:59 pm

would also be good if you can help figure out what happened to this DB and why?

its a bit scary that SO MANY FK's were wrong. which basically means that

* this was a MyISAM db at some point for an extended period
* bugs in civicrm code base

i suspect the latter accounts for a few of them, but not a whole lot (since other folks would have run into a lot of issues also)

lobo

emilyf · June 01, 2010, 05:47:13 pm

My suspicion is that the issue with this database comes from the fact that it originated from a Caravan install. I am not clear on what changes Caravan makes to the db structure or the Civi code, or if perhaps staff members at Caravan or on the original organization were make code changes/db test calls without really realizing what they were doing.

I've upgraded many a db from 2.2 and earlier, and I've never had complications like this. The majority of issues was all related to FKs that didn't exist in the original contact table or other base tables. After this experience, I do not personally plan to take over a project that originates from any proprietary version of Civi again. I like regular Civi so much better

CiviCRM Community Forums (archive)

News:

Author Topic: Civi 2.2 FK to Civi 3.1 (Read 2100 times)

emilyf

Civi 2.2 FK to Civi 3.1

emilyf

Re: Civi 2.2 FK to Civi 3.1

Deepak Srivastava

Re: Civi 2.2 FK to Civi 3.1

emilyf

Re: Civi 2.2 FK to Civi 3.1

Deepak Srivastava

Re: Civi 2.2 FK to Civi 3.1

emilyf

Re: Civi 2.2 FK to Civi 3.1

Deepak Srivastava

Re: Civi 2.2 FK to Civi 3.1

emilyf

Re: Civi 2.2 FK to Civi 3.1

Donald Lobo

Re: Civi 2.2 FK to Civi 3.1

emilyf

Re: Civi 2.2 FK to Civi 3.1