Pagination not working in v5.4.4

[ahmadabulaban]

Logstash information:

Please include the following information:

1. Logstash version: 8.9.0
2. Logstash installation source: docker (opensearchproject/logstash-oss-with-opensearch-output-plugin:8.9.0)
3. How is Logstash being run: docker
4. How was the Logstash Plugin installed: included in logstash core v8.9.0

JVM (e.g. java -version): openjdk 17.0.7

OS version (uname -a if on a Unix-like system): GNU/Linux

Description of the problem including expected versus actual behavior: Upon using logstash v8.6.1 (which include jdbc input plugin v5.4.1) the pagination was working fine, and a count query executed in case of default jdbc_paging_mode (auto) selected. Now after upgrade to logstash v8.9.0 (which include jdbc input plugin v5.4.4) the pagination not worked at all, and a normal select statement executed which lead to load all data into memory and kill the container

Provide logs (if relevant):

With logstash v8.6.1

[2023-09-04T22:36:50,783][INFO ][logstash.inputs.jdbc ][main][31d8329f5a2cc1b3fa649cade041132bfb88ed84629009d9eb291a6633048e2d] (0.001862s) SELECT TOP (1) count(*) AS [COUNT] FROM (select 'message_entity' as source_resource,id from message_entity where id > 0) AS [T1]

With logstash v8.9.0

[2023-09-04T22:29:50,386][INFO ][logstash.inputs.jdbc ][main][b05c0d0f2331e3b1f3a4d986a7ef3e6330ace05b24fa3c43f6efbf745a877e0a] (0.001105s) select 'message_entity' as source_resource,id from message_entity where id > 0

The input plugin used in both version as following

input {
      jdbc {
        jdbc_driver_library => "{{ .Values.db.jdbc_driver_library }}"
        jdbc_driver_class => "{{ .Values.db.jdbc_driver_class }}"
        jdbc_connection_string => "jdbc:sqlserver://{{ .Values.db.serviceName}}:{{ .Values.db.servicePort}};database={{ .Values.db.database | default (include "logstash.database_username" .) }};encrypt=true;trustServerCertificate=true;"
        jdbc_user =>{{ include "logstash.database_username" . }}
        jdbc_password =>{{ include "logstash.database_password" . }}
        schedule => "*/10 * * * * *"
        statement => "select 'message_entity' as source_resource,id from message_entity where id > :sql_last_value"
        jdbc_paging_enabled => true
        jdbc_page_size => 2000
        use_column_value => true
        tracking_column_type => "numeric"
        tracking_column => "id"
        jdbc_paging_mode => "auto"
        last_run_metadata_path => "/usr/share/logstash/pmw-sql-last-value/{{ .Release.Name }}/message_entity_sql_last_value.yml"
      }
}

[ahmadabulaban]

Please note that issue still exist in v5.4.9 too. The auto mode not working as before (in v5.4.1) so if you've a lot of records in DB they will all loaded into the memory

[slav4ik51493]

any updates or workaround?

[slav4ik51493]

I've found a way to fix this behavior.
I've used "explicit" mode like this

jdbc_paging_mode => "explicit"
 jdbc_page_size => 1000

and updated my sql request

 SELECT 
your_tracking_column,
columnA
FROM your_table
 ORDER BY your_tracking_column
 OFFSET :offset ROWS FETCH NEXT :size ROWS ONLY