[resolved] DB Syntax Error in 4.5.0 (WordPress) when new member info confirmed

[rd11]

After upgrading to 4.5.0 on WordPress I am having an issue with new users creating memberships---the issue might have existed before, but I am just discovering it now after testing.

Basically, everything works fine until users confirm their information and click on Continue to PayPal.  Once that happens, users see a fatal syntax error rather than getting directed to the payment processor.  Notably, this only happens for memberships, and not for anonymous contributions. 

Full error below, but I believe the problem is with the part of the SQL query that reads "AND ( status_id NOT IN ( 5,) )".

Is that extra comma after the "5" throwing things off?

Code: [Select]

$Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT *
 FROM civicrm_membership
 
 WHERE ( is_test IS NULL OR is_test = 0 )  AND ( status_id NOT IN ( 5,) ) AND ( membership_type_id IN (1,2,3,4,5,6,7) ) AND (  civicrm_membership.contact_id = 1887 ) AND (  civicrm_membership.membership_type_id = 3 )
 
 
 ORDER BY start_date 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 ( membership_type_id IN (1,2,3,4,5,6,7) ) AND (  civicrm_membership.cont' at line 4]
    [type] => DB_Error
    [user_info] =>  . . .
    [to_string] => . . .
)


[Dan C]

That extra comma isn't extra, it implies that a value in the query is missing.  Did you change or rename one of the default contribution statuses?

[rd11]

Not to my knowledge, but there are other users on the system. 

I've been looking through the settings and am not even sure where you would edit/delete/etc. contribution statuses?

The options provided when I try to add a contribution manually are:

1. Completed
2. Pending
3. Cancelled
4. Failed
5. Partially Paid
6. Pending Refund

Does that look right?

[Eileen]

Turn on debugging & then grab the error backtrace

[rd11]

Backtrace:

Code: [Select]

Sep 28 21:38:55  [info] $backTrace = #0 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\Error.php(239): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\DB.php(975): PEAR_Error->PEAR_Error("DB Error: syntax error", -2, 16, (Array:2), "SELECT * \n FROM civicrm_membership \n \n WHERE ( is_test IS NULL OR is_test ...")
#4 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\PEAR.php(564): DB_Error->DB_Error(-2, 16, (Array:2), "SELECT * \n FROM civicrm_membership \n \n WHERE ( is_test IS NULL OR is_test ...")
#5 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\DB\common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "SELECT * \n FROM civicrm_membership \n \n WHERE ( is_test IS NULL OR is_test ...", "DB_Error", TRUE)
#6 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\DB\mysql.php(899): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\DB\mysql.php(328): DB_mysql->mysqlRaiseError()
#8 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\DB\common.php(1216): DB_mysql->simpleQuery("SELECT * \n FROM civicrm_membership \n \n WHERE ( is_test IS NULL OR is_test ...")
#9 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\DB\DataObject.php(2442): DB_common->query("SELECT * \n FROM civicrm_membership \n \n WHERE ( is_test IS NULL OR is_test ...")
#10 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\DB\DataObject.php(441): DB_DataObject->_query("SELECT * \n FROM civicrm_membership \n \n WHERE ( is_test IS NULL OR is_test ...")
#11 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Member\BAO\Membership.php(1010): DB_DataObject->find(TRUE)
#12 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Member\BAO\Membership.php(2331): CRM_Member_BAO_Membership::getContactMembership("1889", "3", FALSE, NULL, TRUE)
#13 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Member\BAO\Membership.php(1481): CRM_Member_BAO_Membership::renewMembership("1889", "3", FALSE, "20140928213855", "1889", (Array:0), 1, NULL, TRUE, (Array:7), (Array:15), NULL, "%Y%m%d", "Online Contribution: Web Membership Signup", (Array:0), "%Y-%m-%d", 0, NULL)
#14 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Member\BAO\Membership.php(2213): CRM_Member_BAO_Membership::renewMembershipFormWrapper("1889", "3", FALSE, Object(CRM_Contribute_Form_Contribution_Confirm), NULL, "1889", (Array:0), 1, NULL)
#15 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Member\BAO\Membership.php(1322): CRM_Member_BAO_Membership::createOrRenewMembership((Array:37), "1889", (Array:0), NULL, "3", FALSE, 1, Object(CRM_Contribute_BAO_Contribution), Object(CRM_Contribute_Form_Contribution_Confirm))
#16 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Contribute\Form\Contribution\Confirm.php(1731): CRM_Member_BAO_Membership::postProcessMembership((Array:33), "1889", Object(CRM_Contribute_Form_Contribution_Confirm), (Array:30), (Array:0), (Array:0), (Array:11), (Array:1), TRUE, NULL, FALSE, "2", (Array:0))
#17 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Contribute\Form\Contribution\Confirm.php(974): CRM_Contribute_Form_Contribution_Confirm->processMembership((Array:33), "1889", (Array:0), (Array:0), (Array:30), (Array:0))
#18 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\Form.php(328): CRM_Contribute_Form_Contribution_Confirm->postProcess()
#19 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\StateMachine.php(162): CRM_Core_Form->mainProcess()
#20 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\QuickForm\Action\Next.php(60): CRM_Core_StateMachine->perform(Object(CRM_Contribute_Form_Contribution_Confirm), "next", "Next")
#21 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\HTML\QuickForm\Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contribute_Form_Contribution_Confirm), "next")
#22 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\packages\HTML\QuickForm\Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contribute_Form_Contribution_Confirm), "next")
#23 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\Controller.php(356): HTML_QuickForm_Page->handle("next")
#24 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\Invoke.php(331): CRM_Core_Controller->run((Array:3), NULL)
#25 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\Invoke.php(75): CRM_Core_Invoke::runItem((Array:15))
#26 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm\CRM\Core\Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#27 D:\path\myorg\myorg.org\wp-content\plugins\civicrm\civicrm.php(502): CRM_Core_Invoke::invoke((Array:3))
#28 [internal function](): CiviCRM_For_WordPress->invoke("<p> Lorem ipsum ...")
#29 D:\path\myorg\myorg.org\wp-includes\plugin.php(214): call_user_func_array((Array:2), (Array:1))
#30 D:\path\myorg\myorg.org\wp-includes\post-template.php(222): apply_filters("the_content", "<p> Lorem ipsum ...")
#31 D:\path\myorg\myorg.org\wp-content\themes\myorg\content-page.php(16): the_content()
#32 D:\path\myorg\myorg.org\wp-includes\template.php(503): require("D:\path\myorg\myorg.org\wp-content\themes\theme...")
#33 D:\path\myorg\myorg.org\wp-includes\template.php(477): load_template("D:\path\myorg\myorg.org/wp-content/themes/theme...", FALSE)
#34 D:\path\myorg\myorg.org\wp-includes\general-template.php(179): locate_template((Array:2), TRUE, FALSE)
#35 D:\path\myorg\myorg.org\wp-content\themes\myorg\full-width-page.php(17): get_template_part("content", "page")
#36 D:\path\myorg\myorg.org\wp-includes\template-loader.php(74): include("D:\path\myorg\myorg.org\wp-content\themes\theme...")
#37 D:\path\myorg\myorg.org\wp-blog-header.php(16): require_once("D:\path\myorg\myorg.org\wp-includes\template-loader...")
#38 D:\path\myorg\myorg.org\index.php(17): require("D:\path\myorg\myorg.org\wp-blog-header.php")
#39 {main}

