Database error when using 123TEXTQUERY321 query

[KP28]

Hello,

I was troubleshooting an issue with the search in the OOTB "My Requests" widget and came across this line in the widget server side script:

var task = new GlideRecordSecure('task');
task.addQuery('123TEXTQUERY321', localInput.search_text);

When I tried to execute the same query via background script

var search = "INC0024032";
var q = new GlideRecord('task');
q.addQuery('123TEXTQUERY321', search);
q.query();

I run into below database error

FAILED TRYING TO EXECUTE ON CONNECTION glide.ts.20 (connpid=xxx): SELECT task0.`sys_id`, ts_c_11_40.`word`, ts_c_11_40.`document_number`, ts_c_11_40.`total_weight` FROM ((task task0  INNER JOIN ts_document ts_document0 ON task0.`sys_id` = ts_document0.`document_id` )  INNER JOIN ts_c_11_4 ts_c_11_40 ON ts_document0.`number` = ts_c_11_40.`document_number` )  WHERE task0.`sys_class_name` = 'incident' AND ts_c_11_40.`word` IN (32784) ORDER BY document_number DESC  
Syntax Error or Access Rule Violation detected by database ((conn=xxx) Table 'instancename.ts_c_11_4' doesn't exist)

 Anyone encountered something similar before and can provide some input would be much appreciated! 

Thank you!

[Aniket Chavan]

Hello @KP28 ,

Based on the information provided, it seems that the error is related to the database query and the table ts_c_11_4 not being found. The script you shared is attempting to query the task table and join it with ts_c_11_4, but the error suggests that the latter table is either missing or not accessible.

If the ts_c_11_4 table is not needed for your specific use case, you might consider removing it from the script.

var search = "INC0024032";
var q = new GlideRecord('task');
q.addQuery('123TEXTQUERY321', search);
q.query();

You are querying the task table using the 123TEXTQUERY321 field, and it seems that the issue is related to the behind-the-scenes workings of the ServiceNow instance, possibly involving the full-text search index (ts_c_11_4). If ts_c_11_4 is not a table you explicitly need for your query, you might try simplifying your script:

var search = "INC0024032";
var q = new GlideRecord('task');
q.addQuery('number', search); // Assuming 'number' is the field you want to query
q.query();

This script focuses solely on the task table and the number field. Make sure to replace 'number' with the actual field you want to query against.

If you do need to use the ts_c_11_4 table, you will need to investigate why it's not found. This could involve checking the existence of the table, ensuring proper permissions, and reviewing any dependencies or configurations related to the ts_c_11_4 table. Depending on your findings, you might need to modify your script accordingly.

Let me know your views on this and Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks,

Aniket

[KP28]

Hi Aniket, 

Thank you for the reply. The issue actually arises from the OOTB search function in the "My Requests" widget which is failing for the client instance. And when I checked the server side script for the search, that's where I got to know about this "123TEXTQUERY321" search query. Out of curiosity, I tried to perform the same search query via background script, which led me to this error message. The table in error, ts_c_11_4, is not something I defined explicitly in the script ran, so I'm clueless as to where should I check next, would you know if there's anything else I should check? Or is this something I should raise as Hi Support?

Thanks again for your inputs!

[Aniket Chavan]

Hi @KP28 ,

You're welcome!

I'm glad to hear that you were able to resolve the issue by regenerating the text index for the Task table. It's great that you found a solution and shared the knowledge.

Best regards,

Aniket

[KP28 · Accepted solution]

I have managed to solve my issue by regenerating the text index for Task table by following this kb: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0812258

The text index for Task table was in Not Started state originally.