3.0.0>3.4 database upgrade script fails

[minbori]

Hello, trying to upgrade a Joomla 1.5.23 install from 3.0.0 to 3.4, and am following the instructions at http://wiki.civicrm.org/confluence/display/CRMDOC40/Upgrade+Joomla+Sites+to+3.4  Everything seems to go fine until step 4. Running the Database Upgrade script returns the following error:

"Sorry. A non-recoverable error has occurred.  One of parameters (value: Already Registered) is not of the type Float."

I'm guessing the database upgrade script is choking on a type mismatch somewhere in the data -- I've had a look through my data backup and found two tables where columns exist with the value 'Already registered':

table civicrm_option_value, columns LABEL and NAME 
table civicrm_line_item, column LABEL

Help! Anyone have suggestions as to where I go next?  I've looked on the forum and not found a similar post, and I'm not sure how best to troubleshoot from this point.

[minbori]

First a correction: just noticed that in my original post I only said v3.4 -- specifically I'm trying to upgrade to v3.4.4 .

Soldiering on, I started searching for anything on the forums related to database troubleshooting, came across the database tools (civicrm_dbtools-1.2) and ran them on my original (pre-upgrade-attempt) 3.0.0 database.  They reported 116 errors some of which pertained to the offending tables, so I ran "repair" until I got a functional CiviCRM 3.0.0 with no problems (according to the tools at least).

Once more attempted the upgrade and at least this time got an error I found forum references for:

Sorry. A non-recoverable error has occurred.
DB Error: already exists
Error Details
Database Error Code: Duplicate key name 'index_medium_id', 1061
Additional Details: ....

I will try the forum post http://forum.civicrm.org/index.php/topic,15148.0.html suggestion of dropping the three indexes and attempt the upgrade again.

[minbori]

On the latest attempt:  ran repair to fix the 116 errors in the database (which added in the offending indexes, by the way -- they didn't exist before I ran the repair tool), dropped the three indexes, then ran the database upgrade script again.

I've come full circle -- back to this error:

CiviCRM-001 - An error has occurred.
Sorry. A non-recoverable error has occurred.
One of parameters (value: Already Registered) is not of the type Float

Possible desperation options:  try rebuilding the schema before trying the upgrade (http://wiki.civicrm.org/confluence/display/CRMDOC40/Ensuring+Schema+Integrity+on+Upgrades)??  Doing incremental upgrades (3.0->3.1->3.2 etc...)??

Someone please help, I'm really running out of ideas    I just can't believe the upgrade is this difficult...I've got exactly TWO custom tables -- neither of which seems to be causing the trouble!

[Hershel]

It's hard to say what your issue is without examining the DB. I would suggest you try your "Possible desperation options" and let us know what happens.

[minbori]

Thanks for the reply, Hershel :-)  I understand that the database issues can be complex...but I just can't believe our database is so "out of schema".  We have a very standard install, not using CiviMail, CiviContribute, etc. -- we basically only use CiviEvent.  And as I said, only two custom data tables (created through the "Custom Data" configuration), with no front-end or application customizations at all.  Granted, I inherited maintenance of this install, so I can't vouch exactly for the setup procedures ;-)

I'll try both "desperation options" and update as to how they go.

Apologies if these are stupid questions, but I'm still learning CiviCRM.  Are these database issues a common problem when one tries to upgrade from such an old version directly to the current version (are the schemas that different)?  Is it common practice to clean up/rebuild the database schema before an upgrade? And which is the better way to go in terms of bringing the database back to schema standard?  The documented schema rebuild, or the database tools? Or both?

[minbori]

Yikes. Just came across this post http://forum.civicrm.org/index.php/topic,18186.0.html

Seems he had the same troubles as I have.  Is a manual upgrade the only option?

[Hershel]

> so I can't vouch exactly for the setup procedures

The issue can be MySQL-related. Check that all tables are InnoDB.

> Is it common practice to clean up/rebuild the database schema before an upgrade?

Only for versions before 3.0 generally, and then only sometimes.

> And which is the better way to go in terms of bringing the database back to schema standard?  The documented schema rebuild, or the database tools? Or both?

