UPDATE...

this issue was inititally posted due to being unable to manually add memberships to a couple of organisation contacts that had been on the system for some years with previous memberships that had been added no problem. It seems there is also an issue with adding to individuals


Currently using Civi 5.5.1 on a Drupal 7.59 and php 7.1

I have an issue when adding memberships with debugging giving a Syntax error. This issue appears to be random and occurs both when adding a membership against a contact record both manually and through a bulk import.

To explain the randomness.

Yesterday (6th December 2018) we imported 2 groups of members from two seperate .csv files through using the contact import routine. Group 1 (24 contacts) and group 2 (28 contacts).

Group 1...we then imported a membership type (through .csv import) called MILO (which points to an organisation contact named MILO) with a start date of 1st December 2018. No problem.

Group 2 - FAILED...we then tried to import membership type (through .csv import) called GCSS (pointing to an organisation called GCSS) to group 2 with a start date 1st December 2018. Despite civi confirming the import was successful nothing shows on the individual contact record.

I then tried to add a GCSS membership manually (start date 1st December) to an individual in Group 2 - failed. Also failed with trying to add the MILO membership type.

If however I set the membership start date to after the contact created date (which is 6th January) then I can both manually and bulk add any membership.

Checking data - (civi and phpmyadmin)

  • I looked at the contact records for both data import groups and can't see any discernable difference.
  • Both 'membership types' look the same and bot have a relationship 'paid for by' assigned to them.
  • Both 'organisation'contact records don't seem to have any difference
  • I thought it might be related to tags added on contact import but having removed the tag nothing changes.
  • Following Pete's advice (thanks Pete) I have added the contacts to the civi demo site and then bulk added a membership with a start date 1st December with no problem.

Conclusion and help This is really quite weird it appears that I can add (post date) memberships to some contacts but not others. I thought this was perhaps due to a corrupt individual contact import but the initial issue I posted here was after being unable to add a membership against a couple of organisation contact records. Plus using the same .csv file things work fine on the civi demo site.

If anyone has any idea what has gone wrong here any advice most appreciated


Error message below

*backTrace
#0 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
#1 /home/ehfa/www/www/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/ehfa/www/www/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...")
#3 /home/ehfa/www/www/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...")
#4 /home/ehfa/www/www/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, 16, (Array:2), "\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...", "DB_Error", TRUE)
#5 /home/ehfa/www/www/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /home/ehfa/www/www/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-2, NULL, NULL, "\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...", "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /home/ehfa/www/www/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /home/ehfa/www/www/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...")
#9 /home/ehfa/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...")
#10 /home/ehfa/www/www/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...")
#11 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...")
#12 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/DAO.php(1411): CRM_Core_DAO->query("\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...", TRUE)
#13 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Member/Form/MembershipView.php(283): CRM_Core_DAO::executeQuery("\nSELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,\n...")
#14 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/Form.php(585): CRM_Member_Form_MembershipView->preProcess()
#15 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php(92): CRM_Core_Form->buildForm()
#16 /home/ehfa/www/www/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Display->perform(Object(CRM_Member_Form_MembershipView), "display")
#17 /home/ehfa/www/www/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Member_Form_MembershipView), "display")
#18 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("display")
#19 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Member/Page/Tab.php(253): CRM_Core_Controller->run()
#20 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Member/Page/Tab.php(370): CRM_Member_Page_Tab->view()
#21 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(309): CRM_Member_Page_Tab->run((Array:4), NULL)
#22 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:14))
#23 /home/ehfa/www/www/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#24 /home/ehfa/www/www/sites/all/modules/civicrm/drupal/civicrm.module(445): CRM_Core_Invoke::invoke((Array:4))
#25 /home/ehfa/www/www/includes/menu.inc(527): civicrm_invoke("contact", "view", "membership")
#26 /home/ehfa/www/www/index.php(21): menu_execute_active_handler()
#27 {main}
Sorry, due to an error, we are unable to fulfill your request at the moment. You may want to contact your administrator or service provider with more details about what action you were performing when this occurred.
DB Error: syntax error
Error Details
Database Error Code: 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 'AND m.contact_id = r.contact_id_a AND m.is_test = 0) LEFT JOIN civicrm_members' at line 8, 1064
Additional Details:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )
    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => 
SELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,
       rt.name_a_b as relation, r.start_date, r.end_date,
       m.id as mid, ms.is_current_member, ms.label as status
  FROM civicrm_relationship r
  LEFT JOIN civicrm_relationship_type rt ON rt.id = r.relationship_type_id
  LEFT JOIN civicrm_contact c ON c.id = r.contact_id_a
  LEFT JOIN civicrm_membership m ON (m.owner_membership_id = 
  AND m.contact_id = r.contact_id_a AND m.is_test = 0)
  LEFT JOIN civicrm_membership_status ms ON ms.id = m.status_id
 WHERE r.contact_id_b =  AND r.is_active = 1  AND c.is_deleted = 0 AND r.relationship_type_id IN (16) ORDER BY is_current_member DESC [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 'AND m.contact_id = r.contact_id_a AND m.is_test = 0)
  LEFT JOIN civicrm_members' at line 8]
    [type] => DB_Error
    [user_info] => 
SELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,
       rt.name_a_b as relation, r.start_date, r.end_date,
       m.id as mid, ms.is_current_member, ms.label as status
  FROM civicrm_relationship r
  LEFT JOIN civicrm_relationship_type rt ON rt.id = r.relationship_type_id
  LEFT JOIN civicrm_contact c ON c.id = r.contact_id_a
  LEFT JOIN civicrm_membership m ON (m.owner_membership_id = 
  AND m.contact_id = r.contact_id_a AND m.is_test = 0)
  LEFT JOIN civicrm_membership_status ms ON ms.id = m.status_id
 WHERE r.contact_id_b =  AND r.is_active = 1  AND c.is_deleted = 0 AND r.relationship_type_id IN (16) ORDER BY is_current_member DESC [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 'AND m.contact_id = r.contact_id_a AND m.is_test = 0)
  LEFT JOIN civicrm_members' at line 8]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT r.id, c.id as cid, c.display_name as name, c.job_title as comment,
       rt.name_a_b as relation, r.start_date, r.end_date,
       m.id as mid, ms.is_current_member, ms.label as status
  FROM civicrm_relationship r
  LEFT JOIN civicrm_relationship_type rt ON rt.id = r.relationship_type_id
  LEFT JOIN civicrm_contact c ON c.id = r.contact_id_a
  LEFT JOIN civicrm_membership m ON (m.owner_membership_id = 
  AND m.contact_id = r.contact_id_a AND m.is_test = 0)
  LEFT JOIN civicrm_membership_status ms ON ms.id = m.status_id
 WHERE r.contact_id_b =  AND r.is_active = 1  AND c.is_deleted = 0 AND r.relationship_type_id IN (16) ORDER BY is_current_member DESC [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 'AND m.contact_id = r.contact_id_a AND m.is_test = 0)
  LEFT JOIN civicrm_members' at line 8]"]
)
Return to home page.*
  • if as jon says your memberships are using inherited via relationships then it would be good to spell that out in your Question – petednz - fuzion Dec 3 at 17:00
  • i pasted your Answer in to your Question – petednz - fuzion Dec 3 at 22:38
  • Can you pls attach a screenshot of your membership types so that it makes it clear which use Relationships and which don't. – petednz - fuzion Dec 3 at 22:40
  • if you are feeling really lost. ping me on skype (peter.davis59) or fire me a hangout link and I could look over your shoulder – petednz - fuzion Dec 3 at 22:41
  • two other things to run your eye over. Your membership status (ie check that all the before/after totally butt up to each other). GIve the API Import a run – petednz - fuzion Dec 6 at 19:35

The issue here seems to be that the owner_membership_id of the relationship is blank. The owner_membership_id is for "inherited" relationships, and store the id number of the membership being inherited from.

To troubleshoot this, check for problems in your membership type configurations for those memberships that have inherited memberships, and the relationships that define the inherited memberships.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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