search

logstash-plugins / logstash-filter-jdbc_streaming

A Logstash filter that can enrich events with data from a database
Apache License 2.0
12 stars 23 forks source link

Oracle SQLRecoverableException #6

Open kirill1722 opened 7 years ago

kirill1722 commented 7 years ago

Hello

After some time we get the following Exception and Data is lost.

Exception in pipelineworker, the pipeline stopped processing new events, please check your filter configuration and restart Logstash "exception"=>java.sql.SQLRecoverableException: No more data to read from socket

Version Oracle 11.2.0.4.0

jdbc_streaming {
    jdbc_connection_string => "${xxx}"
    jdbc_user => "${xxx}"
    jdbc_password => "${xxx}"
    jdbc_validate_connection => true
    jdbc_driver_library => "/usr/share/logstash/ojdbc7-12.1.0.2.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    statement => "select xxx"
    parameters => { "unr" => "xxx"}
    target => "xxx"
}

To fix it the plugin should handle Network or Sql Exceptions with retry. Or validate / refresh connection when SQL Exception occurs.

Thanks

guyboertje commented 7 years ago

@kirill1722

This looks like a DB / driver issue.

I kinda agree that there should be some protection against this, however the plugin is new and we have not had an opportunity to collect a list errors are really retry worthy.

Some Logstash plugins do have a whitelist of exceptions worthy of a retry but those lists are collected on the battlefield. There are actually very many, maybe 100s, exceptions that can be thrown and there is no definitive list.

Question: as this is event enhancement, would it be acceptable to trap/rescue any error and tag the event and move on? With this behaviour one could implement your own retry by having a second identical jdbc_streaming instance inside a conditional block that tests for the exception tag.

kirill1722 commented 7 years ago

@guyboertje

My Logstash App is running inside Openshift / Kubernetes Pod, Docker Container. It resolves some Foreign Keys, Lookups, Data Denormalization of some business data. So I have to restart it, if I know that some Data is missing.

I have the following ideas:

1. Is it possible that the plugin kills Logstash, if this error occurs? The error says "..restart logstash"

The liveness check of Openshift would restart the Container, if Logstash would not responding.

2. There is new Monitoring API from Logstash. So a (custom) Monitoring App could detect that the pipeline is broken and restart the container. Didnt try this out. https://www.elastic.co/guide/en/logstash/current/monitoring.html

  1. reducing jdbc_connection_timeout. So maybe it reconnects.

  2. retry mechanism which you have described

  3. The plugin refreshes the connections if this error occurs.

  4. using http filter plugin for lookups