CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
Pages: [1]

Author Topic: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10  (Read 3611 times)

VRAZER

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 1
SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 09:02:57 am
I've upgraded to CiviCRM 2.2 from 2.1 a few times now on a Joomla 1.5.10 installation.  I'm using the Joomla-Alt package because the regular Joomla package can't detect that zip functionality is both installed and working in my PHP installation.

After I install the upgrade, it offers me a link to run the upgrade script and when I click that, I always get this error.  How do I resolve the issue?


Sorry. A non-recoverable error has occurred.
DB Error: already exists

Database Error Code: Table 'civicrm_pcp_block' already exists, 1050

Return to home page.

Error Details:

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

       
Code: [Select]
=> -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => CREATE TABLE civicrm_pcp_block (
     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'PCP block Id',
     entity_table varchar(64)    ,
     entity_id int unsigned NOT NULL   COMMENT 'FK to civicrm_contribution_page.id',
     supporter_profile_id int unsigned   DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     is_approval_needed tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     is_tellfriend_enabled tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?',
     tellfriend_limit int unsigned   DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend',
     link_text varchar(255)   DEFAULT NULL COMMENT 'Link text for PCP.',
     is_active tinyint   DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?',
     PRIMARY KEY ( id ),
     CONSTRAINT FK_civicrm_pcp_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_pcp_block' already exists]
    [type] => DB_Error
    [user_info] => CREATE TABLE civicrm_pcp_block (
     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'PCP block Id',
     entity_table varchar(64)    ,
     entity_id int unsigned NOT NULL   COMMENT 'FK to civicrm_contribution_page.id',
     supporter_profile_id int unsigned   DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     is_approval_needed tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     is_tellfriend_enabled tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?',
     tellfriend_limit int unsigned   DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend',
     link_text varchar(255)   DEFAULT NULL COMMENT 'Link text for PCP.',
     is_active tinyint   DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?',
     PRIMARY KEY ( id ),
     CONSTRAINT FK_civicrm_pcp_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_pcp_block' already exists]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TABLE civicrm_pcp_block (
     id int unsigned NOT NULL AUTO_INCREMENT  COMMENT 'PCP block Id',
     entity_table varchar(64)    ,
     entity_id int unsigned NOT NULL   COMMENT 'FK to civicrm_contribution_page.id',
     supporter_profile_id int unsigned   DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     is_approval_needed tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
     is_tellfriend_enabled tinyint   DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?',
     tellfriend_limit int unsigned   DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend',
     link_text varchar(255)   DEFAULT NULL COMMENT 'Link text for PCP.',
     is_active tinyint   DEFAULT 1 COMMENT 'Is Personal Campaign Page Block enabled/active?',
     PRIMARY KEY ( id ),
     CONSTRAINT FK_civicrm_pcp_block_entity_id FOREIGN KEY (entity_id) REFERENCES civicrm_contribution_page(id)   
)  ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_pcp_block' already exists]"]
)

Deepak Srivastava

  • Moderator
  • Ask me questions
  • *****
  • Posts: 677
  • Karma: 65
Re: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 10:17:00 am
Quote
I've upgraded to CiviCRM 2.2 from 2.1 a few times now on a Joomla 1.5.10 installation.

Do you have the backup of your 2.1 db, the one before you tried upgrade to 2.2 for the first time. Since i guess your db has got partially upgraded and hence that error.
Found this reply helpful? Contribute NOW and help us improve CiviCRM with the Make it Happen! initiative.

VRAZER

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 1
Re: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 11:18:10 am
I do have a backup of the database, but the problem is that I have had this same error hit me three times in a row when I've run through the upgrade process.  So I'm not sure what's going on.

Any insight?

VRAZER

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 1
Re: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 11:27:52 am
And I should clarify that I've restored my backup database before running the upgrade each time I tried to do the install.

Deepak Srivastava

  • Moderator
  • Ask me questions
  • *****
  • Posts: 677
  • Karma: 65
Re: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 11:54:32 am
civicrm_pcp_block table was added in 2.2.alpha1. Not sure how its present in your 2.1 db.

So now since its present in your backup itself, drop this table from your db before clicking on upgrade and try again. I am sure this table would not have any data, still you do make sure from your side before dropping.

Found this reply helpful? Contribute NOW and help us improve CiviCRM with the Make it Happen! initiative.

VRAZER

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 1
Re: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 01:33:31 pm
Thanks.  It turns out that I hadn't fully restored my 2.1 backup and left some of the 2.2 tables that were created when I first tried the upgrade. 

I flushed out the entire database (deleting all tables), repopulated from a true 2.1 backup and this time I got this error.  I think this is the failure error I ran into in the first place (before the one I pasted above).

Any ideas what would cause this?


Sorry. A non-recoverable error has occurred.
DB Error: constraint violation

Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`predisan_predisan/#sql-a0a_318baa`, CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL), 1452

Return to home page.

Error Details:

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

       
Code: [Select]
=> -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_note`
    ADD CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`predisan_predisan/#sql-a0a_318baa`, CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_note`
    ADD CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`predisan_predisan/#sql-a0a_318baa`, CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_note`
    ADD CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`predisan_predisan/#sql-a0a_318baa`, CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)]"]
)

Deepak Srivastava

  • Moderator
  • Ask me questions
  • *****
  • Posts: 677
  • Karma: 65
Re: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 03:13:17 pm
When did you get this error, while restoring the dump ? If yes while importing/restoring the dump you 'll need to disable foreign key checks.

Or if its a upgrade time error - The error indicates that there are records in your civicrm_note table pointing to non existing contact. Use the following query to fix such records.
Code: [Select]
UPDATE civicrm_note cn
LEFT JOIN civicrm_contact cc  ON cn.contact_id=cc.id
SET cn.contact_id=NULL
WHERE cc.id IS NULL;
Found this reply helpful? Contribute NOW and help us improve CiviCRM with the Make it Happen! initiative.

VRAZER

  • I post occasionally
  • **
  • Posts: 49
  • Karma: 1
Re: SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10
April 11, 2009, 05:41:36 pm
Yep.  It was about 5 contact notes in my production database that didn't have contact records any more.  I'm not sure how that happened in the first place, but the query you supplied fixed it. 

Thanks for your help!
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • SQL Related Errors Upgrading from 2.1 to 2.2 on Joomla 1.5.10

This forum was archived on 2017-11-26.