Author Topic: Checking the Schema Integrity post-Upgrade (Read 4641 times)

bchoc · December 11, 2007, 06:22:17 pm

Today, after much delaying and at least one serious and failed attempt, I made another attempt to upgrade my CiviCRM from 1.6 to 1.9.

I wasn't sure about the order of operations for the dump-data step (http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades), so I tried both before and after approaches, like so:

Backup + Make a working copy
Run the 1.6->1.7 updater.
Run the 1.7->1.8 updater.
Run the 1.8->1.9 updater.
Dump the data only from the converted-to-1.9-DB, create a new DB, import the empty schema, import the data.

~alternatively~

Backup + Make a working copy
Run the 1.6->1.7 updater.
Dump the data only from the converted-to-1.7-DB, create a new DB, import the empty schema, import the data.
Run the 1.7->1.8 updater.
Run the 1.8->1.9 updater.

In either scenario, I ran into the Foreign Key Errors that were seemingly popular when 1.7 came out last Spring. Notably, the 1.6->1.7, etc. series of updaters executed without error, but the dump-data/import-schema/import-data process fails consistently.

I noticed that the table count matched between a blank 1.9 import and the 1.6 -> 1.9 updated DB, so I decided to just run the updaters and skip the dump/import process ... and it seems that the data is there and CiviCRM 1.9 works.

I've searched this forum pretty reasonably, I think. So I'm going to risk bothering folks with two questions.

