Logstash JDBC Input PREPARE on every run

Elastic Stack Logstash
1

Hi,

Since upgrading from Logstash 8.7.1 to 8.17.2 I've noticed that the PREPARE statement is run every time (followed by the EXECUTE), where as previously PREPARE would run once at start up, and EXECUTE would run every minute. Source is Oracle 19c, and logstash is running using the official container.

Here's one of my inputs, but this is happening on all where use_prepared_statements is true:

  jdbc {
    jdbc_driver_library            => '/opt/logstash/config/jdbc/ojdbc11.jar'
    jdbc_driver_class              => 'Java::oracle.jdbc.driver.OracleDriver'
    jdbc_connection_string         => "jdbc:oracle:thin:@${DB_HOSTNAME}:${DB_PORT}:${DB_SID}"
    jdbc_user                      => "${DB_USER}"
    jdbc_password                  => "${DB_PASSWORD}"
    schedule                       => '* * * * *'
    last_run_metadata_path         => '/opt/logstash/lastrun/my_query'
    statement_filepath             => '/opt/logstash/sql/my_query.sql'
    prepared_statement_name        => "my_query"
    use_prepared_statements        => true
    add_field                      => {
      ...some tags
    } 
  }

This feels like a bug, but if I'm doing something wrong, I'd love to know.

Thanks,
Dave

2

I haven't been through it in detail, but I suspect it is related to this PR. Basically, it creates a new database connection for every scheduled execution, and it does not surprise me in the least that that would require doing the prepare again.

I don't think you can opt out. The prepared statement and the connection are linked. If one is replaced the other has to be.

Hopefully your database does prepared statement caching server-side.

3

Thanks @Badger - I see the change was to resolve another issue so it makes sense. The PREPARE statements appear to be cached as the execution time is very small. Appreciate you looking into it.