[Logstash7.0.1] lError occurs when connecting to database

cheriemilk · October 8, 2019, 8:33am

Hi Team,

I have below logstash configuration file by trying to connect to SAP HANA database(HANA DB and elk stack are not on same laptop). But there's error when executing logstash -f ../config/logstashJDBC.conf.

input {
	jdbc{
	     jdbc_connection_string => "jdbc:sap://qa13hdbbe901.lab.od.sap.biz:30115"
	     jdbc_driver_library => "/usr/sap/hdbclient/"
	     jdbc_driver_class => "com.sap.db.jdbc.Driver"
	     jdbc_user => "SF_READONLY"
	     jdbc_password => "Read0n1ysf123"
	     statement => "SELECT NOMINEE_ID, NOMINATION_ID, NOMINEE_USER_ID, READINESS, CREATED_DATE, CREATED_BY FROM SFBIZX3_TALSHELLEYH.SM_NOMINEE"
	     use_column_value => true

	}
}



output { 
         elasticsearch {
	                     action => "index"
	                     hosts  => "localhost:9200"
	                     index  => "testjdbc"
                         manage_template => true
                         template => "C:/elkstack/elasticsearch-7.0.1-windows-x86_64/mapping/dynamic.json"
                         template_name=> "dynamic"
                         template_overwrite => true
                         document_id => "%{NOMINEE_ID}"
         	           }
         stdout { codec => rubydebug }
        }

Error is

