Error upgrading from 3.1.4 to 3.2.0

[rvanderh]

Hello,

I am working on drupal 6.1.7

I followed instructions here:
http://wiki.civicrm.org/confluence/display/CRMDOC32/Upgrade+Drupal+Sites+to+3.2
- Made sure I disabled civicrm_theme before upgrading, which is the only civicrm extension module I have.
- removed modules/civicrm
- put the new civicrm in modules/
- run drupal update.php
- ran ?civicrm/upgrade&reset=1

And then I got the next output. What can I do about this ?

Thanks, Rik

    Sorry. A non-recoverable error has occurred.

    DB Error: already exists

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

    Return to home page.

Error Details:

Code: [Select]

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

    [code] => -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => CREATE TABLE `civicrm_entity_financial_trxn` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `entity_table` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `entity_id` int(10) unsigned NOT NULL,
  `financial_trxn_id` int(10) unsigned DEFAULT NULL,
  `amount` decimal(20,2) NOT NULL COMMENT 'allocated amount of transaction to this entity',
  PRIMARY KEY (`id`),
  KEY `FK_civicrm_entity_financial_trxn_financial_trxn_id` (`financial_trxn_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_entity_financial_trxn' already exists]
    [type] => DB_Error
    [user_info] => CREATE TABLE `civicrm_entity_financial_trxn` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `entity_table` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `entity_id` int(10) unsigned NOT NULL,
  `financial_trxn_id` int(10) unsigned DEFAULT NULL,
  `amount` decimal(20,2) NOT NULL COMMENT 'allocated amount of transaction to this entity',
  PRIMARY KEY (`id`),
  KEY `FK_civicrm_entity_financial_trxn_financial_trxn_id` (`financial_trxn_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_entity_financial_trxn' 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_entity_financial_trxn` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `entity_table` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `entity_id` int(10) unsigned NOT NULL,
  `financial_trxn_id` int(10) unsigned DEFAULT NULL,
  `amount` decimal(20,2) NOT NULL COMMENT 'allocated amount of transaction to this entity',
  PRIMARY KEY (`id`),
  KEY `FK_civicrm_entity_financial_trxn_financial_trxn_id` (`financial_trxn_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci [nativecode=1050 ** Table 'civicrm_entity_financial_trxn' already exists]"]
)
[/quote][/code]

[Laryn]

I'm no expert, but a "table already exists" error seems to suggest that you ran this script already, at least up until this point in the script.

Taking a cue from Kurund in a database issue I ran into earlier, "can you check your civicrm_domain table for version?"

[mariagwyn]

rvanderh, did you resolve this?  I have a similar error (see below).  It seems that my query wants to modify a FK constraint but my db won't allow it.  I am mystified.

Error
<code>DB Error: a515ac9c2796ca0e23adbe92c68fc9fc

Error Details
Database Error Code: Error on rename of './scethics_liveCivi/civicrm_financial_trxn' to './scethics_liveCivi/#sql2-677-9efb2' (errno: 152), 1025
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_financial_trxn`
       DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id` [nativecode=1025 ** Error on rename of './scethics_liveCivi/civicrm_financial_trxn' to './scethics_liveCivi/#sql2-677-9efb2' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_financial_trxn`
       DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id` [nativecode=1025 ** Error on rename of './scethics_liveCivi/civicrm_financial_trxn' to './scethics_liveCivi/#sql2-677-9efb2' (errno: 152)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_financial_trxn`
       DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id` [nativecode=1025 ** Error on rename of './scethics_liveCivi/civicrm_financial_trxn' to './scethics_liveCivi/#sql2-677-9efb2' (errno: 152)]"]
)</code>

[jase700]

I have the exact same problem..  Any ideas on how to fix?  I went from 3.1.6 to 3.2.0.  Trying to get to 3.2.2 in steps.

[omarkish]

I also am having this exact problem upgrading from 3.1.4 to 3.2.0.  It would be great to get a solution!  

Thanks a lot

[Deepak Srivastava]

One recommended approach is to get your structure repaired by following instructions from - http://wiki.civicrm.org/confluence/display/CRMDOC32/Ensuring+Schema+Integrity+on+Upgrades and then go for upgrade.

And if you think you have run upgrade more than once because of the error, its a good idea to reload your database from backup and do above steps, just to be more safe.

Meanwhile we will try to investigate and figure out the points that could be triggering these issues.
May know what civicrm version you all started with and / or how many upgrades you already been through ?

thanks

[omarkish]

So I have tried upgrading from 3.1.4 to 3.2 twice now with the same result.  After the upgrade failed the first time, I tried the recommended approach of trying to reload the database so as to ensure the integrity of its structure, however I continued to receive error messages relating to "foreign key constraints". 

This morning I tried to run the upgrade again, but have had the same result.  Any other ideas?  Anyone else having this problem? 

The error says this:

---------------------------

DB Error: a515ac9c2796ca0e23adbe92c68fc9fc

Database Error Code: Error on rename of './cvfulb_dev/civicrm_financial_trxn' to './cvfulb_dev/#sql2-171c6-171' (errno: 152), 1025
Additional Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

Code: [Select]

=> a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_financial_trxn`
       DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id` [nativecode=1025 ** Error on rename of './cvfulb_dev/civicrm_financial_trxn' to './cvfulb_dev/#sql2-171c6-171' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_financial_trxn`
       DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id` [nativecode=1025 ** Error on rename of './cvfulb_dev/civicrm_financial_trxn' to './cvfulb_dev/#sql2-171c6-171' (errno: 152)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_financial_trxn`
       DROP FOREIGN KEY `FK_civicrm_financial_trxn_contribution_id` [nativecode=1025 ** Error on rename of './cvfulb_dev/civicrm_financial_trxn' to './cvfulb_dev/#sql2-171c6-171' (errno: 152)]"]
)

[CiviTeacher.com]

omarkish: FK constraints here may be the result of civicrm_financial_trxn referencing contributions in the civicrm_contributions table that no longer exist.

I just wanted to let all of you know I recently ran into several of the errors you mention here.  I believe the errors were as a result of a failed upgrade from 3.1.4 to 3.2.4.  When the upgrade failed, another programmer re-imported the backup data from 3.1.4 to "reset" the database.  

However, what they must not have realized is the (failed) upgrade to 3.2 changed the schema even though the upgrade failed.  When an upgrade fails, we really can't just re-import the data.  We must complete drop the failed database and recreate it.  If you don't have permissions for this....you must drop every single table in the old database (pushing through any FK errors while dropping) until the database is totally clean - only THEN should you re-import and backup.

Of course, a 'clean' backup didn't exist.  So I had to do this the hard way.

So I followed the "schema integrity" instructions and this was troublesome because a data dump contained tables that were not in the clean 3.1.4 schema.

These included three new tables introduced in 3.2:
civicrm_address_format
civicrm_entity_financial_trxn
civicrm_website

I had to remove these from the datadump during step 1 of the "schema integrity" instructions.  

[hallman]

This information was very helpful. I tried to redo an update from 3.1.4 to 3.2 and got this error message (Table 'civicrm_entity_financial_trxn' already exists). I tried going back to a backup from 3.1.4 but that didn't work. I ended up dropping ALL the civicrm tables to get rid of all those added by the earlier attempt to get to 3.2. Then put back in the 3.1.4 backup. And then I was able to upgrade to 3.2.4. At least it looks good so far.