1

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

  • Have you checked to make sure your server has a compatible version of PHP? – Christia Dec 20 '16 at 0:27
  • Yes, compatible PHP version checked, even tried in 2 different servers thinking that was the issue and a XAMPP server and the same happens – Guillermo Ricci Dec 21 '16 at 1:36
  • Can you update your post with what version of MySQL and Joomla you are running? mysql --version – Christia Dec 21 '16 at 1:49
  • wiki.civicrm.org/confluence/display/CRMDOC/… Joomla version needs to be higher than 3.x in order to upgrade and run CiviCRM 4.x versions and MySQL version must be 5.1.x or higher. – Christia Dec 21 '16 at 21:22
0

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.

  • Thank you, that change did help and as you said I've been looking for what is wrong with the database all this time and couldn't find anything at all(also updated the MySQL and Joomla versions) – Guillermo Ricci Dec 22 '16 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.