Logstash update sql_last_value while using paging

Elastic Stack Logstash
1

sql_last_value update with Paging

  • I am configuring logstash to use jdbc input knowing that I am using jdbc_paging with the configuration.

  • what I am facing now is that sql_last_value is being updated after all records in the db is completed which is not what I want, I need to have sql_last_value being updated between paging such that in case logstash pod is down for any reason, it will be able to continue from the sql_last_value for which the last paging query finished successfully.

  • Is this something possible?

  • when I am not using the paging, and only default jdbc input, it is able to do that and I can see the last_metadata_path_file is being updated (using watch cat )

Current Logstash Configuration

        jdbc_user =>{{ include "logstash.database_username" . }}
        jdbc_password =>{{ include "logstash.database_password" . }}
        schedule => "*/5 * * * * *"
        statement => "select id,msg_text from msg_source where id > :sql_last_value"
        jdbc_paging_enabled => true
        jdbc_page_size => 2000
        use_column_value => true
        tracking_column_type => "numeric"
        tracking_column => "id"
        last_run_metadata_path => "/usr/share/logstash/pmw-sql-last-value/{{ .Release.Name }}/messages_sql_last_value.yml"
  • I can see that many users are facing the same case.

I really appreciate any help you can provide.

2

Update:

I have tested my configuration on MSSQL DB and It is updating correctly, but when I tested it on oracle db, with paging, the sql_last_value is not updated on the file used in last_run_metadata_path until all no more records are available in DB.

3

That is just how it works. The paged statement handler loops over pages until it gets a page that is not full. The input updates sql_last_value after the statement handler completes the query.

4

@Badger

Thank you for your fast response.

But why it is working with MSSQL and not with Oracle DB? Could be that ojdbc needs some parameters to be passed?

I can see that file is being updated while the paging is processing 10K messages but with Oracle, it is not.

Much Respect.