Upgrade to 4.3.1 caused multiple errors and unusable system

[Michael]

True enough, Eileen. However, I'm completely stalled with the upgrade process at tis point. We are using Drupal and Drupal entities ... is it possible there is some sort of interaction going on with additional tables we've added for Drupal operations that create views using some of the CiviCRM data that is causing the issue, do you suppose?

Any suggestions for things to look at or try will be appreciated and followed up on.

[Eileen]

Michael - you might want to go onto IRC to discuss it - do you know how to do that? You should get a quicker turn around of feedback

[Michael]

Hi Eileen, thanks ... I'll try IRC ... never done it here before, though. Can you point me in the right direction as to how and protocols, please? That would be great!

[Eileen]

There is a link to IRC via the web @ the top of the page - or you can get an IRC client

The room is #civicrm

Basically go on & ask if anyone can help you with the upgrade problem & link to the forum. Don't ask if you can ask - just put the question there & if people can help they will respond

[Donald Lobo]

we need a complete output of:

SHOW CREATE TABLE civicrm_financial_trxn;

can you run that on your 4.2.x database which has not been upgraded and cut-n-paste that output here

lobo

[Michael]

Hi Lobo, I re-loaded the 4.2.8 db we had and ran: SHOW CREATE TABLE civicrm_financial_trxn;

Here is the result:

civicrm_financial_trxn
CREATE TABLE `civicrm_financial_trxn` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Gift ID',
 `from_account_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to financial_account table.',
 `to_account_id` int(10) unsigned DEFAULT NULL COMMENT 'FK to financial_account table.',
 `trxn_date` datetime NOT NULL,
 `trxn_type` enum('Debit','Credit') COLLATE utf8_unicode_ci NOT NULL,
 `total_amount` decimal(20,2) NOT NULL COMMENT 'amount of transaction',
 `fee_amount` decimal(20,2) DEFAULT NULL COMMENT 'actual processor fee if known - may be 0.',
 `net_amount` decimal(20,2) DEFAULT NULL COMMENT 'actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.',
 `currency` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '3 character string, value from config setting or input via user.',
 `payment_processor` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'derived from Processor setting in civicrm.settings.php.',
 `trxn_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'unique processor transaction id, bank id + trans id,... depending on payment_method',
 `trxn_result_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'processor result code',
 PRIMARY KEY (`id`),
 UNIQUE KEY `UI_ft_trxn_id` (`trxn_id`),
 KEY `FK_civicrm_financial_trxn_from_account_id` (`from_account_id`),
 KEY `FK_civicrm_financial_trxn_to_account_id` (`to_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[Donald Lobo]

if i had to guess, your DB was MyISAM at some stage and does not have the constraints that civi is expecting

a show create table on a 4.2 DB on my machine shows, these lines are:

Code: [Select]

  CONSTRAINT `FK_civicrm_financial_trxn_from_financial_account_id` FOREIGN KEY (`from_financial_account_id`) REFERENCES `civicrm_financial_account` (`id`),
  CONSTRAINT `FK_civicrm_financial_trxn_payment_processor_id` FOREIGN KEY (`payment_processor_id`) REFERENCES `civicrm_payment_processor` (`id`) ON DELETE SET NULL,
  CONSTRAINT `FK_civicrm_financial_trxn_to_financial_account_id` FOREIGN KEY (`to_financial_account_id`) REFERENCES `civicrm_financial_account` (`id`)

Seems like u need to check your schema against a standard 4.2.x schema and see how different it is.

lobo

lobo

[Michael]

Hi Lobo,

Thanks, I had seen that one reference to MyISAM table and asked someone about it a couple of weeks ago. I can check the schema (as best I can) but this is a standard Drupal installation using the default Drupal DB and CiviCRM installation. We have added a few tables (for Drupal use) but we've never altered the database schema from the installation defaults in any way. As far as I know the DB was never anything but the standard MySQL db since it's inception 6 months ago.

We did change website hosts a couple of times in that time period but we simply exported the DB and copied the site files to move from host to host. We never altered the DB from the defaults, especially not any of the default Drupal or CiviCRM tables or fields.

Is there anything specific I can check or is there a procedure I can follow and report on here that will help?

[Michael]

Addendum: When we transferred the site from the old host to the new host I saw that the civicrm_view_case_activity_upcoming table was listed as MyISAM, although I think it's the only table that was. The rest were all listed as InnoDB. Hershel suggested it probably wasn't something to worry about and shouldn't affect the upgrade. However, it's the first time I remember seeing any reference the MyISAM in the DB.

[Michael]

This statement:

