Search Catalog does not return results because of collation conflict in Service Catalog

book

Article ID: 140566

calendar_today

Updated On:

Products

CA Service Catalog CA Service Management - Service Desk Manager CA Service Desk Manager

Issue/Introduction

The side effect for end users is the "Search Catalog" feature not returning results, but behind the scenes there is a collation conflict going on in the usm_offering table.

View.log (trace level enabled):

Query:  select  path,o.offering_id,item_id,o.parent_id,o.code,o.info_link,o.is_leaf,o.status,o.description,o.image_file,o.offering_name,(select offering_name from usm_offering where offering_id = o.parent_id) parent_name,rp.rate_plan_id,case when ( o.offering_name collate database_default like ? OR o.description collate database_default like ? ) then 0 WHEN ( rp.rate_plan_name collate database_default like ? ) then 1  WHEN ( dbo.usm_fn_remove_html(rd.item_text) collate database_default like ?  ) then 2 else 3 end as search_source,rd.item_id as item_id from ( SELECT lo.path,lo.top_base_offering,lo.domain,lo.offering_id,lo.status,lo.parent_id,lo.image_file,lo.code,lo.info_link,lo.is_leaf, CASE WHEN ( lv.attr_localized_value is null ) then lo.offering_name ELSE lv.attr_localized_value end AS offering_name ,CASE WHEN ( lv1.attr_localized_value is null ) THEN lo.description ELSE lv1.attr_localized_value END AS description, lo.mobile_enabled, lo.sort_order_no FROM usm_offering lo LEFT OUTER JOIN usm_localization_value lv on lo.offering_id = lv.obj_id AND lv.locale_code=? AND lv.obj_tbl_id = 1 AND lv.attr_name = 'offering_name' LEFT OUTER JOIN usm_localization_value lv1 on lo.offering_id = lv1.obj_id AND lv1.locale_code= ? AND lv1.obj_tbl_id = 1 AND lv1.attr_name = 'description' ) o  left outer join usm_offering_rate def_inclusion ordi on ordi.parent_id=o.offering_id or ordi.parent_id=o.top_base_offering  left outer join (SELECT text_4,item_type,numeric_1,item_id,rate_col,rate_row,rate_plan_id, CASE WHEN ( lv.attr_localized_value is null ) THEN lrd.item_text ELSE lv.attr_localized_value END AS item_text FROM usm_rate_definition lrd LEFT OUTER JOIN usm_localization_value lv on lrd.item_id= lv.obj_id AND lv.locale_code=? AND lv.obj_tbl_id = 3 AND lv.attr_name = 'item_text' AND lrd.item_type=0 AND lrd.status=1 AND (rate_col=0 or rate_col=1)) rd on ordi.child_id=rd.item_id  left outer join (SELECT rate_plan_id, CASE WHEN ( lv.attr_localized_value is null ) THEN lrp.rate_plan_name ELSE lv.attr_localized_value END as rate_plan_name FROM usm_rate_plan lrp LEFT OUTER JOIN usm_localization_value lv on lrp.rate_plan_id= lv.obj_id AND lv.locale_code=? AND lv.obj_tbl_id = 2 AND lv.attr_name = 'rate_plan_name') rp on ordi.rate_plan_id=rp.rate_plan_id where o.status in (1,6)  and  o.domain='BakerHughes' and (o.path like '10001/%' or o.path like '%/10001/%' ) and (  o.offering_name collate database_default like ? OR rp.rate_plan_name collate database_default like ? OR rd.item_text collate database_default like ? OR o.description collate database_default like ?  )
2019/11/15 09.36.40.954 TRACE [http-nio-8080-exec-13] [DBJDBC] SQL State=S0001, Error code=451
2019/11/15 09.36.40.954 ERROR [http-nio-8080-exec-13] [CService] CSRV0169
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot resolve collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in CASE operator occurring in SELECT statement column 9.

Cause

It happens when somehow the MDB level's collation is different from usm_offering table level collation.

Environment

Clarity Service Management 17.2
Clarity Service Management 17.1

Resolution

Steps for reproducing this issue:

Two fields were chosen: code and description. They were enough to reproduce this issue. Somehow your MDB level's collation is different from usm_offering table level collation. It was a simple test only in order to reproduce this issue.

ALTER TABLE dbo.usm_offering ALTER COLUMN code nvarchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS;
ALTER TABLE dbo.usm_offering ALTER COLUMN description nvarchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS;

Result:

SELECT c.name, c.collation_name FROM SYS.COLUMNS c JOIN SYS.TABLES t ON t.object_id = c.object_id WHERE t.name = 'usm_offering' 


IMPORTANT:

It is not possible to guarantee other tables have the same situation. Additionally, we cannot guarantee if this kind of issue can cause other unexpected issues e.g. weird behaviors compared to other environments with the same patch level. 

Consider talking to your database team. It will be nice if they can change the collation in the usm_offering table in order to see if it fixes the issue.

Make it is a low risk environment and make a database backup before any action.

Additional Information

Changing MDB or column collation for Service Catalog

Attachments