CiviCRM Community Forums (archive)

Hello,

I am using the latest 2.0.1 stable. When I do an advanced search on any of my groups, I am getting this error:

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    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 ') = 'all' ) AND ( 1 ) AND 1 ) AND contact_' at line 2, 1064

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-2` ON contact_a.id = `civicrm_group_contact-2`.contact_id   WHERE  ( ( ( `civicrm_group_contact-2`.group_id IN (2) AND `civicrm_group_contact-2`.status IN ("Added") ) OR ( 
                            ( contact_a.id IN ( SELECT DISTINCT(contact_a.id) as id  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id   LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-6,7,9,8,10,11` ON ( `civicrm_entity_tag-6,7,9,8,10,11`.contact_id = contact_a.id )   WHERE  ( `civicrm_entity_tag-6,7,9,8,10,11`.tag_id IN (6,7,9,8,10,11) AND LOWER(  ) = 'all' )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 2 AND civicrm_group_contact.status = 'Removed' ) ) ) ) )  AND  ( 1 )     [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 ') = 'all' )  AND  ( 1 )  AND 1    ) 
                              AND contact_' at line 2]
    [type] => DB_Error
    [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-2` ON contact_a.id = `civicrm_group_contact-2`.contact_id   WHERE  ( ( ( `civicrm_group_contact-2`.group_id IN (2) AND `civicrm_group_contact-2`.status IN ("Added") ) OR ( 
                            ( contact_a.id IN ( SELECT DISTINCT(contact_a.id) as id  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id   LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-6,7,9,8,10,11` ON ( `civicrm_entity_tag-6,7,9,8,10,11`.contact_id = contact_a.id )   WHERE  ( `civicrm_entity_tag-6,7,9,8,10,11`.tag_id IN (6,7,9,8,10,11) AND LOWER(  ) = 'all' )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 2 AND civicrm_group_contact.status = 'Removed' ) ) ) ) )  AND  ( 1 )     [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 ') = 'all' )  AND  ( 1 )  AND 1    ) 
                              AND contact_' at line 2]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-2` ON contact_a.id = `civicrm_group_contact-2`.contact_id   WHERE  ( ( ( `civicrm_group_contact-2`.group_id IN (2) AND `civicrm_group_contact-2`.status IN ("Added") ) OR ( 
                            ( contact_a.id IN ( SELECT DISTINCT(contact_a.id) as id  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id   LEFT JOIN civicrm_entity_tag `civicrm_entity_tag-6,7,9,8,10,11` ON ( `civicrm_entity_tag-6,7,9,8,10,11`.contact_id = contact_a.id )   WHERE  ( `civicrm_entity_tag-6,7,9,8,10,11`.tag_id IN (6,7,9,8,10,11) AND LOWER(  ) = 'all' )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 2 AND civicrm_group_contact.status = 'Removed' ) ) ) ) )  AND  ( 1 )     [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 ') = 'all' )  AND  ( 1 )  AND 1    ) 
                              AND contact_' at line 2]"]
)
However, I couldn't recreate this error on the demo. I don't know why I am getting it. Any suggestions?
[/code]

lobo,

1. what search are you trying to do. i see an OR clause with contribution and tags in the below sql which seems to cause the error

I am doing an advanced search on a single group, no other parameters. After a bit more testing, I realized that this error is thrown when I search on a Smart Group, but when I search on a non-Smart Group, it returns results properly.

Note: I get the same error if I go to "Manage Groups" and then click on the "Members" link for any Smart Group. My Smart Groups range in terms of their parameters, but all of them are created based on specific tags that contacts have. So if a contact has tags A and B, then they are in Smart Group 1, and so on. However, none of my Smart Groups have anything to do with contributions as far as I can tell.

2. are you using acl's?

I had 2 acl's that I had been testing with, but they were not important. I deleted them, but the error still exists when I search on a Smart Group.

I do know that these searches worked in 1.9, so I am thinking it must have something to do with the 2.0 upgrade....?

thanks,
emily

Was this resolved? I am having what I think is the same issue.

-Justin