Upgrading from 4.2.8 to 4.3.5 ... not working

[pdelbar]

I'm doing an upgrade from 4.2.8 (in Joomla, but that's irrelevant) using the standard method as described. The code upgrade runs fine, but when I do the DB upgrade, I run into issues. The DB structure is clean, I compared it to a clean 4.2.8 install and it is identical.

First issue I have is that I get

ALTER TABLE `civicrm_financial_type` CHANGE `name` `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Financial Type Name.', ADD CONSTRAINT `UI_id` UNIQUE INDEX(id), DROP INDEX UI_name [nativecode=1091 ** Can't DROP 'UI_name'; check that column/key exists]

OK, I can fix that, reload the database, start again after fixing the indexes so this does not crash. Next, I get

ALTER TABLE `civicrm_entity_financial_account` ADD CONSTRAINT `FK_civicrm_entity_financial_account_financial_account_id` FOREIGN KEY (`financial_account_id`) REFERENCES `civicrm_financial_account` (`id`) [nativecode=1005 ** Can't create table 'staging_apopo_site_cms.#sql-82e6_313' (errno: 121)]

This is on a local machine with root privileges.

Has anyone else seen such behaviour ?

[Hershel]

Yes, sometimes such errors occur with upgrades. You can either trace through the code and find where that is and remove it OR you can try this: http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades

[benmoreassynt]

Hi - having an almost identical problem (I've run into this on my last two CiviCRM upgrades).

I've tried the solution on http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades which has worked for me in the past, however it is not working for me this time. I still run into the following error:

Code: [Select]

ALTER TABLE `civicrm_discount` ADD CONSTRAINT `FK_civicrm_discount_price_set_id` FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set` (`id`) ON DELETE CASCADE [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm-new`.`#sql-2c5c_996`, CONSTRAINT `FK_civicrm_discount_price_set_id` FOREIGN KEY (`price_set_id`) REFERENCES `civicrm_price_set` (`id`) ON DELETE CASCADE)
Being still somewhat unfamiliar with InnoDB constraints, could someone suggest to me how I can manually resolve such key conflicts? I've tried various solutions such as adding 'SET FOREIGN_KEY_CHECKS = 0', to the upgrade SQL but with no success.

[Hershel]

Seems to me that you have a missing price_set_id in the civicrm_price_set table. Did you delete one? Easiest thing might be to make a fake record manually, for the upgrade, and then remove it after.

[benmoreassynt]

Apologies for slow reply.

This was exactly the problem. Adding a 'fake' entry to the table referenced by the foreign key, using the correct key id, enabled a smooth upgrade to complete (using the default DB upgrade tool, rather than the workaround).

Many thanks.

[e_mason]

In upgrading a client's system from 4.2.16 to 4.5x I couldn't get the upgrade past the 4.3Alpha1 steps - there were several tables that failed the foreign key creation.  It turns out that the tables already existed, but were truncated with no records.  Thus the "Create if not exists" SQL statements  weren't run, thus no viable records for the foreign key creation.

By deleting tables, the upgrade could both create and populate the tables.  Once done, the upgrade proceeded.

The sql statements I used:

drop table civicrm_financial_type;
drop table civicrm_entity_financial_account;
drop table civicrm_financial_item;