First, is there a trouble-shooting process for dealing with foreign key errors? (Beyond this: http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrading+Drupal+Sites+from+v1.7+to+v1.8#UpgradingDrupalSitesfromv1.7tov1.8-ForeignKeyErrorsorWarningsDuringtheDatabaseUpgrade)

Second, what are the dangers of running a 1.6 -> 1.9 conversion without the dump/import process being run? Will it fail later or will I find errors as I dig into different menu items? Is there a diagnostic I do to catch problems with schema integrity? A manual fix?

Thanks,
Brian

Donald Lobo · December 12, 2007, 11:43:25 am

1. Most FK errors are probably name related. If you can normalize your schema at the 1.9 level that should be fine (IMO)

2. We currently dont have a schema integrity checker. We hope soemone from the community will step up and contribute one

lobo

bchoc · December 12, 2007, 01:32:49 pm

What is the procedure for ensuring the database is adhering to the schema? Perhaps there's a document out there I missed.

As I said, the upgrade SQL imports worked, but importing the data into an empty 1.9 (or 1.7 or 1.

database fails. I've found no way to get around the FK errors in the import.

I'd rather deal with the issues now than after more data is invested into a faulty base.

Donald Lobo · December 12, 2007, 03:10:51 pm

1. There is no document / procedure to check that the database is consistent and adheres to the schema

2. For the latest database, what errors do you get when u try to import the data. Also what procedure do u follow?

lobo

Dave Greenberg · December 12, 2007, 06:56:40 pm

Since you have your migrated DB and a fresh 1.9 install - might be useful to just take a "dump" of "structure only" for each and do a diff on the files.

bchoc · December 13, 2007, 05:08:32 pm

Lobo,

I've followed the pre-1.7 upgrade instructions here:
http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrading+from+Prior+Versions

Of course, I make a backup and work from a duplicate copy of the DB. I manage to run through each of the steps on that page above. I successfully run the civicrm_upgradedb (all 4.1 btw) scripts in sequence: 1.6->1.7, 1.7->1.8, and 1.8->1.9 using phpmyadmin. No problems.

Removing the old install files and putting the new ones up is no problem. At this point, I can actually run CiviCRM 1.9 via Drupal and access the old data. At a casual glance, it seems to work OK.

Where the problem comes in is here:
http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades

I create a new database and import civicrm_41.mysql into that empty database. I then do an export from the (now upgraded) old database of data only. OK so far. Now I come to the failing step. I attempt to import the data-only from the old (updgaded to 1.9) database into the structure of the new (empty 1.9 structure) database.

Quote

Error

SQL query:

--
-- Database: `t4tcolor_t4tCiviCRM19`
--
--
-- Dumping data for table `civicrm_acl`
--
--
-- Dumping data for table `civicrm_acl_cache`
--
--
-- Dumping data for table `civicrm_acl_entity_role`
--
--
-- Dumping data for table `civicrm_activity`
--
INSERT INTO `civicrm_activity`
VALUES ( 1, 2, 5, 'civicrm_contact', 226, 'TechLaunch Training', '2007-09-26 21:00:00', 3, 0, 'Mile High United Way', 'Rough Outline of Training Session:\r\nElements of Web Design: 1hr 15min\r\nQ&A: 10min\r\n\r\n10 min break\r\n\r\nCommunicating w/ Web Designers: 30min\r\nQ&A/Discussion: 15min\r\n\r\nEvaluating Hosting: 30min\r\nQ&A: 10min\r\n', 'Completed', NULL ) ;

MySQL said: Documentation
#1216 - Cannot add or update a child row: a foreign key constraint fails

So, I'm not confident that my database is sound, even though it appears to run. I'm not sure how to go about dealing with these foreign key errors.

Thanks,
Brian

Donald Lobo · December 13, 2007, 09:42:12 pm

Seems like this is an FK issue. Before loading your data, you might want to do

mysql> SET FOREIGN_KEY_CHECKS=0
mysql> "LOAD YOUR DATA HERE"

my syntax might be a bit off, but most likley this is what is causing the errors.

lobo

bchoc · December 14, 2007, 02:34:40 pm

When I add

Code: [Select]

SET FOREIGN_KEY_CHECKS=0; to the top of the import query, I get this error:

Quote

Error

SQL query:

--
-- Dumping data for table `civicrm_email`
--
INSERT INTO `civicrm_email`
VALUES ( 1, 1, 'webmaster@t4tcolorado.net', 1, 0, NULL , NULL , 0 ) ;

MySQL said: Documentation
#1048 - Column 'is_bulkmail' cannot be null

This is a different error. Is that an improvement?

Brian

bchoc · January 23, 2008, 07:26:53 pm

OK, I went through and corrected the error. The is_bulkmail was set to NULL for all of my records. I changed them all to 0, which appeared to be acceptable to the current configuration, and reimported. With Foreign Key checking turned off, it went through without a hitch. However, when I tried to fire up Drupal/Civi using the new, improved 1.9 DB ... I get this:

warning: array_key_exists() [function.array-key-exists]: The second argument should be either an array or an object in /home/t4tcolor/public_html/sites/all/modules/civicrm/CRM/Core/BAO/Setting.php on line 126.

Over and over in very unhappy looking red text in a red box.

Any suggestions on this one?
bc

Dave Greenberg · January 24, 2008, 10:03:54 am

It looks like the code is having a problem handling the current values in your civicrm_domain.config_backend column. You might try setting that column to NULL - which will force the code to rebuild the serialized values there from your settings file and the environment. Note that this will set configuration values to defaults so you'll need to review your Global Settings in the Admin UI afterwards and adjust as needed.

Assuming you a have a single domain site using the default domain ID of 1, they query for this is:

UPDATE civicrm_domain set config_backend = NULL where id = 1;

bchoc · January 24, 2008, 12:53:06 pm

Thanks David for your quick response. I feel like I'm getting closer to a successful, fully functional install.

I queried the values from 1 to NULL as you suggested, and then went in to reset all global settings. I ran into two sorts of problems.

First, two of the Global Settings pages -- Site Preferences and Addresses -- are missing fields. Under Address Editing -> Address Fields only the help text appears, no checkboxes. And on the Site Preferences page there are only help fields, no checkboxes at all (Viewing Contacts, Editing Contacts, etc).

Second, I am told that Events pages that were there before do not exist. And when I try to add a Participant Status Option, I get this very detailed error:

Code: [Select]

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    Database Error Code: Duplicate entry 'participant_status-1' for key 2, 1062

    Return to home page.

Error Details:

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

    [code] => -5
    [message] => DB Error: already exists
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_option_group (domain_id , name , is_active ) VALUES ( 1 , 'participant_status' ,  1 )  [nativecode=1062 ** Duplicate entry 'participant_status-1' for key 2]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_option_group (domain_id , name , is_active ) VALUES ( 1 , 'participant_status' ,  1 )  [nativecode=1062 ** Duplicate entry 'participant_status-1' for key 2]
    [to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_option_group (domain_id , name , is_active ) VALUES ( 1 , 'participant_status' ,  1 )  [nativecode=1062 ** Duplicate entry 'participant_status-1' for key 2]"]
)

I did look in the civicrm_event table to see if the IDs had reset during the import, but they are the same IDs so I'm not sure why the links are now failing.

Brian

[/code]

Dave Greenberg · January 24, 2008, 01:44:29 pm

I suspect you need to empty the template and session cache. There are instructions for running directoryCleanup and sessionRest in the 1.9 upgrade docs. I'd probably also truncate the Drupal sessions table and make sure anyone using the site has logged out and logged in fresh before trying to do any operations.

bchoc · January 25, 2008, 03:54:54 pm

Well, after clearing caches and doing the directoryCleanup, I'm getting the exact same situation. When I tried to view an Event, I get (turned on backtrace) --

Code: [Select]

backTrace

/home/t4tcolor/public_html/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 255
/home/t4tcolor/public_html/sites/all/modules/civicrm/CRM/Event/Page/EventInfo.php, fatal, 73
/home/t4tcolor/public_html/sites/all/modules/civicrm/CRM/Event/Invoke.php, run, 108
/home/t4tcolor/public_html/sites/all/modules/civicrm/CRM/Core/Component.php(183) : eval()'d code, main, 1
/home/t4tcolor/public_html/sites/all/modules/civicrm/CRM/Core/Component.php, eval, 183
/home/t4tcolor/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php, invoke, 144
/home/t4tcolor/public_html/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 319
, civicrm_invoke, 
/home/t4tcolor/public_html/includes/menu.inc, call_user_func_array, 418
/home/t4tcolor/public_html/index.php, menu_execute_active_handler, 15

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    The page you requested is currently unavailable.

    Return to home page.

Would it be easier, really, for me to reinstall CiviCRM 1.9 with a blank DB and try to import data in one table at a time? I feel like this DB may just irreparable.

bc

Dave Greenberg · January 27, 2008, 05:19:12 pm

Actually - looking at the code which is throwing the error - it could be a legitimate error. It "says" that the event you're trying to view is currently "disabled". You should be able to verify and correct this from the admin Manage Events >> Configure >> Event Information and Settings.

bchoc · February 01, 2008, 03:37:51 pm

Alas, we were starting to lose data and my feeling was the damage was done and I needed to cut the losses. So I started from a clean install and spent some serious time managing data from one database into the other.

On the up-side, I learned a lot about the tables in Civi.

bc

CiviCRM Community Forums (archive)

News:

Author Topic: Checking the Schema Integrity post-Upgrade (Read 4641 times)

bchoc

Checking the Schema Integrity post-Upgrade

Donald Lobo

Re: Checking the Schema Integrity post-Upgrade

bchoc

Re: Checking the Schema Integrity post-Upgrade

Donald Lobo

Re: Checking the Schema Integrity post-Upgrade

Dave Greenberg

Re: Checking the Schema Integrity post-Upgrade

bchoc

Re: Checking the Schema Integrity post-Upgrade

Donald Lobo

Re: Checking the Schema Integrity post-Upgrade

bchoc

Re: Checking the Schema Integrity post-Upgrade

bchoc

Re: Checking the Schema Integrity post-Upgrade

Dave Greenberg

Re: Checking the Schema Integrity post-Upgrade

bchoc

Re: Checking the Schema Integrity post-Upgrade

Dave Greenberg

Re: Checking the Schema Integrity post-Upgrade

bchoc

Re: Checking the Schema Integrity post-Upgrade

Dave Greenberg

Re: Checking the Schema Integrity post-Upgrade

bchoc

Re: Checking the Schema Integrity post-Upgrade