Syslog showing many Invalid query detected . . .

[Zuber1]

Hello,

We are receiving these logs and have checked the sc_item_option, item_option_new tables, as well as related business rules and script includes, with no root cause.

The sys_ids correlate to variables in the item_option_new table, are active, and belong to a variety of active catalog items.

Below is a sampling of the errors:

Invalid query detected, please check logs for details [Unknown field 1b615096db43f7001e317722ba96190a in table sc_item_option]
Invalid query detected, please check logs for details [Unknown field ad252c3adbff334065d98fd33996196b in table sc_item_option]
Invalid query detected, please check logs for details [Unknown field 116509bf1b87e99871af5beb234bcb7c in table sc_item_option]

Has anyone else encountered errors like these? Any ideas?

[jcmings]

If you have SN Utils, /vd on the record and view the Context Map field. I don't believe it gives you the exact source but it should give you a clue. If you don't have SN Utils, try pulling that field onto the log entry record.

According to KB0863715, there may be some BR on sc_item_option that uses gr.next() outside of a conditional statement. I'd also search for BRs that fire on that table.

Could the fields referenced by the sys_id's be deleted? If I recall, usually the label of the field displays in those logs rather than the sys_id. Is it possible that these fields were deleted?

[Zuber1]

Thank you for the quick response. 
I've checked the context map field - found that it was triggered by sc_task modifications and query logger. But not consistently.

I've checked for all the business rules and script includes that affect that table for gr.next(), including, item_option_new, sc_req_item, sc_task, etc, disabled them, and the issue recurs.

The sys_ids are not found in sc_item_option, but are found in item_option_new, and do correlate to active/current variables. Just have not figured out where to determine why this list of sys_ids is being checked against sc_item_option.

[JC Moller · Accepted solution]

Use the Node log browser and search for the "Invalid query detected" string. There is a lot of additional data stored in the localhost-logs when the system runs into these issues. This is a frequent finding in the logs on almost all instances I have worked on. Even SN developers refer to columns on tables that do not exist, make typos in the scripts etc. Check the node logs if it provides the additional data you need to resolve this.

Examples:

Thread dumps provide additional information about the code that triggered the error:

There is a system property, glide.invalid_query.returns_no_rows, that prevents invalid queries from being run if the query contains syntax errors. Check to see if you need to activate this setting. The GlideQuery API has a built-in capability to prevent invalid queries from running OOB.

https://www.youtube.com/watch?v=XQxZnRwKcws

[Zuber1]

@JC Moller, 

Taking another look at the thread dumps revealed a clue that pointed back to notifications.

We have a handful of notifications that use an item and item variable as a condition, using the OOB condition builder. For whatever reason, when we use variables/values as a condition, then the system goes through each variable on all the notifications, and logs an error, even when an item included as a condition.

We're resolving it for now by removing the variables from the condition builder and checking them in an advanced view script condition, here's an example confirming the variable exists and has a value of "no":

if (JSUtil.notNil(current.variables.variable_name) &&

    current.variables.variable_name == 'no') {

    answer = true;

} else {

    answer = false;

}

Thank you for your help!