Upgraded from Civi 5.52 to Civi 5.68.1 on Drupal 7, PHP 7.3.13, Mysql 5.74. The errors are all revolving around activities. Anywhere in the UI that involves showing activities is returning a DB error in the UI and failing entirely. So, for example,
- Viewing a contact fails
- Viewing any report that involves activities fails
The error I see in ConfigAndLog is always the same:
Unexpected result from getcountArray ( [error_code] => -2 [sql] => [debug_info] => SELECT count(*) as c FROM civicrm_activity a LEFT JOIN civicrm_case_activity actjoin_case_id ON (actjoin_case_id.activity_id = a.id) WHERE (((`a`.`activity_type_id` Array))) AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = "412")) AND (actjoin_case_id.case_id IS NULL) AND (a.is_current_revision = "1") AND (a.activity_type_id IN ("1", "2", "3", "61", "49", "66", "50", "56", "51", "58", "59", "9", "5", "6", "7", "8", "4", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "44", "45", "46", "47", "48", "52", "53", "54", "55", "57", "60", "62", "63", "64", "65", "67", "68", "69", "70", "71", "72", "74", "76", "78", "80", "82")) AND (a.is_test = "0") AND (a.is_deleted = "0") [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 'Array))) AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE ' at line 4] [entity] => Activity [action] => get [is_error] => 1 [error_message] => DB Error: syntax error [debug_information] => )
So it's the long list of activity types that seems to be throwing a SQL error.
Looking through ConfigAndLog, I don't see any errors showing throughout the upgrade process itself. And the rest of the site functions fine.
I have tried:
- Looking at the list of activities. There are none that are disabled or anything like that.
- Recreating reports that throw the error. That doesn't do anything. And anyway the error occurs even when viewing a contact.
- Searching here for any instances of the error
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 'Arraybut I don't see anything. - Tried searching through the CiviCRM issues log for anything related to activities. Anything that is listed there looks like it got rolled into 5.68.1 though.
- Disabling CiviCase, because I saw a note in the issues log about CiviCase and activities for administrators. That did not do anything.
I'm stumped and not sure where to go from here.
Any suggestions welcome.
EDIT: Adding more fulsome log message with backtrace
2024-01-01 15:55:02-0800 [warning] Expected array of selectWhereClauses for civicrm_activity.activity_type_id, instead got "Array" Caller: Civi\API\SelectQuery::getAclClause
Array
(
[civi.tag] => deprecated
)
2024-01-01 15:55:03-0800 [error]
$Fatal Error Details = array:3 [
"message" => """
Unexpected result from getcountArray\n
(\n
[error_code] => -2\n
[sql] => \n
[debug_info] => SELECT count(*) as c\n
FROM civicrm_activity a\n
LEFT JOIN civicrm_case_activity actjoin_case_id ON (actjoin_case_id.activity_id = a.id)\n
WHERE (((`a`.`activity_type_id` Array)))\n
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = "412"))\n
AND (actjoin_case_id.case_id IS NULL)\n
AND (a.is_current_revision = "1")\n
AND (a.activity_type_id IN ("1", "2", "3", "61", "49", "66", "50", "56", "51", "58", "59", "9", "5", "6", "7", "8", "4", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "44", "45", "46", "47", "48", "52", "53", "54", "55", "57", "60", "62", "63", "64", "65", "67", "68", "69", "70", "71", "72", "74", "76", "78", "80", "82"))\n
AND (a.is_test = "0")\n
AND (a.is_deleted = "0")\n
[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 'Array)))\n
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE ' at line 4]\n
[entity] => Activity\n
[action] => get\n
[is_error] => 1\n
[error_message] => DB Error: syntax error\n
[debug_information] => \n
)\n
"""
"code" => null
"exception" => CRM_Core_Exception {#3870
-errorData: array:5 [
"error_code" => 0
"entity" => "Activity"
"action" => "getcount"
"is_error" => 1
"error_message" => """
Unexpected result from getcountArray\n
(\n
[error_code] => -2\n
[sql] => \n
[debug_info] => SELECT count(*) as c\n
FROM civicrm_activity a\n
LEFT JOIN civicrm_case_activity actjoin_case_id ON (actjoin_case_id.activity_id = a.id)\n
WHERE (((`a`.`activity_type_id` Array)))\n
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = "412"))\n
AND (actjoin_case_id.case_id IS NULL)\n
AND (a.is_current_revision = "1")\n
AND (a.activity_type_id IN ("1", "2", "3", "61", "49", "66", "50", "56", "51", "58", "59", "9", "5", "6", "7", "8", "4", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "44", "45", "46", "47", "48", "52", "53", "54", "55", "57", "60", "62", "63", "64", "65", "67", "68", "69", "70", "71", "72", "74", "76", "78", "80", "82"))\n
AND (a.is_test = "0")\n
AND (a.is_deleted = "0")\n
[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 'Array)))\n
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE ' at line 4]\n
[entity] => Activity\n
[action] => get\n
[is_error] => 1\n
[error_message] => DB Error: syntax error\n
[debug_information] => \n
)\n
"""
]
#cause: null
-_trace: null
#message: """
Unexpected result from getcountArray\n
(\n
[error_code] => -2\n
[sql] => \n
[debug_info] => SELECT count(*) as c\n
FROM civicrm_activity a\n
LEFT JOIN civicrm_case_activity actjoin_case_id ON (actjoin_case_id.activity_id = a.id)\n
WHERE (((`a`.`activity_type_id` Array)))\n
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE contact_id = "412"))\n
AND (actjoin_case_id.case_id IS NULL)\n
AND (a.is_current_revision = "1")\n
AND (a.activity_type_id IN ("1", "2", "3", "61", "49", "66", "50", "56", "51", "58", "59", "9", "5", "6", "7", "8", "4", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "44", "45", "46", "47", "48", "52", "53", "54", "55", "57", "60", "62", "63", "64", "65", "67", "68", "69", "70", "71", "72", "74", "76", "78", "80", "82"))\n
AND (a.is_test = "0")\n
AND (a.is_deleted = "0")\n
[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 'Array)))\n
AND (a.id IN (SELECT activity_id FROM civicrm_activity_contact WHERE ' at line 4]\n
[entity] => Activity\n
[action] => get\n
[is_error] => 1\n
[error_message] => DB Error: syntax error\n
[debug_information] => \n
)\n
"""
#code: 0
#file: "/var/www/html/sites/all/modules/civicrm/api/api.php"
#line: 138
trace: {
/var/www/html/sites/all/modules/civicrm/api/api.php:138 {
civicrm_api3(string $entity, string $action, array $params = [])
› if (is_array($result) && !empty($result['is_error'])) {
› throw new CRM_Core_Exception($result['error_message'], $result['error_code'] ?? 'undefined', $result);
› }
}
/var/www/html/sites/all/modules/civicrm/CRM/Activity/BAO/Activity.php:900 { …}
/var/www/html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php:2651 { …}
/var/www/html/sites/all/modules/civicrm/CRM/Contact/Page/View/Summary.php:434 { …}
/var/www/html/sites/all/modules/civicrm/CRM/Contact/Page/View/Summary.php:255 { …}
/var/www/html/sites/all/modules/civicrm/CRM/Contact/Page/View/Summary.php:83 { …}
/var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php:322 { …}
/var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php:69 { …}
/var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php:36 { …}
/var/www/html/sites/all/modules/civicrm/drupal/civicrm.module:472 { …}
/var/www/html/includes/menu.inc:527 { …}
/var/www/html/index.php:21 { …}
}
}
]
2024-01-01 15:55:03-0800 [debug] $backTrace = #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(443): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(39): CRM_Core_Error::handleUnhandledException(Object(CRM_Core_Exception))
#2 /var/www/html/sites/all/modules/civicrm/drupal/civicrm.module(472): CRM_Core_Invoke::invoke((Array:3))
#3 /var/www/html/includes/menu.inc(527): civicrm_invoke("contact", "view")
#4 /var/www/html/index.php(21): menu_execute_active_handler()
#5 {main}
EDIT 2: Updating the ActivityType ACL extension fixed the issue. Thank you @Demerit because without your timely comment I wouldn't have posted the latest backtrace and that's where I noticed the reference to ActivityType ACL...I've been staring at backtraces for the last 3 hours without noticing that. Thank you!