# Logstash jdbc query issue **URL:** \ **Category:** Logstash\ **Created:** [April 28, 2016, 6:38pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725 "2016-04-28T18:38:01Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [April 28, 2016, 6:38pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/1 "2016-04-28T18:38:01Z")
I have the latest ELK stack installed and all three are communicating correctly. I'm trying to get logstash to query a 'mysql' database. my logstash.conf file: > input { > jdbc { > jdbc\_driver\_library =\> "/opt/logstash/jdbc/mysql-connector-java-5.1.36-bin.jar" > jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" > jdbc\_connection\_string =\> "jdbc:mysql://192.168.1.5:3306/pacsdb" > jdbc\_user =\> "pacs" > jdbc\_password =\> "\*\*\*\*\*\*" > statement\_filepath =\> "/opt/logstash/query/arch4.sql" > type =\> "cd\_exams\_ripped" > } > } > output { > elasticsearch { > index =\> "cdrip-index" > } > } in the path '/opt/logstash/query' I have my 'arch4.sql' file > select distinct se.src\_aet as "Ripped By", s.created\_time as "Date/Time Sent", p.pat\_name as "Patient Name", p.pat\_id as "Patient ID", s.accession\_no as "ACC #", p.pat\_birthdate as "DOB", s.mods\_in\_study as "MOD", s.study\_datetime as "Study Date", s.study\_desc as "Study Desc", s.study\_custom1 as "Inst Name" > from patient p > INNER JOIN study s > on p.pk = s.patient\_fk > INNER JOIN series se > on s.pk = se.study\_fk > where s.accession\_no like '%OUT%' > and s.created\_time \>= curdate() and my logstash.log file > {:timestamp=\>"2016-04-28T12:32:38.884000-0500", :message=\>"Pipeline main started"} > {:timestamp=\>"2016-04-28T12:32:40.100000-0500", :message=\>"Pipeline main has been shutdown"} > {:timestamp=\>"2016-04-28T12:32:41.908000-0500", :message=\>"stopping pipeline", :id=\>"main"} > {:timestamp=\>"2016-04-28T13:20:06.723000-0500", :message=\>"Pipeline main started"} > {:timestamp=\>"2016-04-28T13:20:07.758000-0500", :message=\>"Pipeline main has been shutdown"} > {:timestamp=\>"2016-04-28T13:20:09.743000-0500", :message=\>"stopping pipeline", :id=\>"main"} can anyone see anything wrong that I'm doing? odviously, the pipeline for logstash keeps shutting down. ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [April 28, 2016, 7:29pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/2 "2016-04-28T19:29:00Z")
Unless you've configured a schedule I think the jdbc input is supposed to shut down Logstash after it has made the query once. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [April 28, 2016, 7:29pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/3 "2016-04-28T19:29:57Z")
Oh really, a schedule. Okay, I'll look into it and try to figure that part out. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [April 28, 2016, 7:46pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/4 "2016-04-28T19:46:30Z")
Okay, so I enabled the schedule to just query each minute, and now everything is getting duplicated in kibana. I wouldn't expect this behavior. ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [April 28, 2016, 8:24pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/5 "2016-04-28T20:24:56Z")
The documentation describes how to only grab rows that have been added/updated since the last run. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [April 28, 2016, 8:31pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/6 "2016-04-28T20:31:52Z")
Thanks. I found where I can use ':sql\_last\_start' except I'm using a 'statement\_filepath' instead of 'statement'. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [April 28, 2016, 9:00pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/7 "2016-04-28T21:00:28Z")
are you referring to the 'jdbc docs'? [https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html) ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [April 29, 2016, 5:29am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/8 "2016-04-29T05:29:51Z")
> are you referring to the 'jdbc docs'? Yes, the documentation of the jdbc input. You're on the right track with `:sql_last_start`. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [April 29, 2016, 4:56pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/9 "2016-04-29T16:56:46Z")
So I have added more parameters to my .conf file. ``` input { jdbc { jdbc_driver_library => "/opt/logstash/jdbc/mysql-connector-java-5.1.36-bin.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://192.168.1.5:3306/pacsdb" jdbc_user => "pacs" jdbc_password => " *****" schedule => "* * * * *" statement_filepath => "/opt/logstash/query/arch4.sql" clean_run => "false" record_last_run => "true" last_run_metadata_path => "/opt/logstash/lastrun/.logstash_jdbc_last_run" type => "cd_exams_ripped" } } output { elasticsearch { index => "cdrip-index" } } ``` I could not use the`:sql_last_start` in my .conf file since I was calling `'statement_filepath`'. All of the examples I have seen so far that use '`:sql_last_start`' have been using the '`statement`' parameter. So, my file `/opt/logstash/lastrun/.logstash_jdb_last_run` is getting updated, but the time that is getting written to it is about 5 hours in the future. My timezone is CST and not sure if logstash is set for the wrong timezone perhaps? All in all, the extra additions to my .conf file are still leading to duplicates in kibana. What am I doing wrong here to make it duplicate everything? I know there has to be an answer! lol thanks for all of your help so far. ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [April 30, 2016, 2:41pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/10 "2016-04-30T14:41:06Z")
> I could not use the :sql\_last\_start in my .conf file since I was calling 'statement\_filepath'. All of the examples I have seen so far that use ':sql\_last\_start' have been using the 'statement' parameter. You can use `statement_filepath` with `:sql_last_start`. > So, my file /opt/logstash/lastrun/.logstash\_jdb\_last\_run is getting updated, but the time that is getting written to it is about 5 hours in the future. My timezone is CST and not sure if logstash is set for the wrong timezone perhaps? I suppose Logstash uses UTC so you'd have to adapt your query accordingly. Note that CST is ambiguous; it can either mean Central Standard Time or China Standard Time. Prefer using UTC offsets. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [May 2, 2016, 7:31pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/11 "2016-05-02T19:31:16Z")
Thanks for your reply. My timezone is Central Standard Time, sorry for the confusion. I modified my `> statement_filepath` again to `statement_filepath => "/opt/logstash/query/arch4.sql > :sql_last_start` after I restarted logstash I found this in the log file. `{:timestamp=>"2016-05-02T14:25:03.840000-0500", :message=>"Invalid setting for jdbc input plugin:\n\n input {\n jdbc {\n # This setting must be a path\n # File does not exist or cannot be opened /opt/logstash/query/arch4.sql > :sql_last_start\n statement_filepath => \"/opt/logstash/query/arch4.sql > :sql_last_start\"\n ...\n }\n }", :level=>:error}` I tried that before and that is why I assumed that I couldn't use the`:sql_last_start` with `statement_filepath` Can you please tell me how I should type out my 'statement' line? Thanks ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [May 3, 2016, 1:56pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/12 "2016-05-03T13:56:21Z")
Something like this: ``` statement => "SELECT xxx FROM yyy WHERE zzz AND timestamp > :sql_last_start" ``` But again, the timestamp put into the `sql_last_start` named parameter is UTC-based so you may have to make a timezone adjustment of your timestamp column. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [May 3, 2016, 1:58pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/13 "2016-05-03T13:58:36Z")
Thanks for the reply. You had stated earlier that I could use `statement_filepath`, but I see your just using `filepath`. I'll put my entire query into the .conf file instead of calling a .sql file and give it a shot. thanks ---
**Author:** ![pku](https://avatars.discourse-cdn.com/v4/letter/p/82dd89/32.png) [@pku](https://discuss.elastic.co/u/pku)\ **Post date:** [July 28, 2016, 11:58pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/14 "2016-07-28T23:58:39Z")
@magnusbaeck could you please give an example as to how to use sql\_last\_start with a statement\_filepath. I have spent way too much time figuring this out but still haven't Thank you. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [July 29, 2016, 12:20am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/15 "2016-07-29T00:20:16Z")
I got it to work, but after every query, your results get dupped. Its not worth it. ---
**Author:** ![pku](https://avatars.discourse-cdn.com/v4/letter/p/82dd89/32.png) [@pku](https://discuss.elastic.co/u/pku)\ **Post date:** [July 29, 2016, 12:23am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/16 "2016-07-29T00:23:27Z")
what would be the solution then? I need to use a .sql file since my query is really big and hence have to use a filepath. I read your solution with last\_run\_metadata in input but I cant find the path to lagstash log files either. I am on OS X EI CAPITAN. ---
**Author:** ![docjay](https://avatars.discourse-cdn.com/v4/letter/d/7ea924/32.png) [@docjay](https://discuss.elastic.co/u/docjay)\ **Post date:** [July 29, 2016, 12:50am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/17 "2016-07-29T00:50:38Z")
Sorry to say that for me, there wasn’t a solution. That is as far as I got. I was able to make it query, but in logstash, all of my results were doubled, tripled, then quad..well you get it. Do you want to see my config that I used to get it to query? input { jdbc { ``` jdbc_driver_library => "/opt/logstash/jdbc/mysql-connector-java-5.1.36-bin.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_connection_string => "jdbc:mysql://10.196.50.51:3306/pacsdb" jdbc_user => "my username" jdbc_password => "my super secret password" schedule => "* * * * *" statement_filepath => "/opt/logstash/query/myquery.sql" clean_run => "false" record_last_run => "true" last_run_metadata_path => "/opt/logstash/lastrun/.logstash_jdbc_last_run" type => "cd_exams_ripped" ``` } } output { elasticsearch { ``` index => "cdrip-index" } ``` } I think the answer was supposed to be the line ‘last\_run\_metadata\_path =\> "/opt/logstash/lastrun/.logstash\_jdbc\_last\_run"’ … but mine seemed to ignore it. I always thought that logstash just didn’t use the correct timezone perhaps. It would be fantastic to use, but I had to move on to other projects. Jamie ---
**Author:** ![mmeaney](https://avatars.discourse-cdn.com/v4/letter/m/3ab097/32.png) [@mmeaney](https://discuss.elastic.co/u/mmeaney)\ **Post date:** [March 31, 2017, 2:20pm UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/18 "2017-03-31T14:20:36Z")
> [@docjay](#): > > Thanks for the reply. You had stated earlier that I could use statement\_filepath, but I see your just using filepath. I'll put my entire query into the .conf file instead of calling a .sql file and give it a shot. thanks You can edit the SQL file referenced in the `statement_filepath`, just add the line `AND timestamp >= :sql_last_value` to the SQL file ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 4:27am UTC](https://discuss.elastic.co/t/logstash-jdbc-query-issue/48725/19 "2017-07-06T04:27:24Z")