Can't update smart group counts

Question

My smart groups show unknown counts. Debugging shows this when I click "update smart group counts".

Wordpress 5.0.3, Civi 5.9.0, PHP 7.2.14

#0 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(190): CRM_Core_Error::backtrace()
#1 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#3 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#4 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "DB_Error", TRUE)
#5 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", "1292 ** Truncated incorrect DOUBLE value: ''")
#7 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#9 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2415): DB_common->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#10 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#11 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#12 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1413): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...", TRUE)
#13 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(597): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache384 (SELECT 14 as grou...")
#14 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3106): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#15 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3037): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), NULL, "contact_a", "IN")
#16 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(1822): CRM_Contact_BAO_Query->group((Array:5))
#17 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(2044): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), NULL)
#18 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(531): CRM_Contact_BAO_Query->whereClause(NULL)
#19 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(491): CRM_Contact_BAO_Query->initialize(NULL)
#20 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4478): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), (Array:21), TRUE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND", NULL, "1")
#21 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(226): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE)
#22 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(208): CRM_Contact_BAO_GroupContactCache::add((Array:3))
#23 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Group/Page/Group.php(129): CRM_Contact_BAO_GroupContactCache::loadAll()
#24 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(160): CRM_Group_Page_Group->browse(NULL, NULL)
#25 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309): CRM_Core_Page_Basic->run((Array:2), NULL)
#26 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
#27 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2))
#28 /var/www/sln/wordpress/wp-content/plugins/civicrm/civicrm.php(1240): CRM_Core_Invoke::invoke((Array:2))

Answer 1

Quick Note The issue/solution I describe below only addresses broken Groups/Searches using the "on hold" email field. It's possible the Group/Search is broken through another field/value and the below info will only help specific cases.

Another Quick Note I solved a different Smart Group/Saved Search issue by switching "Empty" operators to "Null" operators in the Search criteria. StackOverflow post here: Search Builder Smart Group with Latitute, Street Address criteria 1292 Truncated DB Error

We are having a similar issue and I have traced the error down to this WHERE condition: civicrm_email.on_hold IN (""). I have also opened up an issue with CiviCRM: https://lab.civicrm.org/dev/core/issues/731

I do not have a fix yet, and would have left this as a comment and not an answer, but alas I do not have enough reputation yet.

EDIT: Per Petednz's help and some investigation, it appears the process for generating these Smart Group queries for contact record was overhauled somewhere between 5.9 and 5.10 (specifically the CRM/Contact/BAO/Query.php went from 4000+ lines to ~100, where the "on_hold" condition was hard-coded in). I have not confirmed an upgrade to 5.10.2 will fix it, however I am operating on the assumption it will and can confirm it once I have done so.

EDIT 2: Sterndata is right, I was looking at the wrong file. The existing offending code still exists in 5.10.2. A temporary workaround I have got working is to find this chunk of code CRM\Contact\BAO\Query.php (lines 1637-1642):

  elseif ($id == 'email_on_hold') {
    if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
      $onHoldValue = (array) $onHoldValue;
      $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
    }
  }

And then I add one line to check for and re-write the bogus value (the isset line):

  elseif ($id == 'email_on_hold') {
    if ($onHoldValue = CRM_Utils_Array::value('email_on_hold', $formValues)) {
      $onHoldValue = (array) $onHoldValue;
      if(isset($onHoldValue['on_hold']) && !$onHoldValue['on_hold']) $onHoldValue = array(0);
      $params[] = array('on_hold', 'IN', $onHoldValue, 0, 0);
    }
  }

As far as I can tell, this value is hard-coded into the form_values column on the civicrm_saved_search table in a serialized array like so:

s:13:"email_on_hold";a:1:{s:7:"on_hold";s:0:"";}

The following code commit in 5.9.0 changed how the field works, by I believe, changing it to allow both a checkbox type OR a select list, depending on the values of what looks like a "Bulk Email" settings option: https://github.com/civicrm/civicrm-core/pull/12942/files#diff-1be8d2488755f023664ac3652a8d5de2R58

It doesn't appear there was a process for updating these values in the database to work with the new fields, but the upgrade did include a message stating that Smart Groups might be broken, and instructs to re-create the broken searches/smart groups.

Hopefully Last Edit I wrote some Drupal 7 specific PHP code (sorry sterndata I know you're on Wordpress) to find these saved searched rows in the table with the bad email_on_hold field data and update the data to work. I have tested it on my 5.9.1 site and it has worked fantastically for us. I ran it once and hopefully should never have to think about this again!

However, per best practice please modify the code to suit your needs and then run it on one record first, preferably in a test environment!

  $query = db_select('civicrm_saved_search', 'ss');
  $query->fields('ss');
  $query->condition('ss.form_values','%' . db_like('email_on_hold') . '%', 'LIKE');
  // this condition might be too specific
  // $bad_data_string = '{s:7:"on_hold";s:0:"";';
  // $query->condition('ss.form_values','%' . db_like($bad_data_string) . '%', 'LIKE');
  // uncomment to only alter one specific record at a time
  // $query->condition('ss.id', 47);
  // $query->range(0,1);
  $result = $query->execute()->fetchAllAssoc('id');
  if(!empty($result)) {
    foreach($result as $record_id => $record) {
      $form_values = unserialize($record->form_values);
      if(isset($form_values['email_on_hold']) && is_array($form_values['email_on_hold'])) {
        $email_on_hold = $form_values['email_on_hold'];
        if(isset($email_on_hold['on_hold']) && !$email_on_hold['on_hold']) $email_on_hold = array(0);
      }
      // update only if different
      if($email_on_hold != $form_values['email_on_hold']) {
        $form_values['email_on_hold'] = $email_on_hold;
        $new_form_values = serialize($form_values);
        $update_on_hold = db_update('civicrm_saved_search');
        $update_on_hold->fields(array(
          'form_values' => $new_form_values,
        ));
        $update_on_hold->condition('id', $record_id);
        $update_on_hold->execute();
      }
    }
  }

Lastly, if you do run these fixes on the database, the one line I had added to the Query.php file is no longer necessary.

Answer 2

The issue I was having, which I think is related to this same update, was that I couldn't go to a Smart Group and click "Contacts" and actually get the result. It gave an error:

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: unknown error

This also didn't provide me with the usual "Edit Smart Group Criteria" link at the top of the page because it was a fatal error page. I did track down a way to do that, and update the smart group despite not actually changing anything, making it functional again without having to do anything directly in the database:

• Click "Settings" beside the Smart Group

• In the popup at the bottom you'll find "Edit Smart Group Criteria" and clicking on it will take you to the functional Smart Group page that lists the contacts/results.

• Select "All X results" and in the actions menu, choose "Group - Update Smart Group" (even though you haven't changed anything)

• Select "Update Smart Group" on the verification screen

Now when you go to the Groups page, the Smart Groups will work as expected.