# Filter jdbc\_static mongodb error **URL:** \ **Category:** Logstash\ **Created:** [June 29, 2020, 9:53pm UTC](https://discuss.elastic.co/t/filter-jdbc-static-mongodb-error/239194 "2020-06-29T21:53:55Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![pedropilla](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pedropilla/32/62057_2.png) [@pedropilla](https://discuss.elastic.co/u/pedropilla)\ **Post date:** [June 29, 2020, 9:53pm UTC](https://discuss.elastic.co/t/filter-jdbc-static-mongodb-error/239194/1 "2020-06-29T21:53:55Z")
Hello, I'm trying to enrich data with mongodb objects, here's the related part in my pipeline: ```auto jdbc_static { loaders => [ { id => "circuitosOLT" table => "ftthLeases" query => 'db.ftthLeases.find({},{"_id": false});' local_table => "dataOLT" } ] local_db_objects => [ { name => "dataOLT" index_columns => ["SERIAL"] columns => [ ["SERIAL", "varchar(64)"], ["CLIENTE", "varchar(64)"], ["BANDA", "varchar(64)"], ["CIRCUITO", "varchar(64)"], ["POSICAO", "varchar(64)"] ] } ] local_lookups => [ { query => "select * from dataOLT WHERE POSICAO = :porta" parameters => { porta => "[posicao]" } target => "dataOLT" } ] loader_schedule => "30 */4 * * *" jdbc_driver_class => "com.dbschema.MongoJdbcDriver" jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/mongojdbc2.1.jar" jdbc_connection_string => "jdbc:mongodb://api.test.com:27017/test" jdbc_user => "root" jdbc_password => "qwerty12345" } ``` But I'm getting this error: ```auto elk_logstash.0.wmnjztsgrhi6@elk1.localdomain | [2020-06-29T18:37:12,640][ERROR][logstash.filters.jdbc.readonlydatabase][main] Exception occurred when executing loader Jdbc query count {:exception=>"Java::JavaSql::SQLException: :1:7 Expected ; but found count\nSELECT count(*) AS \"COUNT\" FROM (db.ftthLeases.find({},{\"_id\": false});) AS \"T1\" LIMIT 1\n ^ in at line number 1 at column number 7", :backtrace=>["com.dbschema.MongoPreparedStatement.executeQuery(com/dbschema/MongoPreparedStatement.java:197)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorImpl.java:62)", "jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:566)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:426)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:293)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:256)"]} elk_logstash.0.wmnjztsgrhi6@elk1.localdomain | [2020-06-29T18:37:12,651][ERROR][logstash.filters.jdbc.readwritedatabase][main] Exception when filling lookup db from loader circuitosOLT, query results, original exception: LogStash::Filters::Jdbc::LookupJdbcException, original message: Exception occurred when executing loader Jdbc query count {:backtrace=>["com.dbschema.MongoPreparedStatement.executeQuery(com/dbschema/MongoPreparedStatement.java:197)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)", "jdk.internal.reflect.NativeMethodAccessorImpl.invoke(jdk/internal/reflect/NativeMethodAccessorImpl.java:62)", "jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(jdk/internal/reflect/DelegatingMethodAccessorImpl.java:43)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:566)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:426)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:293)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:256)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.database.logging.log_connection_yield(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/database/logging.rb:38)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:256)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.statement(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:692)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.RUBY$method$statement$0$ __VARARGS__ (usr/share/logstash/vendor/bundle/jruby/$2_dot_5_dot_0/gems/sequel_minus_5_dot_28_dot_0/lib/sequel/adapters//usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:251)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.connection_pool.threaded.hold(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/connection_pool/threaded.rb:92)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.database.connecting.synchronize(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/database/connecting.rb:270)", "usr.share.logstash.vendor.bundle.jruby.$2_dot_5_dot_0.gems.sequel_minus_5_dot_28_dot_0.lib.sequel.adapters.jdbc.execute(/usr/share/logstash/vendor/bundle/jruby/2.5.0/gems/sequel-5.28.0/lib/sequel/adapters/jdbc.rb:250)"]} ``` I have searched and found [this](https://discuss.elastic.co/t/jdbc-static-getting-a-sql-syntax-error-i-didnt-write/187844) related but I'm querying mongo so dont know exactly how to proceed. Thanks in advance. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [June 29, 2020, 10:32pm UTC](https://discuss.elastic.co/t/filter-jdbc-static-mongodb-error/239194/2 "2020-06-29T22:32:54Z")
> [@pedropilla](#): > > Java::JavaSql::SQLException: :1:7 Expected ; but found count\nSELECT count(\*) AS "COUNT" FROM (db.ftthLeases.find({},{"\_id": false})😉 AS "T1" LIMIT 1\n The problem is that the jdbc\_static filter [insists](https://github.com/logstash-plugins/logstash-filter-jdbc_static/blob/2e37229d01e3da0cf7039184cec10bc4ccc54905/lib/logstash/filters/jdbc/loader.rb#L23) on knowing the number of records (the count is done by [calling](https://github.com/logstash-plugins/logstash-filter-jdbc_static/blob/2e37229d01e3da0cf7039184cec10bc4ccc54905/lib/logstash/filters/jdbc/read_only_database.rb#L7) the sequel library that the filter is built upon). The sequel [library](https://github.com/jeremyevans/sequel) implements the count by wrapping the SQL statement in ``` SELECT count(*) AS \"COUNT\" FROM (...) AS \"T1\" LIMIT 1 ``` and that only works in databases that support that syntax. If your DB uses a different syntax then you cannot use a jdbc\_static filter. The jdbc input also counts records, but if that gets an exception like this it simply logs a message saying it will no longer try to count records. ---
**Author:** ![pedropilla](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pedropilla/32/62057_2.png) [@pedropilla](https://discuss.elastic.co/u/pedropilla)\ **Post date:** [June 30, 2020, 1:13am UTC](https://discuss.elastic.co/t/filter-jdbc-static-mongodb-error/239194/3 "2020-06-30T01:13:04Z")
That's bad, so I think I need to figure out another way. Ok, thanks! ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [June 30, 2020, 5:04pm UTC](https://discuss.elastic.co/t/filter-jdbc-static-mongodb-error/239194/4 "2020-06-30T17:04:06Z")
I see that there has been an [issue](https://github.com/logstash-plugins/logstash-filter-jdbc_static/issues/29) open for a couple of years for this. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 28, 2020, 5:04pm UTC](https://discuss.elastic.co/t/filter-jdbc-static-mongodb-error/239194/5 "2020-07-28T17:04:13Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.