Error connecting to mysql using jdbc_static filter


(Srikanta Patanjali) #1

I'm trying to connect to a Mysql DB using the jdbc_static filter plugin.

I've observed that the error thrown by the plugin for both non existent (Eg: 127.1.1.1) and correct hostname (Eg: 127.0.0.1) as part of the jdbc connection string is as below:

error=>"Java::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: Unknown database 'testdb'"

My question is:

  • Is there any way to debug and get more details to pinpoint the problem ?
  • or is there any error in the below provided filter configuration ?

Config for the filter :

    filter {
      jdbc_static {
		id => "data_enhancement"
		loaders => [
			{
				id => "test_loader"
				query => "SELECT * FROM data_dump.testdb"
				local_table => "testdbLocal"
			}
		]
		local_db_objects => [
			{
				name => "testdbLocal"
				columns => [
					["dept_id", "int"],
					["acc_country_name", "varchar(255)"]
				]
			}
		]	
		local_lookups => [ 
			{
				id => "local-servers"
				query => "SELECT * FROM testdbLocal WHERE dept_id = :groupName"
				parameters => { groupName => "[group_name]"}
				target => "enhanced_info"
			}
		]
		jdbc_user => "admin"
		jdbc_password => "XXX"
		jdbc_driver_class => "com.mysql.jdbc.Driver"
		jdbc_driver_library => "/usr/share/logstash/vendor/mysql-connector-java-5.1.46-bin.jar"
		jdbc_connection_string => "jdbc:mysql://127.0.0.1:3306/testdb"
	}
}

Extended error log:

  :error=>"Java::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: Unknown database 'testane2'", :thread=>"#<Thread:0x665b9633@/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:246 run>"}
        [2018-05-16T14:53:30,792][ERROR][logstash.pipeline        ] Pipeline aborted due to error {:pipeline_id=>"main", :exception=>#<LogStash::Filters::Jdbc::ConnectionJdbcException: Java::ComMysqlJdbcExceptionsJdbc4::MySQLSyntaxErrorException: Unknown database 'testane2'>, :backtrace=>["java.lang.reflect.Constructor.newInstance(java/lang/reflect/Constructor.java:423)", "com.mysql.jdbc.Util.handleNewInstance(com/mysql/jdbc/Util.java:425)", "com.mysql.jdbc.Util.getInstance(com/mysql/jdbc/Util.java:408)", "com.mysql.jdbc.SQLError.createSQLException(com/mysql/jdbc/SQLError.java:944)", "com.mysql.jdbc.MysqlIO.checkErrorPacket(com/mysql/jdbc/MysqlIO.java:3976)", "com.mysql.jdbc.MysqlIO.checkErrorPacket(com/mysql/jdbc/MysqlIO.java:3912)", "com.mysql.jdbc.MysqlIO.checkErrorPacket(com/mysql/jdbc/MysqlIO.java:871)", "com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(com/mysql/jdbc/MysqlIO.java:1714)", "com.mysql.jdbc.MysqlIO.doHandshake(com/mysql/jdbc/MysqlIO.java:1224)", "com.mysql.jdbc.ConnectionImpl.coreConnect(com/mysql/jdbc/ConnectionImpl.java:2190)", "com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(com/mysql/jdbc/ConnectionImpl.java:2221)", "com.mysql.jdbc.ConnectionImpl.createNewIO(com/mysql/jdbc/ConnectionImpl.java:2016)", "com.mysql.jdbc.ConnectionImpl.<init>(com/mysql/jdbc/ConnectionImpl.java:776)", "com.mysql.jdbc.JDBC4Connection.<init>(com/mysql/jdbc/JDBC4Connection.java:47)", "java.lang.reflect.Constructor.newInstance(java/lang/reflect/Constructor.java:423)", "com.mysql.jdbc.Util.handleNewInstance(com/mysql/jdbc/Util.java:425)", "com.mysql.jdbc.ConnectionImpl.getInstance(com/mysql/jdbc/ConnectionImpl.java:386)", "com.mysql.jdbc.NonRegisteringDriver.connect(com/mysql/jdbc/NonRegisteringDriver.java:330)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:468)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:326)", "RUBY.connect(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/adapters/jdbc.rb:203)", "RUBY.make_new(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/connection_pool.rb:126)", "RUBY.assign_connection(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/connection_pool/threaded.rb:192)", "RUBY.acquire(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/connection_pool/threaded.rb:133)", "RUBY.hold(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/connection_pool/threaded.rb:90)", "RUBY.synchronize(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/database/connecting.rb:269)", "RUBY.test_connection(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/database/connecting.rb:279)", "RUBY.connect(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/database/connecting.rb:58)", "RUBY.connect(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/sequel-5.8.0/lib/sequel/core.rb:116)", "RUBY.verify_connection(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-filter-jdbc_static-1.0.3/lib/logstash/filters/jdbc/basic_database.rb:108)", "RUBY.post_create(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-filter-jdbc_static-1.0.3/lib/logstash/filters/jdbc/read_only_database.rb:48)", "RUBY.create(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-filter-jdbc_static-1.0.3/lib/logstash/filters/jdbc/basic_database.rb:33)", "RUBY.build_remote_db(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-filter-jdbc_static-1.0.3/lib/logstash/filters/jdbc/loader.rb:18)", "RUBY.block in prepare_runner(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-filter-jdbc_static-1.0.3/lib/logstash/filters/jdbc_static.rb:178)", "org.jruby.RubyArray.collect(org/jruby/RubyArray.java:2472)", "org.jruby.RubyArray.map(org/jruby/RubyArray.java:2486)", "org.jruby.RubyArray$INVOKER$i$0$0$map19.call(org/jruby/RubyArray$INVOKER$i$0$0$map19.gen)", "RUBY.prepare_runner(/usr/share/logstash/vendor/bundle/jruby/2.3.0/gems/logstash-filter-jdbc_static-1.0.3/lib/logstash/filters/jdbc_static.rb:175)", ............... "org.jruby.RubyProc.call(org/jruby/RubyProc.java:289)", "org.jruby.RubyProc.call(org/jruby/RubyProc.java:246)", "java.lang.Thread.run(java/lang/Thread.java:748)"], :thread=>"#<Thread:0x665b9633@/usr/share/logstash/logstash-core/lib/logstash/pipeline.rb:246 run>"}

#2

I don't know about your TCP stack, but on mine that is a valid and useable loopback address. The entire 128/8 network is useable.


(Srikanta Patanjali) #3

@Badger Yes, you are correct.

The issue has been resolved now.

It was observed that the jdbc string contained the table name in the end instead of a database name. Also appropriate access to the user was given.


(system) #4

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