[2019-10-08T16:28:34,386][DEBUG][logstash.outputs.stdout  ] Closing {:plugin=>"LogStash::Outputs::Stdout"}
[2019-10-08T16:28:34,392][DEBUG][logstash.outputs.elasticsearch] Closing {:plugin=>"LogStash::Outputs::ElasticSearch"}
[2019-10-08T16:28:34,411][DEBUG][logstash.outputs.elasticsearch] Stopping sniffer
[2019-10-08T16:28:34,417][DEBUG][logstash.outputs.elasticsearch] Stopping resurrectionist
[2019-10-08T16:28:34,931][DEBUG][logstash.outputs.elasticsearch] Waiting for in use manticore connections
[2019-10-08T16:28:34,946][DEBUG][logstash.outputs.elasticsearch] Closing adapter #<LogStash::Outputs::ElasticSearch::HttpClient::ManticoreAdapter:0x3fd3c880>
[2019-10-08T16:28:34,959][ERROR][logstash.javapipeline    ] Pipeline aborted due to error {:pipeline_id=>"main", :exception=>#<LogStash::ConfigurationError: Must set :tracking_column if :use_column_value is true.>, :backtrace=>["C:/elkstack/logstash-7.0.1/logstash-7.0.1/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/inputs/jdbc.rb:212:in `register'", "C:/elkstack/logstash-7.0.1/logstash-7.0.1/logstash-core/lib/logstash/java_pipeline.rb:191:in `block in register_plugins'", "org/jruby/RubyArray.java:1792:in `each'", "C:/elkstack/logstash-7.0.1/logstash-7.0.1/logstash-core/lib/logstash/java_pipeline.rb:190:in `register_plugins'", "C:/elkstack/logstash-7.0.1/logstash-7.0.1/logstash-core/lib/logstash/java_pipeline.rb:280:in `start_inputs'", "C:/elkstack/logstash-7.0.1/logstash-7.0.1/logstash-core/lib/logstash/java_pipeline.rb:244:in `start_workers'", "C:/elkstack/logstash-7.0.1/logstash-7.0.1/logstash-core/lib/logstash/java_pipeline.rb:145:in `run'", "C:/elkstack/logstash-7.0.1/logstash-7.0.1/logstash-core/lib/logstash/java_pipeline.rb:104:in `block in start'"], :thread=>"#<Thread:0x5b156470 run>"}
[2019-10-08T16:28:34,988][ERROR][logstash.agent           ] Failed to execute action {:id=>:main, :action_type=>LogStash::ConvergeResult::FailedAction, :message=>"Could not execute action: PipelineAction::Create<main>, action_result: false", :backtrace=>nil}
[2019-10-08T16:28:34,998][TRACE][logstash.agent           ] Converge results {:success=>false, :failed_actions=>["id: main, action_type: LogStash::PipelineAction::Create, message: Could not execute action: PipelineAction::Create<main>, action_result: false"], :successful_actions=>[]}
[2019-10-08T16:28:35,057][DEBUG][logstash.agent           ] Starting puma
[2019-10-08T16:28:35,076][DEBUG][logstash.agent           ] Trying to start WebServer {:port=>9600}
[2019-10-08T16:28:35,095][DEBUG][logstash.instrument.periodicpoller.os] Stopping
[2019-10-08T16:28:35,141][DEBUG][logstash.instrument.periodicpoller.jvm] Stopping
[2019-10-08T16:28:35,146][DEBUG][logstash.instrument.periodicpoller.persistentqueue] Stopping
[2019-10-08T16:28:35,149][DEBUG][logstash.instrument.periodicpoller.deadletterqueue] Stopping

Anyone can advice?

Badger · October 8, 2019, 12:39pm

You are asking the jdbc input to use a column value to track which rows have been read but not telling it which column to use.

cheriemilk · October 10, 2019, 7:21am

@Badger Thanks, trying

cheriemilk · October 15, 2019, 3:30am

@Badger, I've updated the tracking_column

> input {
> 	jdbc{
> 	     jdbc_connection_string => "jdbc:sap://qa13hdbbe901.lab.od.sap.biz:30115"
> 	     jdbc_driver_library => "/app/tomcat/jdbc_lib"
> 	     jdbc_driver_class => "com.sap.db.jdbc.Driver"
> 	     jdbc_user => "xxx"
> 	     jdbc_password => "xxx"
> 	     statement => "SELECT NOMINEE_ID, NOMINATION_ID, NOMINEE_USER_ID, READINESS, CREATED_DATE, CREATED_BY FROM SFBIZX3_TALSHELLEYH.SM_NOMINEE"
> 	     use_column_value => true
> 	     tracking_column => "NOMINEE_ID"
> 	     tracking_column_type => numeric
> 
> 	}
> }

Now I got the JDBC error. Please advice! Does logstash jdbc input support SAP HANA database?

[2019-10-15T13:15:48,683][ERROR][logstash.javapipeline    ] A plugin had an unrecoverable error. Will restart this plugin.
  Pipeline_id:main
  Plugin: <LogStash::Inputs::Jdbc jdbc_user=>"SF_READONLY", use_column_value=>true, tracking_column=>"NOMINEE_ID", jdbc_password=><password>, statement=>"SELECT NOMINEE_ID, NOMINATION_ID, NOMINEE_USER_ID, READINESS, CREATED_DATE, CREATED_BY FROM SFBIZX3_TALSHELLEYH.SM_NOMINEE", tracking_column_type=>"numeric", jdbc_driver_library=>"/app/tomcat/jdbc_lib", jdbc_connection_string=>"jdbc:sap://qa13hdbbe901.lab.od.sap.biz:30115", id=>"4a9ee1ee41b1f9376ca7e0c2d199233d62c0e09cd555272d527de0745ee102c8", jdbc_driver_class=>"com.sap.db.jdbc.Driver", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_212f3f66-0328-42f0-87e0-7b078904b8e9", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_size=>100000, jdbc_validate_connection=>false, 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"=>0}, last_run_metadata_path=>"C:\\Users\\I073341/.logstash_jdbc_last_run", clean_run=>false, record_last_run=>true, lowercase_column_names=>true>
  Error: com.sap.db.jdbc.Driver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
  Exception: LogStash::ConfigurationError
  Stack: C:/elkstack/logstash-7.0.1/logstash-7.0.1/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/jdbc.rb:163:in `open_jdbc_connection'

By the way, the HANA DB and elk stack is not on same machine. elk stack is setup in my local laptop. HANA DB is in another machine.

Badger · October 15, 2019, 2:39pm

I would expect that to point to a jar file.

cheriemilk · October 16, 2019, 7:34am

@Badger, updated and run again, still get the same error. I searched this error in this forum, and found that lots of user meet this error, but most of them are closed without solution or response.

jdbc{
     jdbc_driver_library => "/app/tomcat/jdbc_lib/ngdbc-2.4.61.jar"
         jdbc_driver_class => "com.sap.db.jdbc.Driver"
     jdbc_connection_string => "jdbc:sap://qa13hdbbe901.lab.od.sap.biz:30115"
     jdbc_user => "xxx"
     jdbc_password => "xxx"
         ...} 

[2019-10-16T15:31:39,226][ERROR][logstash.javapipeline    ] A plugin had an unrecoverable error. Will restart this plugin.
  Pipeline_id:main
  Plugin: <LogStash::Inputs::Jdbc jdbc_user=>"xxx", use_column_value=>true, tracking_column=>"NOMINEE_ID", jdbc_validate_connection=>true, jdbc_password=><password>, statement=>"SELECT NOMINEE_ID, NOMINATION_ID, NOMINEE_USER_ID, READINESS, CREATED_DATE, CREATED_BY FROM SFBIZX3_TALSHELLEYH.SM_NOMINEE", tracking_column_type=>"numeric", jdbc_driver_library=>"/app/tomcat/jdbc_lib/ngdbc-2.4.61.jar", jdbc_connection_string=>"jdbc:sap://qa13hdbbe901.lab.od.sap.biz:30115", id=>"239d99b038d185ac811062a18a344f7ccd7ca811cd122b99f98c086968a5b55c", jdbc_driver_class=>"Java::com.sap.db.jdbc.Driver", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_70cb23ec-f1ec-4c21-9770-6240751ed824", enable_metric=>true, charset=>"UTF-8">, jdbc_paging_enabled=>false, jdbc_page_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"=>0}, last_run_metadata_path=>"C:\\Users\\I073341/.logstash_jdbc_last_run", clean_run=>false, record_last_run=>true, lowercase_column_names=>true>
  Error: Java::com.sap.db.jdbc.Driver not loaded. Are you sure you've included the correct jdbc driver in :jdbc_driver_library?
  Exception: LogStash::ConfigurationError
  Stack: C:/elkstack/logstash-7.0.1/logstash-7.0.1/vendor/bundle/jruby/2.5.0/gems/logstash-input-jdbc-4.3.13/lib/logstash/plugin_mixins/jdbc/jdbc.rb:163:in `open_jdbc_connection'

cheriemilk · October 17, 2019, 7:40am

@Badger S
Should I install JDBC dirver locally , along with elk stack in same machine?

Badger · October 17, 2019, 3:27pm

You could try adding the driver jar file to <logstash install dir>/logstash-core/lib/jars/, but be aware that could get removed during a logstash upgrade.

cheriemilk · October 18, 2019, 9:32am

@Badger, It works! Thanks. But it doesn't mention this important step in the official document.

system · November 15, 2019, 9:32am

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

Topic		Replies	Views
JDBC Connection to SAP HANA Logstash	1	1597	April 6, 2018
ELK Integration with SAP HANA Logstash	1	2084	July 3, 2020
Logstash.inputs.jdbc Unable to connect to database Logstash	2	3071	September 30, 2019
Logstash Error while configuring the JDBC connection Logstash	1	196	November 30, 2020
Issue connecting logstash with Oracle db Logstash	1	508	July 6, 2017

[Logstash7.0.1] lError occurs when connecting to database

Related topics