1

When I go to "manage groups", some of my smart group counts are listed as "unknown" and the notification "Count data for smart groups is not currently calculated" appears.

Does anyone know how to address this issue? I have the smart group cache timeout set to 5 minutes and I have "Rebuild Smart Group Cache" cron job set to always run when the cron runs. The cron task runs every 15 minutes.

I can manually click "Update Smart Group Counts", but the problem is that I have an automatic sync to Mailchimp. So whenever the smart groups fail to count themselves and Mailchimp tries to sync, it thinks everyone in the smart group unsubscribed when really Civicrm lost track of the smart group counts.

Any help would be appreciated.

Improve this question
1
  • If your issue is with the integration with MailChimp, have you considered using the new Mosaico extension? That avoids the sync problem and keeps everything in Civi.
    – Aidan ♦
    May 25, 2018 at 8:47

4 Answers 4

Reset to default
1

This Q/A discussion "Should Manage Groups display the Smart Group contact count, or just display it as Unknown" may help including following the links to read the comments for issue CRM-21583.

Improve this answer
1
  • I see it should say "unknown" if no cache is found. However, I think this should never happen. The moment when the cache is not found, the mailchimp extension thinks I have no more users in the mailing list and it unsubscribed everyone. My Smart Group Timeout was set to 5 minutes and the scheduled jobs set to run every 15 minutes. Perhaps the smart group timeout needs to be longer than the scheduled job time? I have updated the cache timeout to 20 minutes to see what happens.
    – Joel
    May 28, 2018 at 13:20
1

Why do you use a smart group for syncing with mailchimp? What would happen if someone unsubscribe from mailchimp?

I would advise to use a normal group for a mail group, that way you are sure what would happen if someone unsubscribes (either via Mailchimp or via CiviCRM).

