Author Topic: DB error on 3.4.5 to 4.1.1 upgrade (drupal6) (Read 3560 times)

photo21 · March 30, 2012, 05:52:07 pm

Hmm. I am getting this same error, and after reading this thread, went through the same steps to try and solve, but no joy. The error and show_table are below:

Code: [Select]

DB Error: unknown error
Error Details
Database Error Code: Error on rename of './joomla/civicrm_pcp_block' to './joomla/#sql2-643e-8b90a0' (errno: 152), 1025
Additional Details:

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

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => ALTER TABLE civicrm_pcp_block 
      DROP FOREIGN KEY FK_civicrm_pcp_block_entity_id,
      DROP INDEX FK_civicrm_pcp_block_entity_id [nativecode=1025 ** Error on rename of './schoolh_joomla/civicrm_pcp_block' to './schoolh_joomla/#sql2-643e-8b90a0' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE civicrm_pcp_block 
      DROP FOREIGN KEY FK_civicrm_pcp_block_entity_id,
      DROP INDEX FK_civicrm_pcp_block_entity_id [nativecode=1025 ** Error on rename of './schoolh_joomla/civicrm_pcp_block' to './schoolh_joomla/#sql2-643e-8b90a0' (errno: 152)]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE civicrm_pcp_block 
      DROP FOREIGN KEY FK_civicrm_pcp_block_entity_id,
      DROP INDEX FK_civicrm_pcp_block_entity_id [nativecode=1025 ** Error on rename of './schoolh_joomla/civicrm_pcp_block' to './schoolh_joomla/#sql2-643e-8b90a0' (errno: 152)]"]
)

and the show for create table:

Code: [Select]

CREATE TABLE `civicrm_pcp_block` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PCP block Id',
  `entity_table` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `entity_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_contribution_page.id',
  `supporter_profile_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to civicrm_uf_group.id. Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
  `is_approval_needed` tinyint(4) DEFAULT NULL COMMENT 'Does Personal Campaign Page require manual activation by administrator? (is inactive by default after setup)?',
  `is_tellfriend_enabled` tinyint(4) DEFAULT NULL COMMENT 'Does Personal Campaign Page allow using tell a friend?',
  `tellfriend_limit` int(10) unsigned DEFAULT NULL COMMENT 'Maximum recipient fields allowed in tell a friend',
  `link_text` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Link text for PCP.',
  `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is Personal Campaign Page Block enabled/active?',
  `notify_email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'If set, notification is automatically emailed to this email-address on create/update Personal Campaign Page',
  PRIMARY KEY (`id`),
  KEY `FK_civicrm_pcp_block_entity_id` (`entity_id`),
  KEY `FK_civicrm_pcp_block_supporter_profile_id` (`supporter_profile_id`),
  CONSTRAINT `civicrm_pcp_block_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contribution_page` (`id`),
  CONSTRAINT `civicrm_pcp_block_ibfk_2` FOREIGN KEY (`supporter_profile_id`) REFERENCES `civicrm_uf_group` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

photo21 · March 31, 2012, 07:32:37 am

I figured out the problem here, which was that the FK's are named incorrectly in the civicrm_pcp_block and civicrm_pcp tables. I renamed the FK's to what the upgrade script was requesting and the upgrade sailed on to error-less completion. They seem to be correct in the Drupal version actually, just not in Joomla.

Hershel · March 31, 2012, 11:21:48 am

Can you file an issue for this (link above right) and put in as much detail as possible--before and after versions and the right and wrong FK names. This sounds like a bug.

questions · November 09, 2012, 07:40:17 pm

I too had this issue. Unfortunately, it took me far too long to trip across what the issue was even though there were hints in this thread.

In my case, I'm using phpmyadmin 3.3.10.4 to copy the database to a test env. I blindly clicked on the add constraints. No errors.

But what it did under the covers was to created a duplicate constraint for every constraint in the db.

So not clicking on add constraints is the way to go for me. It does actually create the constraints without the box checked, (surely a phpmyadmin bug).

If for some reason, people have their production db with these extra constraints so they have no "clean" copy, the constraint names are like this:
table_name_ibfk_# (where # is a integer of the constraint, ex. civicrm_pcp_ibfk_1)

One could create sql to drop them all with

select concat('altar table ',table_name, ' drop ', constraint_name, ';')
from key_column_usage
where CONSTRAINT_NAME like '%ibfk%'
and CONSTRAINT_SCHEMA = 'your_db_name'

For completeness here is sql that created the constraints on one of the tables. You can see it duplicated each of them.

ALTER TABLE `civicrm_pcp`
ADD CONSTRAINT `civicrm_pcp_ibfk_1` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `civicrm_pcp_ibfk_2` FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page` (`id`),
ADD CONSTRAINT `FK_civicrm_pcp_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `FK_civicrm_pcp_contribution_page_id` FOREIGN KEY (`contribution_page_id`) REFERENCES `civicrm_contribution_page` (`id`);

CiviCRM Community Forums (archive)

News:

Author Topic: DB error on 3.4.5 to 4.1.1 upgrade (drupal6) (Read 3560 times)

photo21

Re: DB error on 3.4.5 to 4.1.1 upgrade (drupal6)

photo21

Re: DB error on 3.4.5 to 4.1.1 upgrade (drupal6)

Hershel

Re: DB error on 3.4.5 to 4.1.1 upgrade (drupal6)

questions

Re: DB error on 3.4.5 to 4.1.1 upgrade (drupal6)