3

Environment: CiviCRM 4.7.21 on Drupal 7.x on LAMP stack

Error Message:

DataTables warning: table id=DataTables_Table_0 - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

Bug: On 'Manage Groups' (civicrm/group?reset=1) we do not see any groups upon page load, and see the above error. If we search for groups, they are there.

Question: How do we resolve? This was introduced after upgrade from 4.6 to 4.7

Observations: We have many groups. If I select 'Access Control' then it loads a few groups there. Is the issue that we have too many groups?

enter image description here

If I enable Debugging/Backtrace, and try to update 'Smart Group Counts' I get this:

0 mysite.com/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(374): CRM_Core_Error::backtrace()
1 mysite.com/public_html/sites/all/modules/civicrm/CRM/Utils/Type.php(358): CRM_Core_Error::fatal("current is not of the type Integer") 
2 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(5609): CRM_Utils_Type::escape("current", "Integer") 
3 mysite.com/public_html/sites/all/modules/civicrm/CRM/Member/BAO/Query.php(255): CRM_Contact_BAO_Query::buildClause("civicrm_membership.status_id", "=", "current", "Integer") 
4 mysite.com/public_html/sites/all/modules/civicrm/CRM/Member/BAO/Query.php(152): CRM_Member_BAO_Query::whereClauseSingle((Array:5), Object(CRM_Contact_BAO_Query)) 
5 mysite.com/public_html/sites/all/modules/civicrm/CRM/Core/Component.php(299): CRM_Member_BAO_Query::where(Object(CRM_Contact_BAO_Query))
6 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2037): CRM_Core_Component::alterQuery(Object(CRM_Contact_BAO_Query), "where") 
7 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(519): CRM_Contact_BAO_Query->whereClause(NULL) 
8 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(479): CRM_Contact_BAO_Query->initialize(NULL) 
9 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(637): CRM_Contact_BAO_Query->__construct((Array:3), (Array:5), NULL, FALSE, FALSE, 1, TRUE, TRUE, FALSE, NULL, (Array:2)) 
10 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(3089): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO)) 
11 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(3024): CRM_Contact_BAO_Query->addGroupContactCache((Array:1), NULL, "contact_a", "IN") 
12 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(1813): CRM_Contact_BAO_Query->group((Array:5)) 
13 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(2033): CRM_Contact_BAO_Query->whereClauseSingle((Array:5), NULL) 
14 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(519): CRM_Contact_BAO_Query->whereClause(NULL) 
15 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(479): CRM_Contact_BAO_Query->initialize(NULL) 
16 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(4435): CRM_Contact_BAO_Query->__construct((Array:1), (Array:1), NULL, TRUE, FALSE, 1, TRUE, TRUE, FALSE, NULL, "AND", NULL) 
17 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(229): CRM_Contact_BAO_Query::apiQuery((Array:1), (Array:1), NULL, NULL, 0, 0, FALSE) 
18 mysite.com/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(208): CRM_Contact_BAO_GroupContactCache::add((Array:85)) 
19 mysite.com/public_html/sites/all/modules/civicrm/CRM/Group/Page/Group.php(129): CRM_Contact_BAO_GroupContactCache::loadAll() 
20 mysite.com/public_html/sites/all/modules/civicrm/CRM/Core/Page/Basic.php(172): CRM_Group_Page_Group->browse(NULL, NULL) 
21 mysite.com/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(310): CRM_Core_Page_Basic->run((Array:2), NULL) 
22 mysite.com/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13)) 
23 mysite.com/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2)) 
24 mysite.com/public_html/sites/all/modules/civicrm/drupal/civicrm.module(448): CRM_Core_Invoke::invoke((Array:2)) 
25 [internal function](): civicrm_invoke("group") 
26 mysite.com/public_html/includes/menu.inc(527): call_user_func_array("civicrm_invoke", (Array:1)) 
27 mysite.com/public_html/index.php(21): menu_execute_active_handler() 
28 {main}
  • Can you share a screenshot of the Manage group page with the error ? – jitendra - fuzion Aug 26 '17 at 6:04
  • added the photo – Zachary Aug 27 '17 at 6:18
  • Does the PR mentioned in the answer resolves it for you ? – jitendra - fuzion Aug 28 '17 at 6:14
  • I applied the patch and it did not change results. – Zachary Aug 29 '17 at 17:36
  • I have narrowed it down to group(s) by a particular user. – Zachary Sep 8 '17 at 17:14
