4

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))
  • I'm still stuck on this, now with 5.10.2. How can I get a full debug on the 2nd line: CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1218 (SELECT 14 as gro...") that inner select. – sterndata Feb 15 at 14:45
  • Additional info at pastebin.com/Y3Z62PTf The key error seems to be " Database Error Code: Truncated incorrect DOUBLE value: '', 1292" – sterndata Feb 15 at 14:51
  • I am seeing this on multiple sites -- I can't get a list of contacts in a smart group by clicking on "Contacts" as usual. It gives a fatal error similar to above. If I rebuild the group by using "Edit Smart Group Criteria" and resaving, then it works again. – Laryn - CEDC.org Feb 18 at 17:33
4

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.

  • 1
    Thanks for posting the gitlab issue Joegl - will probably help other folk to look at this if you can confirm the issue persists in 5.10.2 and / or master – petednz - fuzion Feb 15 at 18:54
  • 1
    +1'd both your answers so you are > 50 so you can do comments now ;-) – petednz - fuzion Feb 15 at 18:57
  • Hey Petdnz! Nice to see you here too! Thanks for the +1's, I will make sure to update my answer. – joegl Feb 15 at 19:20
  • 1
    I'm using 5.10.2. It's still there. – sterndata Feb 15 at 22:05
  • Thanks @sterndata I've looked again and edited my answer. – joegl Feb 18 at 16:59
1

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.

  • This is good advice as my solution only addresses a specific field. However, I wasn't able to fix another of ours which was broken in this manner. – joegl Mar 7 at 18:41
  • What happened in that one when you tried? – Laryn - CEDC.org Mar 7 at 21:16
  • Laryn the smart group was still broken after I tried updating. However, I did eventually fix it and made a different post here: civicrm.stackexchange.com/questions/28802/… – joegl Mar 7 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.