Gateway - What does the error message "ERROR: SQL Error in Stage: 'Extract xxx' mean in Gateway SQL

Tip 1 Copied

It could be that the dataview sources are not available, it could be a license issue, or there is a duplicate rowname among the sources (when using UNION statement).

Information for tip 1 Copied

If you encounter this issue, first, you need to ensure that the dataview sources are available and that the license daemon is running with available tokens for Gateway SQL. If these checks are true and you still encounter the issue, then it is possible that you are using UNION statements and there are duplicate rownames in the dataview sources.

For example, you have source T1:

And source T2:

And you want to select all the rows from both sources using the UNION statement. But since there are duplicate rownames (ID=2), you will get the error:

select * from T1 union all select * from T2

To solve this issue, you can tick the Show row id option.

Tip 2 Copied

The select query might be using SQLITE reserve keywords such as Group.

Information for tip 2 Copied

To troubleshoot this, do the following:

1. Enable the Gateway-SQL plugin’s statusDetails and showQueries debug settings:

2. Save the configuration and open the Gateway logs.
3. Search for the keyword Gateway-sql::sql Details to extract the following log entries:

<Mon Jan 20 10:10:10> DEBUG: Gateway-sql::sql [Extract Status]: 'SELECT FileNo, Group, FileName FROM Custom_Table' <Mon Jan 20 10:10:10> DEBUG: Gateway-sql::sql Details of Sampler ERROR Message::     Sampler: '/geneos/gateway[(@name="my_gateway")]/directory/probe[(@name="my_probe")]/managedEntity[(@name="my_entity")]/sampler[(@name="my_sampler")][(@type="not_my_type")]'     View: 'Status'     Stage: 'Extract Status'     Message: 'near "Group": syntax error'

Using the above, the Message parameter highlights that the error was caused by a syntax error. The syntax error was caused by not escaping the reserve keyword Group. To solve this, the said reserve keyword needs to be enclosed in double quotes (").