Logstash with jdbc input for Oracle DB


(Jyothi G) #1

I updated jdbc details as below:

    jdbc_driver_library => "../ojdbc6.jar"
    jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin@//<<my-host>>:1521/<<my-sid>>"

The user and pass word are provided in jdbc_user and jdbc_password configurations correctly.

When I run this, I am getting following error:

Pipeline aborted due to error {:exception=>"Sequel::DatabaseConnectionError", :backtrace=>["oracle.jdbc.driver.OracleDriver.connect(oracle/jdbc/driver/OracleDriver.java:423)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", "RUBY.connect(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/adapters/jdbc.rb:222)", "RUBY.make_new(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool.rb:116)", "RUBY.make_new(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:228)", "RUBY.available(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:201)", "RUBY._acquire(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:137)", "RUBY.acquire(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:151)", "RUBY.sync(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:282)", "org.jruby.ext.thread.Mutex.synchronize(org/jruby/ext/thread/Mutex.java:149)", "RUBY.sync(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:282)", "RUBY.acquire(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:150)", "RUBY.acquire(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/extensions/connection_validator.rb:98)", "RUBY.hold(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:106)", "RUBY.synchronize(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/database/connecting.rb:256)", "RUBY.test_connection(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/database/connecting.rb:266)", "RUBY.prepare_jdbc_connection(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc-3.1.0/lib/logstash/plugin_mixins/jdbc.rb:173)", "RUBY.register(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc-3.1.0/lib/logstash/inputs/jdbc.rb:187)", "RUBY.start_inputs(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/pipeline.rb:330)", "org.jruby.RubyArray.each(org/jruby/RubyArray.java:1613)", "RUBY.start_inputs(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/pipeline.rb:329)", "RUBY.start_workers(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/pipeline.rb:180)", "RUBY.run(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/pipeline.rb:136)", "RUBY.start_pipeline(C:/InsightExplorer/ElasticSearch-inSightExplorer/logstash-2.4.0-insight/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/agent.rb:491)", "java.lang.Thread.run(java/lang/Thread.java:748)"], :level=>:error}e[0m
stopping pipeline {:id=>"main"}
The signal HUP is in use by the JVM and will not work correctly on this platform

To get more verbose on the logged error, based on response from logstash plugin manager on link Logstash Oracle JDBC issue, tried this with Logstash 5.0 , and below error is received

[2017-07-24T15:12:02,486][ERROR][logstash.pipeline ] A plugin had an unrecoverable error. Will restart this plugin.
Plugin: <LogStash::Inputs::Jdbc jdbc_driver_library=>"../ojdbc6.jar", jdbc_driver_class=>"Java::oracle.jdbc.driver.OracleDriver", jdbc_connection_string=>"jdbc:oracle:thin@\\my-host:1521\my-sid", jdbc_user=>"myuserid", jdbc_password=>, jdbc_validate_connection=>true, statement=>"SELECT AGENC.AG_ID,AGENC.AG_NME FROM AGENCY", type=>"cadagencies", id=>"bfff23913d32e7f49b26f1
136daf1fce22cf7156-1", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_50e5f4f1-fbe5-4de1-a96f-8953840c284c", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_p
age_size=>100000, jdbc_validation_timeout=>3600, jdbc_pool_timeout=>5, sql_log_level=>"info", connection_retry_attempts=>1, connection_retry_attempts_wait_time=>0.5, parameters=>{"sql_last_value"=>201
7-07-20 10:40:24 UTC}, last_run_metadata_path=>"C:\Users\nborra/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase
_column_names=>true>
Error: undefined method `close_jdbc_connection' for #Sequel::JDBC::Database:0x7118d17
[2017-07-24T15:12:03,489][WARN ][logstash.inputs.jdbc ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseConnectionError: Java::JavaSql::SQLException: Invalid Oracle URL specifie
d>}
[2017-07-24T15:12:24,396][WARN ][logstash.inputs.jdbc ] Attempt reconnection.

Error: undefined method `close_jdbc_connection' for #Sequel::JDBC::Database:0x7118d17
<Sequel::DatabaseConnectionError: Java::JavaSql::SQLException: Invalid Oracle URL specifie
d>}

Can you please help me what is that I am missing?


(Jyothi G) #2

Figured it out, I missed the : after thin in jdbc connection and I did not need // after @. So, the below one worked for me, @jdbc:oracle:thin:@<<>my_hots>:1521:<<my_sid>> worked


(system) #3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.