Logstash error with jdbc connection


(rudra) #1

Unable to connect to oracle database from a logstash conf file:

input {
jdbc {
jdbc_validate_connection => true
jdbc_connection_string => "jdbc:oracle:thin:@:1522/"
jdbc_user => "<>"
jdbc_password => "<>"
jdbc_driver_library => "/config-dir/ojdbc7-12.1.0.2.0.jar"
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
#use_column_value => true
#tracking_column => executiondate
statement=>"select testcase from QE_EXECUTIONTABLE"
}
}

output {
elasticsearch {

      hosts => "elasticsearch:9200"
}

    stdout{codec=>json_lines}
    #stdout{}

I start the logstash like this:

docker run -h logstash --name logstash --link elasticsearch:elasticsearch -it --rm -v "$PWD":/config-dir logstash -f /config-dir/logstash.conf

I get errors continuously:
docker run -h logstash --name logstash --link elasticsearch:elasticsearch -it --rm -v "$PWD":/config-dir logstash -f /config-dir/logstash.conf
ERROR StatusLogger No log4j2 configuration file found. Using default configuration: logging only errors to the console.
Sending Logstash's logs to /var/log/logstash which is now configured via log4j2.properties
18:38:37.487 [main] INFO logstash.setting.writabledirectory - Creating directory {:setting=>"path.queue", :path=>"/var/lib/logstash/queue"}
18:38:37.491 [main] INFO logstash.setting.writabledirectory - Creating directory {:setting=>"path.dead_letter_queue", :path=>"/var/lib/logstash/dead_letter_queue"}
18:38:37.512 [LogStash::Runner] INFO logstash.agent - No persistent UUID file found. Generating new UUID {:uuid=>"f040e3a4-b40f-41b2-bafb-689c68ae6ba5", :path=>"/var/lib/logstash/uuid"}
18:38:37.829 [[main]-pipeline-manager] INFO logstash.outputs.elasticsearch - Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://elasticsearch:9200/]}}
18:38:37.829 [[main]-pipeline-manager] INFO logstash.outputs.elasticsearch - Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://elasticsearch:9200/, :path=>"/"}
18:38:37.893 [[main]-pipeline-manager] WARN logstash.outputs.elasticsearch - Restored connection to ES instance {:url=>#Java::JavaNet::URI:0x8ecb39e}
18:38:37.895 [[main]-pipeline-manager] INFO logstash.outputs.elasticsearch - Using mapping template from {:path=>nil}
18:38:38.048 [[main]-pipeline-manager] INFO logstash.outputs.elasticsearch - Attempting to install template {:manage_template=>{"template"=>"logstash-", "version"=>50001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"default"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
18:38:38.053 [[main]-pipeline-manager] INFO logstash.outputs.elasticsearch - New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>[#Java::JavaNet::URI:0x323cad72]}
18:38:38.056 [[main]-pipeline-manager] INFO logstash.pipeline - Starting pipeline {"id"=>"main", "pipeline.workers"=>6, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>750}
18:38:38.146 [[main]-pipeline-manager] INFO logstash.pipeline - Pipeline main started
18:38:38.190 [Api Webserver] INFO logstash.agent - Successfully started Logstash API endpoint {:port=>9600}
18:38:58.096 [[main]<jdbc] WARN logstash.inputs.jdbc - Failed test_connection.
18:38:58.099 [[main]<jdbc] ERROR logstash.pipeline - A plugin had an unrecoverable error. Will restart this plugin.
Plugin: <LogStash::Inputs::Jdbc jdbc_validate_connection=>true, jdbc_connection_string=>"jdbc:oracle:thin:@dentsd3dlpx04.test.tiaa-cref.org:1522/suu004", jdbc_user=>"qprobews", jdbc_password=>, jdbc_driver_library=>"/config-dir/ojdbc7-12.1.0.2.0.jar", jdbc_driver_class=>"Java::oracle.jdbc.driver.OracleDriver", statement=>"select testcase from QE_EXECUTIONTABLE", id=>"58ac81bab11ca47886f1a0bb6a1c547e10b82d36-1", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_a81cecb5-dec0-4a2d-a32a-47601b5e72e1", 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"=>1970-01-01 00:00:00 UTC}, last_run_metadata_path=>"/usr/share/logstash/.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:0x1b8ac9b9
18:38:59.213 [[main]<jdbc] WARN logstash.inputs.jdbc - Exception when executing JDBC query {:exception=>#<Sequel::DatabaseConnectionError: Java::JavaSql::SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01882: timezone region not found

}
18:38:59.213 [[main]<jdbc] WARN logstash.inputs.jdbc - Attempt reconnection.
18:38:59.332 [[main]<jdbc] WARN logstash.inputs.jdbc - Failed test_connection.
18:38:59.334 [[main]<jdbc] ERROR logstash.pipeline - A plugin had an unrecoverable error. Will restart this plugin.
Plugin: <LogStash::Inputs::Jdbc jdbc_validate_connection=>true, jdbc_connection_string=>"jdbc:oracle:thin:@dentsd3dlpx04.test.tiaa-cref.org:1522/suu004", jdbc_user=>"qprobews", jdbc_password=>, jdbc_driver_library=>"/config-dir/ojdbc7-12.1.0.2.0.jar", jdbc_driver_class=>"Java::oracle.jdbc.driver.OracleDriver", statement=>"select testcase from QE_EXECUTIONTABLE", id=>"58ac81bab11ca47886f1a0bb6a1c547e10b82d36-1", enable_metric=>true, codec=><LogStash::Codecs::Plain id=>"plain_a81cecb5-dec0-4a2d-a32a-47601b5e72e1", 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"=>1970-01-01 00:00:00 UTC}, last_run_metadata_path=>"/usr/share/logstash/.logstash_jdbc_last_run", use_column_value=>false, tracking_column_type=>"numeric", clean_run=>false, record_last_run=>true, lowercase_column_names=>true>


(Guy Boertje) #2

From http://www.orafaq.com/wiki/JDBC#Thin_driver

SID (no longer recommended by Oracle to be used):
jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>
Services:
jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>
TNSNames:
jdbc:oracle:thin:[<user>/<password>]@<TNSName>

There is no reliable universal URL format, it must be decided according to the server configuration.

the connection string should look like:
jdbc:oracle:thin:qprobews/password@//dentsd3dlpx04.test.tiaa-cref.org:1522/suu004


(system) #3

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