0

All of a sudden my front end name listing/search gives a DB Error. Any ideas on why this would start now?? This is occuring on the public and the registered access search profiles.

Here's what I found in the Log >>

Using Joomla 3.6.2 and CiviCRM 4.7.20, with MySQL 5.6.35


Jun 20 17:18:51 [info] $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle )

[code] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] => SELECT count(id) FROM civicrm_uf_field WHERE uf_group_id =  AND visibility IN ('Public Pages', 'Public Pages and Listings')  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND visibility IN ('Public Pages', 'Public Pages and Listings')' at line 1]
[type] => DB_Error
[user_info] => SELECT count(id) FROM civicrm_uf_field WHERE uf_group_id =  AND visibility IN ('Public Pages', 'Public Pages and Listings')  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND visibility IN ('Public Pages', 'Public Pages and Listings')' at line 1]
[to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT count(id) FROM civicrm_uf_field WHERE uf_group_id =  AND visibility IN ('Public Pages', 'Public Pages and Listings')  [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND visibility IN ('Public Pages', 'Public Pages and Listings')' at line 1]"]

)

1

Looks like you don't have summary_overlay profile in your civicrm_uf_group table. This is a reserved profile which we cannot delete or disable from UI.

So its possible if the name is edited or the profile is deleted from your db due to some reason. To check-

  1. Open civicrm_uf_group table.
  2. Check for the entry for Summary Overlay profile. This is with id 7 on demo site of civicrm.
  3. If you don't find this profile - error is due to this reason.
  4. If you find it - check the name column of this profile and change if it is anything other than summary_overlay.
  • The entry is there .. listed as summer_overlay_7 The other entries there also have their ID number added so I didn't touch this. – Neil Z Jun 22 '17 at 3:20
  • hmm - could be due to some unknown reason - but I'm pretty sure error would go off if you replace this with summary_overlay. – jitendra - fuzion Jun 22 '17 at 3:41
  • I took your advice, and you were right that fixed the problem. The question is how did that number appear, and why do the rest have them? In any case, thanks for the fix !! – Neil Z Jun 23 '17 at 9:58
0

Neil Z - your query missing uf_group_id value in the query ("WHERE uf_group_id = AND") - sorry if this is stating the obvious - and i think the value should be whatever the id of the civicrm_uf_group is. Has anyone deleted/disabled groups , can you find it in Manage Groups?

  • I agree with you on this, but nothing has changed, and I'm the only one who can change anything on the backend. This worked fine before the latest update from 4.7.18 to 4.7.20. Turns out all my user searches are having the same issue. – Neil Z Jun 22 '17 at 3:22
  • FWIW .. here's the URL that's created in Joomla to initiate the search: 'index.php?option=com_civicrm&view=Profiles&layout=search&task=civicrm/profile&gid=11&reset=1' however, that civicrm/profile does not look like a correct parameter. – Neil Z Jun 22 '17 at 3:32
  • In addition, the fields in the civicrm_uf_field table are correctly associated with the profile using the uf_group ID of 11. Apparently the gid=11 parameter is not being parsed and included in the SQL. Again, I think its due to that forward slash in the civicrm/profile part of the URL. – Neil Z Jun 22 '17 at 3:41
0

We had exactly the same problem. Changed summary_overlay_7 to Summary_Overlay like before it stopped "DB Syntax error"

  • and did that fix the problem? sounds like it did but just hoping that if this is an Answer it can be a bit more obvious. thx – petednz - fuzion Jul 12 '17 at 20:28

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.