0

Activity Search is failing every time for this install (Search/Find Activities) no matter what search parameters are used. The problem is the following SQL error:
SELECT count( DISTINCT ( civicrm_activity.id ) ) as rowCount FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_contact ON ( civicrm_activity_contact.contact_id = contact_a.id ) LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_contact.activity_id AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) INNER JOIN civicrm_contact ON ( civicrm_activity_contact.contact_id = civicrm_contact.id and civicrm_contact.is_deleted != 1 ) LEFT JOIN civicrm_activity_contact source_activity ON (source_activity.activity_id = civicrm_activity_contact.activity_id AND source_activity.record_type_id = 2) LEFT JOIN civicrm_contact source_contact ON (source_activity.contact_id = source_contact.id) WHERE ( civicrm_activity.activity_type_id IN ("110") AND civicrm_activity.status_id IN ("1", "2") AND civicrm_activity.is_test = 0 ) AND ( ( civicrm_activity.activity_type_id IN (110,135,136,120,1,2,3,116,4,9,12,5,6,19,22,34,40,44,37,38,39,45,41,42,43,52,49,50,46,47,48,51,64,114,115,68,73,74,75,76,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,96,97,99,100,101,102,103,104,105,106,107,108,109,111,113,117,118,119,121,122,123,124,125,126,127,128,130,131,133,134,137,138,139,140,141,142,143) ) ) AND (contact_a.is_deleted = 0) AND (civicrm_activity.activity_type_id Array)
the error message is 'check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Array)' at line 8]'
As you can see this install has a lot of activity types - could this be the reason? The last 'AND' statement seems to be the problem. This is on 5.28.3 and activity search is working fine on other 5.28.3 systems. Also there seems to be no limit on the number of items in the 'IN' clause in MySQL.

Improve this question
4
  • Do you have some ACL or extension that restricts by types? It's not the amount - what's happening is there's supposed to be a string but it's getting a php array, so it's a coding error somewhere.
    – Demerit
    Sep 24, 2020 at 19:30
  • Yes indeed (my, that was a fast response!). We have the Activity ACL extension installed (ACL for Activity Types). I didn't think it looked like that was relevant...wrong?
    – Andy Clark
    Sep 24, 2020 at 19:35
  • See github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/…
    – Demerit
    Sep 24, 2020 at 19:59
  • Many thanks, I'll test and will confirm if that's the answer. I looked at that site but it wasn't in the list of issues
    – Andy Clark
    Sep 24, 2020 at 20:02

1 Answer 1

Reset to default
3

I think this is the same problem as https://github.com/JMAConsulting/biz.jmaconsulting.activitytypeacl/pull/14

Improve this answer
2
  • Thanks for this - 47 minutes from reporting the problem and the fix is now installed!
    – Andy Clark
    Sep 24, 2020 at 20:13
  • Great! Glad it's working.
    – Demerit
    Sep 24, 2020 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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