Logstash 2.4.0 gets Sequel::DatabaseConnectionError in JDBC Input

Elastic Stack Logstash
1

Hi,

I have a docker image of ELK 4 stack with logstash 2.4.0 installed.

In that container I have configured logstash with the following configuration:

input {
  beats {
    ssl => false
    host => "0.0.0.0"
    port => 5044
  }

  jdbc {
    jdbc_connection_string => "jdbc:mysql://192.168.1.10:3306/mydatabase"
    jdbc_user => "dbuser"
    jdbc_password => "dbpassword"
    jdbc_validate_connection => true
    jdbc_driver_library => "/opt/logstash/lib/mysql-connector-java-5.1.38.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"

    statement => "SELECT * FROM mytable WHERE id > :sql_last_value"
    schedule => "* * * * *"
    clean_run =>false

    add_field => {"type" => "mydatabase"}
  }
}

output {
  if [type] == "mydatabase"  {
    elasticsearch {
      hosts => ["127.0.0.1:9200"]
      index => "mydatabase-%{+YYYY.MM.dd}"
    }
  } else {
	elasticsearch {
	  hosts => ["127.0.0.1:9200"]
	  index => "generic"
	}
  }  
}

But Logstash get down with the following error:

{
	:timestamp=>"2017-11-24T11:30:46.340000+0000", 
	:message=>"Pipeline aborted due to error", 
	:exception=>"Sequel::DatabaseConnectionError", 
	:backtrace=>[
		"com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(com/mysql/cj/jdbc/exceptions/SQLError.java:695)", 
		"com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(com/mysql/cj/jdbc/exceptions/SQLError.java:663)", 
		"com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(com/mysql/cj/jdbc/exceptions/SQLError.java:653)", 
		"com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(com/mysql/cj/jdbc/exceptions/SQLError.java:638)", 
		"com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(com/mysql/cj/jdbc/exceptions/SQLError.java:606)", 
		"com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(com/mysql/cj/jdbc/exceptions/SQLError.java:624)", 
		"com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(com/mysql/cj/jdbc/exceptions/SQLError.java:620)", 
		"com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(com/mysql/cj/jdbc/exceptions/SQLExceptionsMapping.java:69)", 
		"com.mysql.cj.jdbc.ConnectionImpl.createNewIO(com/mysql/cj/jdbc/ConnectionImpl.java:1663)", 
		"com.mysql.cj.jdbc.ConnectionImpl.<init>(com/mysql/cj/jdbc/ConnectionImpl.java:662)", 
		"com.mysql.cj.jdbc.ConnectionImpl.getInstance(com/mysql/cj/jdbc/ConnectionImpl.java:352)", 
		"com.mysql.cj.jdbc.NonRegisteringDriver.connect(com/mysql/cj/jdbc/NonRegisteringDriver.java:221)", 
		"java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", 
		"RUBY.connect(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/adapters/jdbc.rb:222)", 
		"RUBY.make_new(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool.rb:116)", 
		"RUBY.make_new(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:228)", 
		"RUBY.available(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:201)", 
		"RUBY._acquire(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:137)", 
		"RUBY.acquire(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:151)", 
		"RUBY.sync(/opt/logstash/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(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:282)", 
		"RUBY.acquire(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:150)", 
		"RUBY.acquire(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/extensions/connection_validator.rb:98)", 
		"RUBY.hold(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/connection_pool/threaded.rb:106)", 
		"RUBY.synchronize(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/database/connecting.rb:256)", 
		"RUBY.test_connection(/opt/logstash/vendor/bundle/jruby/1.9/gems/sequel-4.37.0/lib/sequel/database/connecting.rb:266)", 		"RUBY.prepare_jdbc_connection(/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc-3.1.0/lib/logstash/plugin_mixins/jdbc.rb:173)", 		"RUBY.register(/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-input-jdbc-3.1.0/lib/logstash/inputs/jdbc.rb:187)", 
		"RUBY.start_inputs(/opt/logstash/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(/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/pipeline.rb:329)", 
		"RUBY.start_workers(/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/pipeline.rb:180)", 
		"RUBY.run(/opt/logstash/vendor/bundle/jruby/1.9/gems/logstash-core-2.4.0-java/lib/logstash/pipeline.rb:136)", 
		"RUBY.start_pipeline(/opt/logstash/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:745)"], 
	:level=>:error}

I have also tried other versions of the connector but I get the same error.

2

If you have Mysql Workbench, this may help. https://stackoverflow.com/a/26788332/5349531

Otherwise you could try the Alternate Format for JDBC URLs
https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

3

@guyboertje I have solved with your suggest.

The right url should had being:

jdbc:mysql://192.168.1.10:3306/mydatabase?user=dbuser

Thanks for your help!

4

Great. IIRC, JDBC Drivers have differing ways to incorporate the user and password.
We rely on a 3rd party library to augment the connection string provided but it does not always get it right, in its defence though, many bugs have been fixed in the 3rd party library since we released Logstash 2.4.

5

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