Database failures on activities after upgrade from 5.52 to 5.68.1

Question

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 'Array but 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!

Answer 1

This issue ended up being with the ActivityType ACL extension. Once I upgraded that extension to its latest version the errors went away.

Lessons learned:

• Since I could not find any discussion about this error either here or in the CiviCRM core github or gitlab, that should have clued me in to the fact that this is something unique to my environment - and the most unique thing is actually the extensions.

• Since I was doing my first upgrade in ~1 year, it stands to reason that many extensions would break. Updating extensions first before updating Civi might have been a better strategy.

• When I saw error messages about activities, rather than work hard to decipher the big backtrace and try to understand why the SQL statement was failing (which is where I went to) I should have immediately suspected an extension and looked at which extensions relate to activities.

• Another simple step I did not take would have been to disable any extension related to activities -- or even just keep disabling extensions one at a time to find if any of them were the problem.

This community is amazing. Thank you so much @Demerit for all of your rapid, timely help!