Whichever will work.

> Is a manual upgrade the only option?

Try the other options first.

[minbori]

>The issue can be MySQL-related. Check that all tables are InnoDB.

Thanks for the suggestion...but all civicrm schema tables are InnoDB. There are some stray Joomla tables that are MyISAM, but that's a problem for another day...

As for the other options, at least I'm getting consistent behavior.

First attempt --  rebuilt 3.0.0 schema, installed 3.4.4, ran db upgrade script: got the "not of type float" error as before.

Second attempt -- rebuilt 3.0.0 schema, then used dbtools to correct all reported errors, installed 3.4.4, db upgrade script returned the "Duplicate key name 'index_medium_id'" error. (I think the dbtools repair put in this index, but perhaps the data integrity corrections cleared the float issue?)

So, off to try the incremental backup option 
 

[Dave Greenberg]

I did a grep on index_medium_id in the codebase and found this reference:

./Incremental/sql/3.2.1.mysql.tpl:32:ADD INDEX index_medium_id (medium_id),

Might be worth just commenting out that line and trying the automated upgrade script again. With some luck maybe that's the only error condition stopping things from completing.

[minbori]

Thanks for the suggestion Dave...at least now I know exactly where to find the incremental sql scripts :-)  But it seems the index_medium_id is not the only issue :-(

The incremental upgrade process has been insightful.  Set up my test environment with 3.0.0 and rebuilt the 3.0.0 schema, ran dbtools to repair the database, then started upgrading one version at a time. Got through 3.1.1, 3.2, 3.2.1, 3.2.3, and 3.2.4 without problems. Running the 3.2.5 upgrade script I hit the "index_medium_id exists" error, so I reimported the 3.2.4 database, dropped the three indexes mentioned in http://forum.civicrm.org/index.php/topic,15148.0.html (index_medium_id, index_is_current_revision, index_is_deleted), and successfully upgraded to 3.2.5.

The upgrade from 3.2.5 to 3.3.0 now fails with the same "float" db error I saw before:
Sorry. A non-recoverable error has occurred.
One of parameters (value: Already Registered) is not of the type Float
Return to home page.

So, this problem seems to exist in the upgrade from 3.2.5 to 3.3.0, and has something to do with the sql on tables civicrm_option_value or table civicrm_line_item -- the only tables with the data value "Already Registered" in them.

Progress, but still not success 

[minbori]

Just an update if anyone's interested....have identified the source of the float error, but not what's causing it.

I couldn't find any problem on the database side, so I captured the backtrace of the float error.  It's thrown by the upgrade script:

backtrace
/www.foo.com/administrator/components/com_civicrm/civicrm/CRM/Core/Error.php, backtrace, 270
/www.foo.com/administrator/components/com_civicrm/civicrm/CRM/Utils/Type.php, fatal, 268
/www.foo.com/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php, validate, 922
/www.foo.com/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php, composeQuery, 859
/www.foo.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/ThreeThree.php, executeQuery, 216
/www.foo.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Page/Upgrade.php, upgrade_3_3_beta1, 210
/www.foo.com/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php, run, 219
/www.foo.com/administrator/components/com_civicrm/admin.civicrm.php, invoke, 77
/www.foo.com/administrator/components/com_civicrm/admin.civicrm.php, civicrm_invoke, 45
/www.foo.com/libraries/joomla/application/component/helper.php, require_once, 162
/www.foo.com/administrator/includes/application.php, renderComponent, 136
/www.foo.com/administrator/index.php, dispatch, 67
/www.foo.com/administrator/index2.php, include, 14

I inserted some code into DAO.php to output the queries it built and reran the script.  Confirmed that the problem was an attempted update of table civicrm_line_item:

Code: [Select]

QUERY ----> UPDATE civicrm_line_item SET price_field_value_id = %1 WHERE label = %2 AND unit_price = %3
ITS PARAMETERS --> Array ( [1] => Array ( [0] => 33 [1] => Integer ) [2] => Array ( [0] => Already Registered [1] => String ) [3] => Array ( [0] => Already Registered [1] => Float ) )
I checked the source data table and it is correct -- there is no row where the UNIT_PRICE is the string "Already Registered".  There are however six rows there the LABEL column is "Already Registered". So it seems that for some reason the script is inserting the wrong value into the third query parameter for these rows, and the type validation in Type.php dies when it sees that the string isn't in fact a float.

The code that's building the bad queries is in ThreeThree.php line 216:

Code: [Select]

       // update civicrm_line_item for price_field_value_id.
        // Used query to avoid line by line update.
        if ( $labelFound || $priceFound ) {
            $lineItemParams  = array( 1 => array( $fieldValueDAO->id, 'Integer' ),
                                      2 => array( $opValueDAO->label, 'String'  ),
                                      );                                           
            $updateLineItems = "UPDATE civicrm_line_item SET price_field_value_id = %1 WHERE label = %2";
            if ( $priceFound ) {
                $lineItemParams[3] = array( $opValueDAO->name, 'Float' );
                $updateLineItems .= " AND unit_price = %3";
            }
            CRM_Core_DAO::executeQuery( $updateLineItems, $lineItemParams );
        }
Several successful update queries are run against this table until this query dies; they have the correct values in the parameters. 

Possible short term solution:  I could attempt this same SQL update by hand and comment out this code before I run the upgrade script.

Longer term issue:  why are these queries being built wrongly?

[Donald Lobo]

most likely the bug is here:

            if ( $priceFound ) {
                $lineItemParams[3] = array( $opValueDAO->name, 'Float' );

Seems like the value should be in the value field and not in the name field?

BUT, i dont know that code well enough, so would be great if you can instrument and add some debugging statements and see what opValueDAO is

thanx

lobo

   

[minbori]

Thanks for taking an interest Lobo, this is quite the upgrade experience    I can report at least one success...

The GOOD news:  I did in fact do some debugging on the DAO and related statements and figured out that this problem is in fact a DATA problem, NOT a CODE problem.  A price set row in the civicrm_option_value table had an option value that was a string rather than a "monetary value".  Not sure how that happened, since I checked in the 3.0.0 application and it doesn't let you save price set values that aren't numeric (though the database doesn't validate that, and the price set monetary value is stored as a string in at least in the 3.0.0 version of the schema). So that was the errant string value showing up in the "name" parameter of the opValueDAO.  An error well caught, though not so well reported 

The BAD news:  Reset everything, made the data correction, and tried another upgrade.  Behold the next DB error.

 DB Error: already exists
    [mode] 16
    [debug_info] ALTER TABLE `civicrm_line_item` ADD UNIQUE INDEX UI_line_item_value (`entity_table`,`entity_id`,`price_field_value_id`,`price_field_id`) [nativecode=1062 ** Duplicate entry 'civicrm_participant-19-11-8' for key 'UI_line_item_value']

Next step is similar troubleshooting for this error...these two tables have become the bane of my existence 

[minbori]

Luckily for me the latest "already exists" DB error on the UI_line_item_value was already a known bug that was fixed in releases after 3.3.0.  (See forum post http://forum.civicrm.org/index.php/topic,16920.0/topicseen.html ). When I instead tried an upgrade from my functional 3.2.5 install directly to 3.4.4, it seems to have run smoothly. 

I'm now going to try putting all this info together for a clean upgrade from 3.0.0 to 3.4.4....

[minbori]

Just to tie up loose ends for you guys who have followed this thread, I now have a completely functional live 3.4.4 version! 

Finding and correcting the one data problem allowed me to run the 3.4.4 upgrade directly on the live 3.0.0 site.  There were a few little hiccups that required some config tweaking (for example I had something similar to this: http://forum.civicrm.org/index.php/topic,20426.msg85452.html#msg85452 ), and the nifty database tools still seem to be finding a few schema problems, but overall things seem to be working just fine.

My best advice to anyone who has found this thread because of a "float error" or other datatyping error during upgrade:  crack open the /administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php file and put in some code to output the actual queries being run against the database. That was most helpful to me in figuring out where the problem data was causing the db upgrade script to fail.

Thanks again everyone!