Input plugin issue in maintaining JDBC connections

Elastic Stack Logstash
1

Hi,

I am using JDBC input plugin for a connection to oracle database.

There are several sql files one for each jdbc input config file. Now, the issue is, logstash is successfully establishing a connection to oracle DB but it is never closing that connection. Because of these long connections, some of the sql requests are failing. This issue has become a nightmare and not able to find any relevant content.

Can someone please suggest the way to avoid this error. Basically, the connection to oracle should be closed after the particular sql has run.

2

Is it Logstash's queries that are failing? Because the other end has closed the connection and Logstash doesn't automatically reconnect? Or are the long-lived connections from Logstash causing problems for other systems connected to the same database?

3

My bad for the delayed response

Yes, it is the logstash queries that are failing due to these long-live connections.
for example, three of my logstash sql requests are configured to run daily at below cron expressions -
a) 0 0-12 * * *
b) 20 0-12 * * *
c) 40 0-12 * * *
Everyday, I see the below error in my log files -(strangely this error will be produced only till the third run of each query. Later to that, neither data will be loaded to elasticsearch nor error will be shown)

{:timestamp=>"2016-06-29T01:00:00.181000+0200", :message=>"Exception when executing JDBC query", :exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: ORA-01012: not logged on

, :level=>:warn}
{:timestamp=>"2016-06-29T01:20:00.286000+0200", :message=>"Exception when executing JDBC query", :exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: ORA-01012: not logged on
, :level=>:warn}
{:timestamp=>"2016-06-29T01:40:00.114000+0200", :message=>"Exception when executing JDBC query", :exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLException: ORA-01012: not logged on
, :level=>:warn}

As I am seeing this issue daily, it is clear that logstash is not trying to reconnect. Also, it is trying to using the same old session which means that it is not closing the previously opened one.

Sorry, I didnt give a try to check the option for Re-connecting as the underlying intention here is logstash should close the connection after sql request is complete. It is strictly against to our AUDIT policies to have long-live sessions.

To add-on, these are the different errors(related to connections) in logstash logs, I see daily -

  1. ORA-01012: not logged on
  2. ORA-02396: exceeded maximum idle time, please connect again
  3. DatabaseDisconnectError
  4. ORA-12801: error signaled in parallel query server , instance
    ORA-08103: object no longer exists
4

Which version of the jdbc input plugin do you have? It looks like automatic reconnection was added in PR #92, merged early March of this year and available in v3.0.1 of the plugin.

5

Thanks Magnus. But the idea here is not to retry connection. I am insisting more on disconnection of JDBC from oracle database once the SQL request is completed.

In other terms, if I configure my sql to run at cron expression - "0 0 * * *", then JDBC has to establish a new connection to oracle at 00:00 and disconnect from it once the sql has run.

6

Feel free to file a GitHub issue for this.

7

#147 opened. Thanks