# Jdbc-streaming plugin seems not working

**URL:** <https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530>\
**Category:** Logstash\
**Created:** [May 12, 2017, 9:27am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530 "2017-05-12T09:27:47Z")\
**Posts on this page:** 20\
**Page:** 2

<div class="post-metadata">

**Author:** ![Vittorio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vittorio/32/18171_2.png) [@Vittorio](https://discuss.elastic.co/u/Vittorio)\
**Post date:** [May 15, 2017, 1:50pm UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/21 "2017-05-15T13:50:06Z")

</div>

thanks @guyboertje , it worked 🙂

---

<div class="post-metadata">

**Author:** ![Dror\_Bletter](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dror_bletter/32/18339_2.png) [@Dror\_Bletter](https://discuss.elastic.co/u/Dror_Bletter)\
**Post date:** [May 18, 2017, 3:53pm UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/22 "2017-05-18T15:53:55Z")

</div>

Hi,

I've configured logstash exactly the same as here:

```
jdbc_streaming {
  jdbc_driver_library => "/etc/logstash/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/test_db"
  jdbc_user => "root"
  jdbc_password => "test"
  parameters => { "ostype" => "major" }
  statement => "select value from test_table WHERE keyid = :ostype"
  target => "loo"
  use_cache => "false"
}

```

major is a logstash variable that comes from logs.

Each time MySQL query runs, it gets "NULL" instead of "major":

170518 14:50:29 25 Query select value from test\_table WHERE keyid = NULL

Any suggestions?

Thanks!

---

<div class="post-metadata">

**Author:** ![Vittorio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vittorio/32/18171_2.png) [@Vittorio](https://discuss.elastic.co/u/Vittorio)\
**Post date:** [May 18, 2017, 3:57pm UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/23 "2017-05-18T15:57:04Z")

</div>

Can you please post the output error you got? Did you see, in debug mode, what jdbc receive?

I'm assuming that inside test\_db there's a table called test\_table, am i right?

---

<div class="post-metadata">

**Author:** ![Dror\_Bletter](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dror_bletter/32/18339_2.png) [@Dror\_Bletter](https://discuss.elastic.co/u/Dror_Bletter)\
**Post date:** [May 18, 2017, 4:34pm UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/24 "2017-05-18T16:34:26Z")

</div>

That's at LS startup:

16:19:39.171 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/jdbc\_driver\_library = "/etc/logstash/mysql-connector-java-5.1.42/mysql-connector-java-5.1.42-bin.jar"  
16:19:39.171 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/jdbc\_driver\_class = "com.mysql.jdbc.Driver"  
16:19:39.171 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/jdbc\_connection\_string = "jdbc:mysql://localhost:3306/test\_db"  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/jdbc\_user = "root"  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/jdbc\_password =   
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/parameters = {"ostype"=\>"major"}  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/statement = "select value from test\_table WHERE keyid = :ostype"  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/target = "loo"  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/use\_cache = false  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/id = "7c0e1c9d2c02a7fec055a98665a5d3551194ccc4-3"  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/enable\_metric = true  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/add\_tag = []  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/@remove\_tag = []  
16:19:39.172 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/add\_field = {}  
16:19:39.173 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/remove\_field = []  
16:19:39.173 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/periodic\_flush = false  
16:19:39.173 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/jdbc\_validate\_connection = false  
16:19:39.173 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/jdbc\_validation\_timeout = 3600  
16:19:39.173 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/default\_hash = {}  
16:19:39.173 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/tag\_on\_failure = ["\_jdbcstreamingfailure"]  
16:19:39.173 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/tag\_on\_default\_use = ["\_jdbcstreamingdefaultsused"]  
16:19:39.174 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/cache\_expiration = 5.0  
16:19:39.174 [LogStash::Runner] DEBUG logstash.filters.jdbcstreaming - config LogStash::Filters::JdbcStreaming/cache\_size = 500

That's when event comes in:  
16:31:13.473 [[main]\>worker7] DEBUG logstash.pipeline - filter received {"event"=\>{"@timestamp"=\>2017-05-17T14:55:46.588Z, "offset"=\>0, "beat"=\>{"hostname"=\>"ip-10-0-5-208", "name"=\>"ip-10-0-5-208", "version"=\>"5.0.2"}, "input\_type"=\>"stdin", "@version"=\>"1", "source"=\>"", "message"=\>"184.74.151.130 [2017-05-17T14:55:46+00:00] "GET /p.gif?cb=1302978897 HTTP/1.1" 200 43 0.000 "[http://www.msn.com/](http://www.msn.com/)" "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36"", "type"=\>"log"}}  
16:31:13.474 [[main]\>worker7] DEBUG logstash.filters.jdbcstreaming - Executing JDBC query {:statement=\>"select value from test\_table WHERE keyid = :ostype", :parameters=\>{:ostype=\>nil}}

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [May 18, 2017, 6:42pm UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/25 "2017-05-18T18:42:53Z")

</div>

If this is your event...

```auto
{"@timestamp"=>2017-05-17T14:55:46.588Z,
  "offset"=>0,
  "beat"=>{"hostname"=>"ip-10-0-5-208", "name"=>"ip-10-0-5-208", "version"=>"5.0.2"},
  "input_type"=>"stdin",
  "@version"=>"1",
  "source"=>"",
  "message"=>"184.74.151.130 [2017-05-17T14:55:46+00:00] \"GET /p.gif?cb=1302978897 HTTP/1.1\" 200 43 0.000 \"http://www.msn.com/\" \"Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36\"",
  "type"=>"log"
}

```

what filters are you using to fill in the `major` field for the event - before you try to use it in the jdbc lookup?

---

<div class="post-metadata">

**Author:** ![Dror\_Bletter](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dror_bletter/32/18339_2.png) [@Dror\_Bletter](https://discuss.elastic.co/u/Dror_Bletter)\
**Post date:** [May 19, 2017, 12:05am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/26 "2017-05-19T00:05:09Z")

</div>

I'm using useragent filter. Also tried to use parsed fields from kv. Neither have worked...

---

<div class="post-metadata">

**Author:** ![Vittorio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vittorio/32/18171_2.png) [@Vittorio](https://discuss.elastic.co/u/Vittorio)\
**Post date:** [May 19, 2017, 7:31am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/27 "2017-05-19T07:31:02Z")

</div>

`16:31:13.474 [[main]>worker7] DEBUG logstash.filters.jdbcstreaming - Executing JDBC query {:statement=>"select value from test_table WHERE keyid = :ostype", :parameters=>{:ostype=>nil}}`

it's not finding the major field, so I guess something before is not working(filters useragent you said?). `Major` should be in your event but `:parameters=>{:ostype=>nil}}` it is not there.

---

<div class="post-metadata">

**Author:** ![nano](https://avatars.discourse-cdn.com/v4/letter/n/dec6dc/32.png) [@nano](https://discuss.elastic.co/u/nano)\
**Post date:** [May 19, 2017, 8:40am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/28 "2017-05-19T08:40:30Z")

</div>

Hello!  
i have some questions please, i have two configurations files  
first one: i recuperate first database with jdbc and i make the result in csv file

second configuration: i recuperate an other database and i use translate filter , i make search in csv file that i saved and if two column are the same i take the result

My question is if it is possible to make that with jdbc streaming and regroub two files configurations and make only one file with two database

sorry for my english , thank you

---

<div class="post-metadata">

**Author:** ![Vittorio](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vittorio/32/18171_2.png) [@Vittorio](https://discuss.elastic.co/u/Vittorio)\
**Post date:** [May 19, 2017, 8:42am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/29 "2017-05-19T08:42:23Z")

</div>

Would you please rewrite in english? Thank you

---

<div class="post-metadata">

**Author:** ![nano](https://avatars.discourse-cdn.com/v4/letter/n/dec6dc/32.png) [@nano](https://discuss.elastic.co/u/nano)\
**Post date:** [May 19, 2017, 8:53am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/31 "2017-05-19T08:53:55Z")

</div>

translate {  
field =\> "field1"  
destination =\> "code"  
dictionary\_path =\> "/tmp/Result.csv"  
refresh\_interval =\> "300"  
}  
this is my translate filter i search for field1 in dictionary\_path and if he find it , it return the code and insert it in the database  
i wnat to make the same thing using jdbc streaming

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [May 19, 2017, 8:55am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/32 "2017-05-19T08:55:25Z")

</div>

Yes. That is exactly why we wrote the jdbc\_streaming filter plugin. 😀

---

<div class="post-metadata">

**Author:** ![nano](https://avatars.discourse-cdn.com/v4/letter/n/dec6dc/32.png) [@nano](https://discuss.elastic.co/u/nano)\
**Post date:** [May 19, 2017, 9:07am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/34 "2017-05-19T09:07:56Z")

</div>

this is what i want:

input{  
jdbc{  
statement =\>" select field1, field2, field3 from database1"  
......  
}  
filter {

jdbc\_streaming {  
statement =\> " select field 4, field 5 from database2"

( what i want when field2=field4 Add field5 into the first database and make output in ES)  
}

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [May 19, 2017, 9:09am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/35 "2017-05-19T09:09:09Z")

</div>

No need for csv and translate.

```auto
LS DB1 DB2
| | |
Inputs <-------/ |
| |
Transformations 1 |
| |
Enhancements <----------------/
|
Transformations 2
|
Outputs

```

`Transformations 1` would be changes you need to the event fields/values to do before the JDBC Streaming enhancement - like mutates.  
`Transformations 2` would be changes you need to the event fields/values to do after the enhancement merged data.

---

<div class="post-metadata">

**Author:** ![nano](https://avatars.discourse-cdn.com/v4/letter/n/dec6dc/32.png) [@nano](https://discuss.elastic.co/u/nano)\
**Post date:** [May 19, 2017, 9:19am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/36 "2017-05-19T09:19:16Z")

</div>

thank you i ask how can i make that:

statement =\> " select field 4, field 5 from database2"

( what i want when field2=field4 Add field5 into the first database and make output in ES)  
}

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [May 19, 2017, 9:27am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/37 "2017-05-19T09:27:51Z")

</div>

**EDITED**

```auto
input{
  jdbc{
    statement => "select field1, field2, field3 from database1"
    ......
  }
}
filter {
  jdbc_streaming {
    statement => "select field5 from database2 where field 4 = :field2val"
    parameters => {"field2val" => "field2"}
}

```

Replace fieldX with your real field names and databaseX with your real database names, should work.

Remember to look at the cache config options - this will prevent too many network calls to database2.  
The cache uses the substituted parameter hash as the key e.g.

{"field2" =\> "foo"} will cache the recordset for database2 where field4 == "foo" or an empty recordset if there is no field4 == "foo"  
if you set a default this will be used for field5 and the event is tagged with "\_defaultused" or similar.  
{"field2" =\> "bar"} will cache the recordset for database2 where field4 == "bar"

---

<div class="post-metadata">

**Author:** ![nano](https://avatars.discourse-cdn.com/v4/letter/n/dec6dc/32.png) [@nano](https://discuss.elastic.co/u/nano)\
**Post date:** [May 19, 2017, 9:35am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/38 "2017-05-19T09:35:13Z")

</div>

thank you for your clear answer, for me field2val is not static value that i want to search i want if the two column field 2 and field 4 are the same i return field5

i'm so sorry if i didn't understand well what you said

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [May 19, 2017, 9:38am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/39 "2017-05-19T09:38:51Z")

</div>

NOTE:  
If database2 is changing often i.e. field4 == "foo", field5 == "apples" now and field4 == "foo", field5 == "oranges" 5 minutes later then you should use a small cache expiry time (default is 5 seconds).

If database2 is (mostly) static reference data then long cache expiry is better - you should change it from the default.

Also find out how many distinct values of field4 you have in database2 this will help determine the best cache size (default is 500).  
The cache is in memory so you do not want the JVM to run out of heap by caching too many records.

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [May 19, 2017, 9:41am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/40 "2017-05-19T09:41:52Z")

</div>

Think of **field2val** as a label that "glues" the field2 name to the place in the query string that the Event **value** of field2 must be put.

---

<div class="post-metadata">

**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\
**Post date:** [May 19, 2017, 9:45am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/41 "2017-05-19T09:45:23Z")

</div>

CORRECTION:  
I switched field2 and field4val around by mistake. It should be like this. **I have edited my original post**.

```auto
input{
  jdbc{
    statement => "select field1, field2, field3 from database1"
    ......
  }
}
filter {
  jdbc_streaming {
    statement => "select field5 from database2 where field 4 = :field2val"
    parameters => {"field2val" => "field2"}
}

```

---

<div class="post-metadata">

**Author:** ![nano](https://avatars.discourse-cdn.com/v4/letter/n/dec6dc/32.png) [@nano](https://discuss.elastic.co/u/nano)\
**Post date:** [May 19, 2017, 9:49am UTC](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530/42 "2017-05-19T09:49:49Z")

</div>

thank you very much my database2 changes dialy i think i have to minimize cache , i still confused about field2val , i ask if i can make:  
filter {  
jdbc\_streaming {  
statement =\> "select field5 from database2 where field 4 = :field2"

whithout pasisng by field2val

[Previous page](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530.md?page=1)

[Next page](https://discuss.elastic.co/t/jdbc-streaming-plugin-seems-not-working/85530.md?page=3)