Why I am getting this error when using JDBC input plugin of Logstash

Elastic Stack Logstash
1

Here is the query I had put in the logstash conf file:

select a.ID, a.TransactionID, b.Result 
from MyDB.Result a inner join MyDB.ResultData b on a.ID=b.ID 
where a.ID < :sql_last_value and a.CreatedOn > '2020-01-01' 
order by a.ID

This is warning I get:
[2020-06-22T11:29:01,084][ERROR][logstash.inputs.jdbc ][main][c453478trgergy8gh78og6ogy8wef7834g78o9b5] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.>}

This is the line listed:
SELECT TOP (1) count(*) AS [COUNT] FROM (select a.ID, a.TransactionID, b.Result from MyDB.Result a inner join MyDB.ResultData b on a.ID=b.ID where a.ID < 100000 and a.CreatedOn > '2020-01-01' order by a.ID) AS [T1]

I removed the inner join and just put in a simple select statement but still it is not working with the ORDER BY clause.

Without ORDER BY, the whole logic will fail.
As per this thread, ORDER BY should fine.

Is it something to do with the Microsoft SQL server which is my source database?

Logstash version: 7.7.0
SQL server version: Microsoft SQL Server 2014 (SP2-CU1) (KB3178925) - 12.0.5511.0 (X64) Aug 19 2016 14:32:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

EDIT
Just went through this thread.
Looks like he got around this issue by specifying
SELECT TOP 638858

Somehow I feel it is not the optimum solution.

2

this error is coming from the database itself, so yes, MSSQL refuses to execute the query

3

Oh dear.
Is there any way out then to keep pulling data out of MSSQL server?
Any ideas how to go about this one then? I am sure I am not the first one to hit this roadblock.
Is this behaviour specific to MSSQL?

Is there any way I can find the last value and assign it explicity to the :sql_last_value?

EDIT:
I asked about this on Stackoverflow and they said that it is happening since my query is getting embedded inside a larger query. Is there something future releases of Elastic can do to make life easier?

4

I know that in your case you are not calling a stored procedure, but I think this solution could apply to your problem as well as the plugin apparently only creates the "SELECT TOP (1) … " query in debug mode?

5

Hi @Jenni,

I tried with --log.level=info but I still see the same error.
Beats me how others are able to use it.
I must be missing out on some configuration I think.

This is my config:

jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://servername:9999;databaseName=Somethign"
jdbc_user => ""
jdbc_password => ""
tracking_column => "ID"
use_column_value => true
tracking_column_type => "numeric"
schedule => "*/1 * * * *"
jdbc_paging_enabled => "true"
jdbc_default_timezone => "Australia/Sydney"
jdbc_page_size => "500"
last_run_metadata_path => "/Data/path/logstash_jdbc_last_run"
6

Does it work without paging?

7

Yikes !!! It looks like it is working. No error yet !!

Will removing jdbc_paging_enabled increase the load on the SQL database ? My understanding was that it would break up a big query into smaller ones which probably be lighter on Database.

8

I would ignore the error. It tries to do a count, if it fails then it stops trying. More details here.

9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.