CiviCRM 5.14.0 Drupal 7.67
Users with CiviCase permission "access all cases and activities" have no issues, but users with "access my cases and activities" get an error ("DataTables warning: table id=DataTables_Table_0 - Ajax error." ) when the My Cases dashlet attempts to load on the main dashboard page.
The backtrace reveals that it's stemming from the getCases() function in /CRM/Case/Page/AJAX.php and the DB is returning an error "1054 ** Unknown column 't_act.activity_type_id' in 'field list'" I checked, and activity_type_id does exist in the referenced table.
The CiviCase Dashboard also does not work for users with the restricted permissions. It does not give and error, but it does not return any results (for either upcoming or recent activities) even though it should.
My feeble attempts to trace the error suggest that it has to do with the 'has access to all cases' permission and how that gets validated in /CRM/Case/BAO/Case.php and then passed to getCases().
Any help is much appreciated!
Here is the full text from the error log if it's useful:
Aug 23 11:00:53 [info] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)
[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, ANY_VALUE(case_relation_type.label_b_a) as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id FROM civicrm_case
INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_phone
ON civicrm_phone.contact_id = civicrm_contact.id
AND civicrm_phone.is_primary = 1
LEFT JOIN civicrm_relationship case_relationship
ON case_relationship.contact_id_a = civicrm_case_contact.contact_id
AND case_relationship.contact_id_b = 13622
AND case_relationship.is_active
AND case_relationship.case_id = civicrm_case.id
LEFT JOIN civicrm_relationship_type_en_US case_relation_type
ON case_relation_type.id = case_relationship.relationship_type_id
AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND case_relationship.contact_id_b = 13622 AND case_relationship.is_active GROUP BY case_id ORDER BY activity_date_time ASC) temp [nativecode=1054 ** Unknown column 't_act.activity_type_id' in 'field list']
[type] => DB_Error
[user_info] => SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, ANY_VALUE(case_relation_type.label_b_a) as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id FROM civicrm_case
INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_phone
ON civicrm_phone.contact_id = civicrm_contact.id
AND civicrm_phone.is_primary = 1
LEFT JOIN civicrm_relationship case_relationship
ON case_relationship.contact_id_a = civicrm_case_contact.contact_id
AND case_relationship.contact_id_b = 13622
AND case_relationship.is_active
AND case_relationship.case_id = civicrm_case.id
LEFT JOIN civicrm_relationship_type_en_US case_relation_type
ON case_relation_type.id = case_relationship.relationship_type_id
AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND case_relationship.contact_id_b = 13622 AND case_relationship.is_active GROUP BY case_id ORDER BY activity_date_time ASC) temp [nativecode=1054 ** Unknown column 't_act.activity_type_id' in 'field list']
[to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info=" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALUE(civicrm_case.subject) as case_subject, ANY_VALUE(civicrm_contact.id) as contact_id, ANY_VALUE(civicrm_contact.sort_name) as sort_name, ANY_VALUE(civicrm_phone.phone) as phone, ANY_VALUE(civicrm_contact.contact_type) as contact_type, ANY_VALUE(civicrm_contact.contact_sub_type) as contact_sub_type, ANY_VALUE(t_act.activity_type_id) as activity_type_id, ANY_VALUE(civicrm_case.case_type_id) as case_type_id, ANY_VALUE(civicrm_case.status_id) as case_status_id, ANY_VALUE(t_act.status_id) as status_id, ANY_VALUE(civicrm_case.start_date) as case_start_date, ANY_VALUE(case_relation_type.label_b_a) as case_role, ANY_VALUE(t_act.activity_date_time) as activity_date_time, ANY_VALUE(t_act.id) as activity_id FROM civicrm_case
INNER JOIN civicrm_case_contact ON civicrm_case.id = civicrm_case_contact.case_id
INNER JOIN civicrm_contact ON civicrm_case_contact.contact_id = civicrm_contact.id LEFT JOIN civicrm_phone
ON civicrm_phone.contact_id = civicrm_contact.id
AND civicrm_phone.is_primary = 1
LEFT JOIN civicrm_relationship case_relationship
ON case_relationship.contact_id_a = civicrm_case_contact.contact_id
AND case_relationship.contact_id_b = 13622
AND case_relationship.is_active
AND case_relationship.case_id = civicrm_case.id
LEFT JOIN civicrm_relationship_type_en_US case_relation_type
ON case_relation_type.id = case_relationship.relationship_type_id
AND case_relation_type.id = case_relationship.relationship_type_id WHERE (1) AND civicrm_case.is_deleted = 0 AND civicrm_contact.is_deleted <> 1 AND case_relationship.contact_id_b = 13622 AND case_relationship.is_active GROUP BY case_id ORDER BY activity_date_time ASC) temp [nativecode=1054 ** Unknown column 't_act.activity_type_id' in 'field list']"]
)
Aug 23 11:00:53 [info] $backTrace = #0 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Error.php(236): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#3 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#4 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...", "DB_Error", TRUE)
#5 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, " SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...", "1054 ** Unknown column 't_act.activity_type_id' in 'field list'")
#7 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#9 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#10 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#11 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(439): DB_DataObject->query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#12 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/DAO.php(1491): CRM_Core_DAO->query(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...", TRUE)
#13 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Case/BAO/Case.php(551): CRM_Core_DAO::singleValueQuery(" SELECT COUNT(*) FROM (SELECT ANY_VALUE(civicrm_case.id) as case_id, ANY_VALU...")
#14 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Case/Page/AJAX.php(203): CRM_Case_BAO_Case::getCases(FALSE, (Array:5))
#15 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(277): CRM_Case_Page_AJAX::getCases()
#16 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(85): CRM_Core_Invoke::runItem((Array:12))
#17 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#18 /opt/bitnami/apps/civicrm/htdocs/sites/all/modules/civicrm/drupal/civicrm.module(444): CRM_Core_Invoke::invoke((Array:3))
#19 /opt/bitnami/apps/civicrm/htdocs/includes/menu.inc(527): civicrm_invoke("ajax", "get-cases")
#20 /opt/bitnami/apps/civicrm/htdocs/index.php(21): menu_execute_active_handler()
#21 {main}