Upgrading 4.0.7-> 4.1.1 Error on rename of './dbname/civicrm_pcp_block

[SemperFi]

Hello,

I'm facing this issue when trying to execute the /civicrm/upgrade?reset=1 - I'm migrating from civi 4.0.7 to 4.1.1 on a Drupal 7.12 installation.
Could you please advise how to solve the problem ?

Can I use the 4.1.1 installation even if the upgrade script didn't go to its end ?
Thanks in advance

---------------------------------
DB Error: unknown error
Error Details
Database Error Code: Error on rename of './dbname/civicrm_pcp_block' to './dbname/#sql2-30f2-5b9d' (errno: 152), 1025
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> -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 './dbname/civicrm_pcp_block' to './dbname/#sql2-30f2-5b9d' (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 './dbname/civicrm_pcp_block' to './dbname/#sql2-30f2-5b9d' (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 './dbname/civicrm_pcp_block' to './dbname/#sql2-30f2-5b9d' (errno: 152)]"]
)


[Hershel]

See http://forum.civicrm.org/index.php?topic=23837.new

[SemperFi]

Hi !
I was able to run this request from my phpadmin interface : ALTER TABLE `civicrm_pcp_block` DROP INDEX `FK_civicrm_pcp_block_entity_id` and it worked well.
But then I ran the update (.../civicrm/upgrade?reset=1) and it failed :

DB Error: not found
Error Details
Database Error Code: Can't DROP 'FK_civicrm_pcp_block_entity_id'; check that column/key exists, 1091
Additional Details: ...

Can I remove this FK manually, using phpmyadmin, and then delete this code in the sql update file, to be able to run it again ? - which file should I edit to find this request ?
Thanks

[Hershel]

Can I remove this FK manually, using phpmyadmin, and then delete this code in the sql update file, to be able to run it again ? - which file should I edit to find this request ?
Thanks

Yes you can do that. I don't know which file offhand but you can simply search the source code for that MySQL query to find it.

[SemperFi]

OK great - Can you precise "search the source code for that MySQL query" / which file is/are called when lauching the update process, or in which folder would I be able to find it ?

Searching in all the source code could be very long regarding the number of files...

[Hershel]

Took me 3 seconds to search:

Code: [Select]

$ grep -r 'ALTER TABLE civicrm_pcp_block' *
CRM/Upgrade/Incremental/sql/4.1.alpha1.mysql.tpl:ALTER TABLE civicrm_pcp_block
CRM/Upgrade/Incremental/sql/4.1.0.mysql.tpl:      ALTER TABLE civicrm_pcp_block ADD link_text_{$locale} varchar(255);
CRM/Upgrade/Incremental/sql/4.1.0.mysql.tpl:  ALTER TABLE civicrm_pcp_block DROP link_text;
That means it's in CRM/Upgrade/Incremental/sql

[SemperFi]

In the file /sites/all/modules/civicrm/CRM/Upgrade/Incremental/sql4.1.alpha1.mysql.tpl,  I commented these lines :

-- CRM-8534
-- ALTER TABLE civicrm_pcp_block
--      DROP FOREIGN KEY FK_civicrm_pcp_block_entity_id,
--      DROP INDEX FK_civicrm_pcp_block_entity_id;

-- ALTER TABLE civicrm_pcp
--      DROP FOREIGN KEY FK_civicrm_pcp_contribution_page_id,
--      DROP INDEX FK_civicrm_pcp_contribution_page_id;

and I removed manually the FK using phpmyadmin. Then I ran the update script and it worked perfectly ! Upgrade done !
Thanks a lot for your invaluable help !

[Hershel]

My pleasure.

[Prolineserver]

See also this bug: http://issues.civicrm.org/jira/browse/CRM-9450

[CiviTeacher.com]

See attached file for illustration to drop keys via PHPMyAdmin.

Also:
http://s3.postimg.org/52jkwv20j/this_is_how.png