If you need people to be automaticly subscribed to mailgroup you could use CiviRules (https://civicrm.org/extensions/civirules) for that. Documentation on CiviRules could be found at: https://docs.civicrm.org/civirules/en/latest/

Improve this answer
2
  • Hi Jaap, It's because we group people dynamically based on certain data, usually membership contribution status and their geolocation. We do a one-way upload, so if the user unsubscribers from Mailchimp, then mailchimp handles their unsubscription. Civi rules may be a good solution, but I am still concerned about how civi is working. Is my installation broken if it can't maintain a list of smart group counts? This seems like a bug to me.
    – Joel
    May 28, 2018 at 12:57
  • The smart groups are a bit buggy and unstable in CiviCRM. Altough I am not a user and quite a few users use them succesfully. But I had some clients in the past where smart group counts did not work, or where the smart group caused a database deadlock. So my general advice is to stay away from smart groups.
    – Jaap Jansma - CiviCooP
    May 28, 2018 at 14:50
0

Hello there I have been reading this thread as I'm having issues with CiviCRM and not allow smart groups to refresh or cache. Keep getting the same error message when exporting or trying to use a smart group. I also get this message when trying to update the smart group cache.

I have tried to input the code in civiCRM.settings as suggested. I have tried to set up another scheduled job to flush and have used all the other scheduled jobs to try and flush the cache. I have tried to change the smart group cache timer in Admin to 0, more than 5 and 35 which is longer than the cron job runs (every half hour, Hosting doesn't allow a shorter time)

error message

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: no such field

Log errors:

Log Entry for Oct 02 18:42:29 [debug] $backTrace = #0 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(915): CRM_Core_Error::backtrace("backTrace", TRUE) #1 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error)) #2 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/DB.php(997): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), "SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...") #3 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...") #4 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...", "DB_Error", TRUE) #5 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1925): PEAR->__call("raiseError", (Array:7)) #6 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(935): DB_common->raiseError(-19, NULL, NULL, "SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...", "1054 ** Unknown column 'a.api_entity' in 'field list'") #7 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(405): DB_mysqli->mysqliRaiseError() #8 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1231): DB_mysqli->simpleQuery("SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...") #9 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2696): DB_common->query("SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...") #10 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...") #11 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(421): DB_DataObject->query("SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...") #12 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1473): CRM_Core_DAO->query("SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...", TRUE) #13 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/Api4/Query/Api4SelectQuery.php(136): CRM_Core_DAO::executeQuery("SELECT a.id AS id, a.form_values AS form_values, a.mapping_id...") #14 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/Api4/Generic/DAOGetAction.php(76): Civi\Api4\Query\Api4SelectQuery->run() #15 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/Api4/Generic/DAOGetAction.php(67): Civi\Api4\Generic\DAOGetAction->getObjects() #16 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/Api4/Provider/ActionObjectProvider.php(68): Civi\Api4\Generic\DAOGetAction->_run(Object(Civi\Api4\Generic\Result)) #17 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(150): Civi\Api4\Provider\ActionObjectProvider->invoke(Object(Civi\Api4\Generic\DAOGetAction)) #18 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/Api4/Generic/AbstractAction.php(230): Civi\API\Kernel->runRequest(Object(Civi\Api4\Generic\DAOGetAction)) #19 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/SavedSearch.php(214): Civi\Api4\Generic\AbstractAction->execute() #20 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(462): CRM_Contact_BAO_SavedSearch::getSearchParams("2") #21 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3160): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO)) #22 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(3061): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), "civicrm_group_contact_cache_5f77668580a43", "contact_a", "IN") #23 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(1820): CRM_Contact_BAO_Query->group((Array:5)) #24 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(2058): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), FALSE) #25 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(582): CRM_Contact_BAO_Query->whereClause(FALSE) #26 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(529): CRM_Contact_BAO_Query->initialize(NULL) #27 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4578): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), (Array:165), TRUE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND", NULL, "1") #28 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(210): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE) #29 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/GroupContactCache.php(192): CRM_Contact_BAO_GroupContactCache::add((Array:13)) #30 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/api/v3/Job.php(679): CRM_Contact_BAO_GroupContactCache::loadAll((Array:0), 0) #31 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/API/Provider/MagicFunctionProvider.php(89): civicrm_api3_job_group_rebuild((Array:1)) #32 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(150): Civi\API\Provider\MagicFunctionProvider->invoke((Array:8)) #33 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(81): Civi\API\Kernel->runRequest((Array:8)) #34 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/api/api.php(22): Civi\API\Kernel->runSafe("Job", "group_rebuild", (Array:1)) #35 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/JobManager.php(133): civicrm_api("Job", "group_rebuild", (Array:1)) #36 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/JobManager.php(100): CRM_Core_JobManager->executeJob(Object(CRM_Core_ScheduledJob)) #37 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Admin/Form/Job.php(195): CRM_Core_JobManager->executeJobById(15) #38 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(484): CRM_Admin_Form_Job->postProcess() #39 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Submit.php(58): CRM_Core_Form->mainProcess() #40 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Submit->perform(Object(CRM_Admin_Form_Job), "submit") #41 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Job), "submit") #42 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(335): HTML_QuickForm_Page->handle("submit") #43 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(395): CRM_Core_Controller->run() #44 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(140): CRM_Core_Page_Basic->edit(4, 15) #45 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Admin/Page/Job.php(134): CRM_Core_Page_Basic->run() #46 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(312): CRM_Admin_Page_Job->run((Array:3), NULL) #47 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:16)) #48 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:3)) #49 /home/customer/www/dublinfood.coop/public_html/wp-content/plugins/civicrm/civicrm.php(1535): CRM_Core_Invoke::invoke((Array:3)) #50 /home/customer/www/dublinfood.coop/public_html/wp-includes/class-wp-hook.php(287): CiviCRM_For_WordPress->invoke("") #51 /home/customer/www/dublinfood.coop/public_html/wp-includes/class-wp-hook.php(311): WP_Hook->apply_filters("", (Array:1)) #52 /home/customer/www/dublinfood.coop/public_html/wp-includes/plugin.php(478): WP_Hook->do_action((Array:1)) #53 /home/customer/www/dublinfood.coop/public_html/wp-admin/admin.php(259): do_action("toplevel_page_CiviCRM") #54 {main}

Log error

Log Entry for Oct 02 18:42:29 [error] $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => exceptionHandler )

[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => SELECT `a`.`id` AS `id`, `a`.`form_values` AS `form_values`, `a`.`mapping_id` AS `mapping_id`, `a`.`search_custom_id` AS `search_custom_id`, `a`.`api_entity` AS `api_entity`, `a`.`api_params` AS `api_params`

FROM civicrm_saved_search a WHERE (a.id = "2") [nativecode=1054 ** Unknown column 'a.api_entity' in 'field list'] [type] => DB_Error [user_info] => SELECT a.id AS id, a.form_values AS form_values, a.mapping_id AS mapping_id, a.search_custom_id AS search_custom_id, a.api_entity AS api_entity, a.api_params AS api_params FROM civicrm_saved_search a WHERE (a.id = "2") [nativecode=1054 ** Unknown column 'a.api_entity' in 'field list'] [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT a.id AS id, a.form_values AS form_values, a.mapping_id AS mapping_id, a.search_custom_id AS search_custom_id, a.api_entity AS api_entity, a.api_params AS api_params FROM civicrm_saved_search a WHERE (a.id = "2") [nativecode=1054 ** Unknown column 'a.api_entity' in 'field list']"] )

CiviCRM is working fine, but cannot access or use already set up smart groups. Has their been a fix that I am not aware of? I can't export any contacts in that group for a mailing to go out!!

Improve this answer
1
  • Hi Aaron - the idea with StackExchange is that as a Question/Answer system you are advised not to ask a Question by submitting an answer. Much better to ask a new Question and refer to existing ones, or add as a comment to an existing Answer or the original Question - but doesn't work for big posts like this
    – petednz - fuzion ♦
    Oct 2, 2020 at 18:37
0

We hit a new issue where it appears the Smart Group Cache table's auto increment value was too big for the id field causing smart groups not to rebuild. The AUTO_INCREMENT was up to 4294978644 which seems to be too large for maria db's unsigned int. Once we reset the auto increment back to 1 the table refilled. There is a discussion here

Improve this answer

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.