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) »
  • Database update error going from 4.4.6 to 4.5 (Drupal)
Pages: [1]

Author Topic: Database update error going from 4.4.6 to 4.5 (Drupal)  (Read 1753 times)

tmccartney

  • I post frequently
  • ***
  • Posts: 154
  • Karma: 9
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.5.34-cll-lve
  • PHP version: 5.3.28
Database update error going from 4.4.6 to 4.5 (Drupal)
September 18, 2014, 01:56:10 pm
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):

Quote
[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

  • I post frequently
  • ***
  • Posts: 154
  • Karma: 9
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.5.34-cll-lve
  • PHP version: 5.3.28
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 18, 2014, 02:24:36 pm
To answer my own question, the same error occurs when going from 4.4.7 to 4.5.  :'(

Stuart Parker

  • I post occasionally
  • **
  • Posts: 64
  • Karma: 2
  • CiviCRM version: 4.5.2
  • CMS version: Drupal 7
  • MySQL version: 5.1.63
  • PHP version: 5.2.14
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 18, 2014, 10:39:46 pm
I am having exactly the same problem going from 4.4.6 to 4.5 (Drupal)

Stuart Parker

  • I post occasionally
  • **
  • Posts: 64
  • Karma: 2
  • CiviCRM version: 4.5.2
  • CMS version: Drupal 7
  • MySQL version: 5.1.63
  • PHP version: 5.2.14
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 19, 2014, 03:45:00 am
So I commented out the following lines from civicrm/CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl

Quote
{* 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

  • I post frequently
  • ***
  • Posts: 154
  • Karma: 9
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.5.34-cll-lve
  • PHP version: 5.3.28
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 19, 2014, 06:19:15 am
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 post occasionally
  • **
  • Posts: 64
  • Karma: 2
  • CiviCRM version: 4.5.2
  • CMS version: Drupal 7
  • MySQL version: 5.1.63
  • PHP version: 5.2.14
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 19, 2014, 06:46:49 am
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

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 19, 2014, 07:05:32 am
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
« Last Edit: September 19, 2014, 08:05:48 am by Donald Lobo »
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

tmccartney

  • I post frequently
  • ***
  • Posts: 154
  • Karma: 9
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.5.34-cll-lve
  • PHP version: 5.3.28
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 19, 2014, 07:50:56 am
Quote from: Stuart Parker on September 19, 2014, 06:46:49 am
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

  • I post occasionally
  • **
  • Posts: 64
  • Karma: 2
  • CiviCRM version: 4.5.2
  • CMS version: Drupal 7
  • MySQL version: 5.1.63
  • PHP version: 5.2.14
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 22, 2014, 01:23:57 am
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

  • I post frequently
  • ***
  • Posts: 179
  • Karma: 17
  • CiviCRM version: All
  • CMS version: Joomla 2.5 and 3.X, Drupal 7, Wordpress 3.7 and up
  • MySQL version: 5.1.7 and up
  • PHP version: 5.3.5 and up
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 22, 2014, 06:41:20 am
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.
CrusonWeb - Web Development, Implementation, and Support.  Specializing in CiviCRM and Joomla (although also knowledgable in Wordpress and Drupal as well).

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
September 22, 2014, 06:57:01 am

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
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

jsimonis

  • I post frequently
  • ***
  • Posts: 316
  • Karma: 4
    • Forward Support, Inc.
  • CiviCRM version: 4.4-4.5
  • CMS version: Drupal 7
  • MySQL version: 5.5.37-cll
  • PHP version: 5.3.29
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
December 02, 2014, 08:37:10 pm
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 post occasionally
  • **
  • Posts: 92
  • Karma: 5
    • Greenleaf Advancement
  • CiviCRM version: 4.4.10, 4.5.4
  • CMS version: WordPress 4, Drupal 7, Drupal 6, Joomla 3
  • MySQL version: 5.5
  • PHP version: 5.3
Re: Database update error going from 4.4.6 to 4.5 (Drupal)
March 07, 2015, 08:55:54 pm
I've just run into this error at the same place in the upgrade process on two different sites.
Guy Iaccarino
www.greenleafadvancement.com
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • Database update error going from 4.4.6 to 4.5 (Drupal)

This forum was archived on 2017-11-26.