If a sys_term_config is defined against a particular storage table with a certain search term, *any* CONTAINS or ENDSWITH query against that storage table against that specific search term optimizes erroneously. The optimization is only supposed to kick in when the query is against the specific field therefore incorrect results may be returned when searching against a field that is different from the one that is defined in sys_term_config.
What is Term Optimization?
Term Optimization is a platform feature in Kingston which detects certain types of slow queries and optimizes them for better performance in MySQL.
Steps to Reproduce
- Navigate to /cmdb_ci_service_list.do and set model number to a couple arbitrary rows to contain ABC.
- Create an entry in sys_term_config:
- table name: cmdb_ci
- element name: model_number
- term: ABC
- Click Synchronize, and wait for it to finish
- Click Optimize/Activate
- Turn on SQL Debug
- Navigate to /cmdb_ci_service_list.do?sysparm_query=model_numberCONTAINSABC
- Confirm that query returns the rows where model_number was modified to contain ABC
- Confirm that query does a join to a sys_termXX table. This is normal.
- Now, navigate go to /cmdb_ci_service_list.do?sysparm_query=nameCONTAINSABC.
Observe the incorrect optimizing even though it is the wrong field.
If on Kingston through Kingston Patch 4:
Run the following script in scripts background to disable the term optimization feature:
var configs = ;
var gr = new GlideRecord('sys_term_config')
If on Pre-Kingston:
Run the following script in scripts background to create a property that ensures term optimization is not enabled when upgrading to Kingston. This prevents any impact, and when upgrading to a fixed build the optimization turns back on.
Related Problem: PRB1267268