Database update error going from 4.4.6 to 4.5 (Drupal)

[tmccartney]

Today I am trying to upgrade Civi from 4.4.6 to 4.5.  I have installed the files, but when I run the database upgrade script, this is what happens (I have redacted a portion of my actual path for security reasons):

[Error: Upgrade DB to 4.5.alpha1: SQL]
Error Field   Error Value
Type   DB_Error
Code   -1
Message   DB Error: unknown error
Mode   16
UserInfo   ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id` [nativecode=1025 ** Error on rename of './(redacted)_civicrm/civicrm_contribution' to './(redacted)_civicrm/#sql2-f7e41-5701' (errno: 152)]
DebugInfo   ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id` [nativecode=1025 ** Error on rename of './(redacted)_civicrm/civicrm_contribution' to './(redacted)_civicrm/#sql2-f7e41-5701' (errno: 152)]
PEAR_Exception: DB Error: unknown error in unknown on line unknown
- DB_Error: DB Error: unknown error in unknown on line unknown
Exception trace
#   Function   Location
0   CRM_Core_Error::exceptionHandler(Object(DB_Error))   unknown:unknown
1   call_user_func(Array, Object(DB_Error))   /home/(redacted)/public_html/sites/all/modules/civicrm/packages/PEAR.php:931
2   PEAR_Error->PEAR_Error('DB Error: unknow…', -1, 16, Array, 'ALTER TABLE civi…')   /home/(redacted)/public_html/sites/all/modules/civicrm/packages/DB.php:975
3   DB_Error->DB_Error(-1, 16, Array, 'ALTER TABLE civi…')   /home/(redacted)/public_html/sites/all/modules/civicrm/packages/PEAR.php:564
4   PEAR->raiseError(null, -1, null, null, 'ALTER TABLE civi…', 'DB_Error', true)   /home/(redacted)/public_html/sites/all/modules/civicrm/packages/DB/common.php:1905
5   DB_common->raiseError(-1, null, null, null, '1025 ** Error on…')   /home/(redacted)/public_html/sites/all/modules/civicrm/packages/DB/mysql.php:899
6   DB_mysql->mysqlRaiseError()   /home/(redacted)/public_html/sites/all/modules/civicrm/packages/DB/mysql.php:328
7   DB_mysql->simpleQuery('ALTER TABLE civi…')   /home/(redacted)/public_html/sites/all/modules/civicrm/packages/DB/common.php:1216
8   DB_common->query('ALTER TABLE civi…')   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Utils/File.php:303
9   CRM_Utils_File::sourceSQLFile('mysql://(redacted)…', ' SELECT @wo…', null, true)   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Upgrade/Form.php:205
10   CRM_Upgrade_Form->source(' SELECT @wo…', true)   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Upgrade/Form.php:405
11   CRM_Upgrade_Form->processLocales('/home/(redacted)/p…', '4.5.alpha1')   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Upgrade/Form.php:437
12   CRM_Upgrade_Form->processSQL('4.5.alpha1')   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Upgrade/Incremental/php/FourFive.php:307
13   CRM_Upgrade_Incremental_php_FourFive::task_4_5_x_runSql(Object(CRM_Queue_TaskContext), '4.5.alpha1')   unknown:unknown
14   call_user_func_array(Array, Array)   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Queue/Task.php:82
15   CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Queue/Runner.php:189
16   CRM_Queue_Runner->runNext(true)   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:48
17   {closure}()   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php:89
18   CRM_Queue_ErrorPolicy->call(Object(Closure))   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:51
19   CRM_Queue_Page_AJAX::runNext()   unknown:unknown
20   call_user_func(Array)   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:296
21   CRM_Core_Invoke::runItem(Array)   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:75
22   CRM_Core_Invoke::_invoke(Array)   /home/(redacted)/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php:52
23   CRM_Core_Invoke::invoke(Array)   /home/(redacted)/public_html/sites/all/modules/civicrm/drupal/civicrm.module:457
24   civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext')   unknown:unknown
25   call_user_func_array('civicrm_invoke', Array)   /home/(redacted)/public_html/includes/menu.inc:517
26   menu_execute_active_handler()   /home/(redacted)/public_html/index.php:21
27   {main}   

Any help you can offer is appreciated.  Should I restore the 4.4.6 version of the database and files and just upgrade incrementally?

[tmccartney]

To answer my own question, the same error occurs when going from 4.4.7 to 4.5. 

[Stuart Parker]

I am having exactly the same problem going from 4.4.6 to 4.5 (Drupal)

[Stuart Parker]

So I commented out the following lines from civicrm/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl

{* ALTER TABLE civicrm_contribution DROP FOREIGN KEY `FK_civicrm_contribution_honor_contact_id`;*}

{* ALTER TABLE civicrm_pledge DROP FOREIGN KEY `FK_civicrm_pledge_honor_contact_id`; *}
{* ALTER TABLE civicrm_pledge DROP honor_contact_id;*}

...and the upgrade to 4.5.0 worked.

Then I went into phpMyAdmin and made these changes manually. It looks like the first foreign key (FK_civicrm_contribution_honor_contact_id) may have been missing already.

So not a tidy upgrade for me, but I got there in the end.

[tmccartney]

Stuart, are FK_civicrm_contribution_honor_contact_id, FK_civicrm_pledge_honor_contact_id and honor_contact_id simply fields in the tables that just need to be removed?

And was it the fact that FK_civicrm_contribution_honor_contact_id was missing already that was causing the problem?

Thanks for figuring this out!

[Stuart Parker]

I think honor_contact_id is a field that can be dropped. The other two are foreign keys. To remove them with phpMyAdmin, you can click on the Relation view and expand the Indexes to find them and Drop them.

Yes, I think the already missing FK was the problem, but I'm not 100% sure...

I'm not a developer, so I'm hoping this is an ok workaround!

[Donald Lobo]

in general missing FK's are a bad sign. CiviCRM relies a lot on FK's being present and doing the right thing. If your db is myisam or has dropped fk's at some point, it indicates a much larger problem and like to have inconsistent data

lobo

[tmccartney]

I think honor_contact_id is a field that can be dropped. The other two are foreign keys. To remove them with phpMyAdmin, you can click on the Relation view and expand the Indexes to find them and Drop them.

Yes, I think the already missing FK was the problem, but I'm not 100% sure...

I'm not a developer, so I'm hoping this is an ok workaround!

You're a genius for having figured that out.  I used the same workaround and apparently now have a successful upgrade to 4.5.  Like you, I was missing that FK, which is why I couldn't get into the relation view for that table.

[Stuart Parker]

Lobo (or anyone else) - is there any fairly quick way of doing a health check on the database structure to see if it is in good shape? I suppose I could do a test installation and manually check the structure and relations for each table, but this would be laborious.

[Dan C]

There are instructions on Ensuring Schema Integrity at http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades.  I have used these in the past when I have run into upgrade errors to resolve issues.

[Donald Lobo]

there used to be a tool for the 2.x (or 3.x) series from dharmatech that checked the health of your DB and ensured it was good.

unfortunately it is no longer maintained or supported. would be a great project for someone to take on

http://wiki.civicrm.org/confluence/display/CRMDOC/Database+Troubleshooting+Tools

volunteers?

lobo

[jsimonis]

Ran into the same error. I'm going to restore the site and try the solution you mentioned above. It's odd, as this site has only had a few upgrades and all have run without any errors or anything mentioned. Wonder if the recent MySQL update could have caused the issue.

[Guy Iaccarino]

I've just run into this error at the same place in the upgrade process on two different sites.