CiviCRM - Can not access Manage Groups Page

Question

In civicrm i can not access Manage Groups Page I got following error.

Sorry but we are not able to provide this at the moment.
DB Error: unknown error
http://screencast.com/t/tzZBWqKq

But after commenting following code [civicrm/CRM/Contact/BAO/GroupContactCache.php] , i can access manage group page.

line : 683 to 700

$tempTable = 'civicrm_temp_group_contact_cache' . rand(0, 2000);
foreach (array($sql, $sqlB) as $selectSql) {
  if (!$selectSql) {
    continue;
  }
  $insertSql = "CREATE TEMPORARY TABLE $tempTable ($selectSql);";
  $processed = TRUE;
  CRM_Core_DAO::executeQuery($insertSql);
  CRM_Core_DAO::executeQuery(
    "INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
    SELECT DISTINCT $idName, group_id FROM $tempTable
  ");
  CRM_Core_DAO::executeQuery(" DROP TEMPORARY TABLE $tempTable");
}

self::updateCacheTime($groupIDs, $processed);

but by commenting this i can not create smart group using search builder, to create smart group i have to uncomment this code.

please help me to solve this caching group problem as soon as possible.

CiviCRM Version : 4.7.8 , Drupal core 7.43

Full Error Log

   Database Error Code: Operand should contain 1 column(s), 1241
   Additional Details:
   Array
      (
         [callback] => Array
            (
              [0] => CRM_Core_Error
              [1] => handle
      )

[code] => -1
[message] => DB Error: unknown error
[mode] => 16
[debug_info] => CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache492 (SELECT 15 as group_id,  contact_a.id as id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)` ON (contact_a.id = `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.contact_id AND `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.status IN ("Added"))   LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)` ON contact_a.id = `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)`.contact_id   WHERE  (  ( ( ( `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.group_id IN ( (1,2,3,4,5,6,7,8,9,10,11,12,13) ) ) OR ( `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)`.group_id IN ("(1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13)") ) ) AND contact_a.contact_type = 'individual' )  )  AND (contact_a.is_deleted = 0)   AND contact_a.id NOT IN (
                          SELECT contact_id FROM civicrm_group_contact
                          WHERE civicrm_group_contact.status = 'Removed'
                          AND   civicrm_group_contact.group_id = 15 ) ); [nativecode=1241 ** Operand should contain 1 column(s)]
[type] => DB_Error
[user_info] => CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache492 (SELECT 15 as group_id,  contact_a.id as id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)` ON (contact_a.id = `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.contact_id AND `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.status IN ("Added"))   LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)` ON contact_a.id = `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)`.contact_id   WHERE  (  ( ( ( `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.group_id IN ( (1,2,3,4,5,6,7,8,9,10,11,12,13) ) ) OR ( `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)`.group_id IN ("(1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13)") ) ) AND contact_a.contact_type = 'individual' )  )  AND (contact_a.is_deleted = 0)   AND contact_a.id NOT IN (
                          SELECT contact_id FROM civicrm_group_contact
                          WHERE civicrm_group_contact.status = 'Removed'
                          AND   civicrm_group_contact.group_id = 15 ) ); [nativecode=1241 ** Operand should contain 1 column(s)]
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache492 (SELECT 15 as group_id,  contact_a.id as id  FROM civicrm_contact contact_a  LEFT JOIN civicrm_group_contact `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)` ON (contact_a.id = `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.contact_id AND `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.status IN ("Added"))   LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)` ON contact_a.id = `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)`.contact_id   WHERE  (  ( ( ( `civicrm_group_contact-(1,2,3,4,5,6,7,8,9,10,11,12,13)`.group_id IN ( (1,2,3,4,5,6,7,8,9,10,11,12,13) ) ) OR ( `civicrm_group_contact_cache_(1,2,3,4,5,6,7,8,9,10,11,12,13)`.group_id IN ("(1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13)") ) ) AND contact_a.contact_type = 'individual' )  )  AND (contact_a.is_deleted = 0)   AND contact_a.id NOT IN (
                          SELECT contact_id FROM civicrm_group_contact
                          WHERE civicrm_group_contact.status = 'Removed'
                          AND   civicrm_group_contact.group_id = 15 ) ); [nativecode=1241 ** Operand should contain 1 column(s)]"]

)

Answer 1

Your issue lies with your group with a group ID of 15. From skimming your SQL, it looks like group 15 is anyone who's a member of groups 1-13? It also sounds like you made this group using Search Builder.

So you've almost certainly found a bug - and if you can replicate that bug on the demo server, someone may be able to take a look at it. If you can take a look at it, even better! For now though, I want to get you the result you're looking for.

Try one of the following approaches: * Use parent and child groups - make groups 1-13 a child of group 15. * Instead of using Search Builder, use the Custom Search "Include/Exclude by Group/Tag". You'll find that at Search menu » Custom Searches - click on Custom Searches, NOT on the items on its submenu. You'll find more custom searches there!

Answer 2

I had a similar issue following an upgrade from 4.5 to 4.6, and rebuilding the smart group solved it.

Googling your database error code found me this: https://stackoverflow.com/questions/14046838/mysql-operand-should-contain-1-columns which suggests that the query builder associated with your smart group just hit a wall in complexity.

Conclusion: what Jon said.