CiviCRM Community Forums (archive)

I'm having a problem similar to here: http://forum.civicrm.org/index.php/topic,10817.0.html, details posted below. I've tried the upgrade after clearing out the ghost entries in civicrm_uf_match, following the advice of that posting, and even tried truncating the civicrm_uf_match table, but still get the same error. I also tried to upgrade only to 3.0 first. After each one, i'm restoring the database to a pre-upgrade copy (deleting the whole database to ensure there are no stray tables left).

BUT now that I go in and look at the error log file (in files/civicrm/upload), I see that I only got the error logged once (and only for the 3.0 upgrade).

SO: is it possible that i need to clear a session or something before retrying the upgrade? That'd be weird, since I'm restoring the database to exactly the state pre-upgrade, so the sessions table shouldn't have any information about the upgrade failure, so I'm not sure why it's not logging all the times I failed to upgrade ...

OR: is there another table that may have a FK constraint on uf_match? I did end up finding the error of FK constraints with the contacts table and removed those from the uf_match table (about 20 of them). There are about 2000 contacts, most of them with Drupal users. There are two drupal - civicrm sync modules enabled (og and roles), and I wondered whether they might have caused some FK constraint errors, though I tried some clever SQL to identify all the FK constraints on the uf_match table and didn't find any. Perhaps those get added during the upgrade to 3.0 and then the failure happens?

In any case, here is the error in the log file, which is the same error I got on the screen:
Feb 04 16:30:49  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_uf_match`
        DROP FOREIGN KEY `FK_civicrm_uf_match_contact_id`,
        DROP INDEX `UI_uf_name` ,
        DROP INDEX `UI_contact` [nativecode=1025 ** Error on rename of './civicrm_ftthcouncil_org/civicrm_uf_match' to './civicrm_ftthcouncil_org/#sql2-6f3a-c4431' (errno: 152)]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_uf_match`
        DROP FOREIGN KEY `FK_civicrm_uf_match_contact_id`,
        DROP INDEX `UI_uf_name` ,
        DROP INDEX `UI_contact` [nativecode=1025 ** Error on rename of './civicrm_ftthcouncil_org/civicrm_uf_match' to './civicrm_ftthcouncil_org/#sql2-6f3a-c4431' (errno: 152)]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm_uf_match`
        DROP FOREIGN KEY `FK_civicrm_uf_match_contact_id`,
        DROP INDEX `UI_uf_name` ,
        DROP INDEX `UI_contact` [nativecode=1025 ** Error on rename of './civicrm_ftthcouncil_org/civicrm_uf_match' to './civicrm_ftthcouncil_org/#sql2-6f3a-c4431' (errno: 152)]"]
)


Feb 04 16:30:49  [info] $backTrace = /var/www/civicrm/3.0/CRM/Core/Error.php, backtrace, 190
, handle,
/var/www/civicrm/3.0/packages/PEAR.php, call_user_func, 912
/var/www/civicrm/3.0/packages/DB.php, PEAR_Error, 969
/var/www/civicrm/3.0/packages/PEAR.php, DB_Error, 574
/var/www/civicrm/3.0/packages/DB/common.php, raiseError, 1903
/var/www/civicrm/3.0/packages/DB/mysql.php, raiseError, 898
/var/www/civicrm/3.0/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/civicrm/3.0/packages/DB/common.php, simpleQuery, 1216
/var/www/civicrm/3.0/CRM/Utils/File.php, query, 236
/var/www/civicrm/3.0/CRM/Upgrade/Form.php, sourceSQLFile, 109
/var/www/civicrm/3.0/CRM/Upgrade/Form.php, source, 231
/var/www/civicrm/3.0/CRM/Upgrade/Form.php, processLocales, 247
/var/www/civicrm/3.0/CRM/Upgrade/ThreeZero/ThreeZero.php, processSQL, 115
/var/www/civicrm/3.0/CRM/Upgrade/Page/Upgrade.php, upgrade, 271
/var/www/civicrm/3.0/CRM/Upgrade/Page/Upgrade.php(118) : eval()'d code, upgrade_3_0_alpha1, 1
/var/www/civicrm/3.0/CRM/Upgrade/Page/Upgrade.php, eval, 118
/var/www/civicrm/3.0/CRM/Core/Invoke.php, run, 209
/var/www/civicrm/3.0/drupal/civicrm.module, invoke, 333
, civicrm_invoke,
/var/www/drupal/pressflow-6/includes/menu.inc, call_user_func_array, 348
/var/www/drupal/pressflow-6/index.php, menu_execute_active_handler, 18


So, thanks to the dbtools, this finally worked.

1. The dbtools repair did most of the work. I had to run it about 4 times.

2. The dbtools analysis actually gave me some wrong information about a table that it claimed was not in the schema but was, but it also identified some other tables (the import_job tables) that I was able to get rid of.

3. There was an additional issue similar to another one on the forum about duplicate indices in the activity table, so I had to manually remove those after running the repair.

After all that, it did a full upgrade from 2.2.8 to 3.2.5.

As a nice side benefit, the size of the database shrunk to less than a quarter!

My take away: run dbtools on old/upgraded databases of uncertain provenance.

And big thanks to dharmatech!