[Eileen]

It looks like you are missing a build in membership status - the section of code you are dealing with looks like

    $statusIds[] = array_search('Pending', CRM_Member_PseudoConstant::membershipStatus());
    if (!$membershipId) {
      $statusIds[] = array_search('Cancelled', CRM_Member_PseudoConstant::membershipStatus());
    }
    $dao->whereAdd('status_id NOT IN ( ' . implode(',',  $statusIds) . ')');

In my install Pending is 5 & Cancelled is 6 - which implies Cancelled is the missing one.

There is a 'name' field and a 'label' field in the civicrm_membership_status' table - and these are both 'Cancelled' for me

[rd11]

Thanks, Eileen. Sure enough, cancelled is missing. 

Is there any chance someone could provide me all of the field values for Cancelled in membership status so I can reinsert it?

Thanks again.

[Eileen]

id;name;start_event;start_event_adjust_unit;start_event_adjust_interval;end_event;end_event_adjust_unit;end_event_adjust_interval;is_current_member;is_admin;weight;is_default;is_active;is_reserved;label
6;Cancelled;join_date;\N;\N;join_date;\N;\N;0;0;6;0;1;0;Cancelled

[rd11]

Thank you so much. That fixed the issue.

In a future release, maybe the cancelled status should be protected to prevent deletion?  I note that the the pending and deceased options cannot be edited, deleted, etc.

[Eileen]

you might want to check & see if it is on demo - if not then log an issue. (who knows maybe someone had to work really hard to break your site :-))

[Dan C]

To my knowledge you do have to work pretty hard to change any of the statuses.  They are not something that is accessible from the menus, you either need to know the URL to get to the UI or you need to change it in the database itself.  I have had to do it a couple times and always have to look up the URL .

[rd11]

Dan, the issue was actually with membership status, not contribution status.  The membership statuses are pretty easily accessible and "cancelled" is one click from deletion (which is what apparently happened in this case).

Administer > CiviMember > Membership Status Rules.

[Dan C]

Sorry, was reading through too fast this morning (shouldn't check the forums without a cup of coffee).  I have checked both the demo (4.4) and sandbox (4.5) sites and only Pending and Deceased are reserved.  Since cancelled is hard coded into Civi it should be reserved as well.  I agree with Eileen that this should be logged as an issue (and link it here because I will look into it when I have a minute).

[joanne]

For anyone finding this topic, issue is https://issues.civicrm.org/jira/browse/CRM-15384