"A note on possible Foreign Key Constraint Violation errors during import (and what to do about them): You may receive Foreign Key constraint violation errors on import because of the order in which the tables are exported and then imported, which is alphabetical.
For example, data for the civicrm_activity_assignment table would be imported before data for the civicrm_contact table, violating the Foreign Key constraint defined on the civicrm_activity_assignment.assignee_contact_id field. You can avoid this by instructing MySQL to disable Foreign Key checks before the import and to re-enable it when the import is complete. "

... is from this page:

http://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades

Could the problem be as simple as the imported table and fields being out of alphabetical order? I can try to follow the instructions on that page and see what happens although it seems a bit drastic for the average upgrade to require these extra steps.

[Michael]

Hi Lobo, I looked at the schema in Drupal and this does not exist as far as I can tell: FOREIGN KEY civicrm_financial_trxn_ibfk_2

... so it make sense that this is failing:

ALTER TABLE civicrm_financial_trxn DROP FOREIGN KEY civicrm_financial_trxn_ibfk_2

 ... since it's not there it can't be dropped.

Is that what you were asking to be done, please?

[Donald Lobo]

At this point, i suspect you will need to get someone more familiar with mysql and civicrm to get involved and figure out whats happening on your site and why

lobo

[Michael]

Thanks, Lobo. I am pretty convinced at this point there was a flaw in consecutive CiviCRM upgrades due to the coding of CiviCRM and that messed up my DB over time. We don't have the money to hire people to fix things like this, so I'm stuck in the middle.

I will work with my client and decide whether we wait for a future version release or abandon CiviCRM altogether for the time being.

Your help is sincerely appreciated.

[Mark Tompsett]

Hi Michael

I realise that it has been a few weeks since you last posted to this thread (I sensed the frustration that you felt) and I may not necessarily be quite the "someone more familiar with mysql and civicrm" that Lobo encouraged you to try to find, but I had the exact same problem and it took me a while to figure out a solution but I did come up with one.

In my case I was trying to upgrade a CiviCRM 4.2.6 installation to 4.3.4, but I ran into exactly the same problem.  It turned out that my CiviCRM database (for some reason) had lost all of its FK constraints.  Rather than try to add them in to an existing database, what I did was to create a virgin 4.2.6 install and (using phpMyAdmin) I exported just the structure of the database to an SQL file, ie I now had just the database structure of a 4.2.6 install.  Next I exported just the data of my live database to another SQL file.  Next I deleted and recreated my live database (so that it was empty, ie had no tables, but the MySQL user had the necessary rights to the database).  Into this newly created I imported the structure of the virgin 4.2.6 install, so that I had a database with all the necessary tables and FK constraints, but no data at all, ie all the tables were empty.  Lastly I inserted some lines at the top of my SQL data file to suppress FK checking, etc

Code: [Select]

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;and these lines at the bottom

Code: [Select]

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;so that the data could be imported without encountering foreign key constraint violations, etc.  (Just to confirm, I ran all of the imports and exports using phpMyAdmin.)
I was then able to successfully upgrade my database to 4.3.4.

Hopefully this might be of help to you (or someone else who encounters a similar problem).

Mark   

PS  Naturally, before I did any of this, I took a full backup of my databases and code (I always do this before attempting any upgrade), just in case something went wrong.

[Michael]

Hi Mark, thanks for your thoughtful and informed reply. It is very much appreciated. Your description of the problem and workaround are excellent and very useful. (Nice to know I'm not alone in this experience.)

I think what happened in our case was a twofold issue (as I understand it, anyway). We had moved the website twice between different web hosts by the usual method of doing a full backup of all files and a MySQL dump then uploading/importing everything to the new host. It seems that at some point in the process at least one of the civicrm tables got converted from InnoDB to MYISAM although I have no idea how or why that happened. CiviCRM didn't like it and the table in question had to be converted back to InnoDB.

The second issue is that when upgrading and performing the MySQL dump I was unaware of the need to drop the foreign key constraints and this caused some issues. When we upgrade CiviCRM or Drupal we always perform the process with two people with one person following all the latest instructions from this site and directing the other person who performs the actual work. We do this to ensure nothing is missed and there are no unrecorded anomalies. We always make full backups just prior to upgrading so we can roll back if necessary. We've been performing every civicrm (and drupal) upgrade since version 4 was first released and never had a problem until this time. How the necessity of dropping foreign key constraints eluded us I do not know. (However I was extremely ill at the time so maybe I missed something in the documentation.) I've been running CiviCRM sites for several years and this is the first jackpot like this I got myself into I couldn't figure out.

Hopefully this information from our combined experiences will help others. The takeaway here is beware of foreign key constraint requirements when upgrading and always check your tables for inadvertent conversion from InnoDB to MyISAM when transferring  civicrm website between hosting providers.