Upgrade from 4.2.7 to 4.7.13 fails during DB 4.4alpha1 update Joomla

Question

The upgrade process keeps failing, I even tried doing upgrades version after version but always when getting to 4.4 it fails when we get the 4.4 alpha1 update, the error that shows is:

[Error: Consolidate activity contacts]
Error Field Error Value
Type    DB_Error
Code    -2
Message DB Error: syntax error
Mode    16
UserInfo    UPDATE civicrm_option_value SET is_reserved = 1 WHERE option_group_id = 62 AND value IN (2, , 3) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 3)' at line 3]
DebugInfo   UPDATE civicrm_option_value SET is_reserved = 1 WHERE option_group_id = 62 AND value IN (2, , 3) [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 3)' at line 3]
PEAR_Exception: DB Error: syntax error in unknown on line unknown
- DB_Error: DB Error: syntax error in unknown on line unknown

I keep looking for the error but can't find it, does anyone knows a better way to verify it? Or if you know what error is on this table? (I only left the main columns and deleted the rest)

https://dl.dropboxusercontent.com/u/92433965/civicrm_option_value.csv

Server 1 PHP:5.6.8 MySQL:5.6.24

Server 2 PHP:5.6.28-0+deb8u1 MySQL:5.5.53-0+deb8u1

Answer 1

I see where the problem is happening and I have a solution, but from the CSV you're sharing, I can't see why the problem is happening.

Next time, before upgrading, go to your CiviCRM root and then open CRM/Upgrade/Incremental/php/FourFour.php. Around line 543 will be a function called activityContacts(). CiviCRM changes how activity contacts are stored in version 4.4+, and this function is meant to facilitate that.

The first several lines are meant to look up the option value records labeled "Activity Source", "Activity Assignees" and "Activity Targets" and set them to be "reserved", so they can't be changed. Based on the SQL in your error, it's not finding "Activity Assignees" - but I don't understand why, your CSV looks fine.

Regardless - you can fix this by ignoring the lookups and manually change the SQL query to use the values that I can see in your CSV file.

Change these lines:

    $query = " 
UPDATE civicrm_option_value
SET    is_reserved = 1
WHERE  option_group_id = {$optionGroupID} AND value IN ($ovValues)";

to this:

    $query = " 
UPDATE civicrm_option_value
SET    is_reserved = 1
WHERE  option_group_id = {$optionGroupID} AND value IN (1, 2, 3)";

Re-run the upgrade and you should get past that error.