2

While I can't tell (as Hans suggests) which smart group is causing the problem here, he's right - there's some smart group that's causing a problem. I couldn't tell you why this is happening, but it's true that deleting and recreating the smart group in question will resolve this.

current is not of the type integer is the key to this. You have some smart group where one of the search terms is "membership status = current". That's the one with the problem.

To find the smart group(s) that have this problem, you can run this SQL command:

SELECT cg.id, cg.title FROM civicrm_saved_search css JOIN civicrm_group cg ON cg.saved_search_id = css.id WHERE css.form_values LIKE '%status_id%' AND css.form_values LIKE '%current%';
  • Thank you @Jon G I was just going through them manually, and I found the particular user has many 'Hidden Smart Groups' - presumably due to using Civimail? Your query comes up with two Hidden Smart Groups: Hidden Smart Group 117, 122 – Zachary Sep 8 '17 at 17:27
  • @Zachary yes - sending CiviMail directly from search results (as opposed to Mailings menu » New Mailing is what causes hidden smart groups. If the problem group IS a hidden smart group, please let us know - that's a good clue as to why this problem might have occurred to begin with, and hopefully we can fix it. – Jon G - Megaphone Tech Sep 8 '17 at 17:29
  • I have saved the two query results into a .CSV file. Is it safe to delete the groups? I could also send you the file to review? – Zachary Sep 8 '17 at 17:32
  • If you know how to recreate the groups, it's safe to delete/recreate them. If you want to play it safe, it's best to make a backup of the CiviCRM database first. – Jon G - Megaphone Tech Sep 8 '17 at 17:40
  • 1
3

I've seen this in case of an older smart group containing deprecated formValues. See https://issues.civicrm.org/jira/browse/CRM-20874.

You can try applying the PR(patch) submitted on that issue and check if it resolves for you.

3

We had this error because there were some smart groups (we had 4 groups out of 210) that contained wrong mysql code, so the groups overview did not display because civi could not calculate the number of people in these groups.

We could see the first screen of the groups overview after reducing the pager to 10 items because the first wrong group was on the second page then. In your case you know the wrong groups will be mailing groups because filtering them out removes the error.

We solved as follows:

  • Take a full backup
  • Enable debugging
  • Click the link to update the smartgroup counter (/civicrm/group?reset=1&update_smart_groups=1) to get the error message on screen with the mysql query below in a collapsed fieldset where you can see which smart group id is blocking
  • Get the group_id out of the sql error message and check which group it is at /civicrm/group?reset=1&action=update&id=GROUPID. Write down this information so you know which smart group needs to be recreated.
  • Delete the smart group at /civicrm/group?reset=1&action=delete&id=GROUPID
  • Update again the smartgroup counter to see if there are other smart groups wrong and repeat
  • Recreate manually the deleted smartgroups
  • It says " Sorry but we are not able to provide this at the moment. current is not of the type Integer Return to home page." when I do your third step, of updating smart group counter. If I enable the backtrace, I get the following (updated main post) – Zachary Sep 8 '17 at 16:50
  • Yes I had this backtrace too but below that I also had a collapsed fieldset that shows the sql query when opened. I'm not sure which debug option triggers this extra fieldset. – Hans Rossel Sep 11 '17 at 6:24
  • Oh awesome. I didn't notice that. Did you resolve your issue? I used the 'Manage Groups' search feature to narrow down which User had the bad groups. Then I pulled up all the groups in phpMyAdmin. I then looked at the names of the group, and then searched in CiviCRM for a word/phrase in each group name, and then narrowed it down to the exact group that was causing the issue. Then, I removed the group via phpMyAdmin (after backing up)- and then it worked. I also had some smart groups from 2012-era CiviCRM based on event price sets I had to remove. – Zachary Sep 12 '17 at 17:38
  • 1
    Great! yes my issue is fixed, deleted the 4 bad groups. I'm not sure why you had to search so far for the groups id because they are normally visible in the mysql query below the backtrace, the number of the bad group is there. – Hans Rossel Sep 13 '17 at 17:53
  • I did not notice the group ID – Zachary Sep 15 '17 at 0:01

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.