CiviCRM Community Forums (archive)

I had been working with Lobo on an upgrade issue where my smart groups became corrupted. I only have about 8 smart groups, so I decided it would actually be easier to just delete them and then rebuild them from new advanced searches.

The problem is that I get the same error when I go to delete them as I do when I go to search on them. I would prefer to delete them as opposed to just disabling them as I would like to keep things clean and tidy.

I am wondering if someone can give me the direct DB query I should run to delete these groups, or if at this point it's a bug? I am happy to directly delete them from the DB; I just want to make sure I do it properly, considering there are a few different group tables.

For reference, here is the error I get when either searching on or trying to delete any of my smart groups:


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 civicrm_contribution.is_test = '0' ) AND ( 1 ) AND 1 ) ' 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(contact_a.id) as contact_id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-10` ON contact_a.id = `civicrm_group_contact-10`.contact_id   WHERE  ( ( ( `civicrm_group_contact-10`.group_id IN (10) AND `civicrm_group_contact-10`.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-17,7` ON ( `civicrm_entity_tag-17,7`.contact_id = contact_a.id )   WHERE  ( `civicrm_entity_tag-17,7`.tag_id IN (17,7) AND LOWER(  ) = 'all' AND  civicrm_contribution.is_test = '0' )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 10 AND civicrm_group_contact.status = 'Removed' ) ) ) ) )  LIMIT 0, 25  [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  civicrm_contribution.is_test = '0' )  AND  ( 1 )  AND 1    ) 
  ' at line 2]
    [type] => DB_Error
    [user_info] => SELECT DISTINCT(contact_a.id) as contact_id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-10` ON contact_a.id = `civicrm_group_contact-10`.contact_id   WHERE  ( ( ( `civicrm_group_contact-10`.group_id IN (10) AND `civicrm_group_contact-10`.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-17,7` ON ( `civicrm_entity_tag-17,7`.contact_id = contact_a.id )   WHERE  ( `civicrm_entity_tag-17,7`.tag_id IN (17,7) AND LOWER(  ) = 'all' AND  civicrm_contribution.is_test = '0' )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 10 AND civicrm_group_contact.status = 'Removed' ) ) ) ) )  LIMIT 0, 25  [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  civicrm_contribution.is_test = '0' )  AND  ( 1 )  AND 1    ) 
  ' 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(contact_a.id) as contact_id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-10` ON contact_a.id = `civicrm_group_contact-10`.contact_id   WHERE  ( ( ( `civicrm_group_contact-10`.group_id IN (10) AND `civicrm_group_contact-10`.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-17,7` ON ( `civicrm_entity_tag-17,7`.contact_id = contact_a.id )   WHERE  ( `civicrm_entity_tag-17,7`.tag_id IN (17,7) AND LOWER(  ) = 'all' AND  civicrm_contribution.is_test = '0' )  AND  ( 1 )  AND 1    ) 
                              AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = 10 AND civicrm_group_contact.status = 'Removed' ) ) ) ) )  LIMIT 0, 25  [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  civicrm_contribution.is_test = '0' )  AND  ( 1 )  AND 1    ) 
  ' at line 2]"]
)
[/code]

Thanks lobo...perfect