# How should I use sql\_last\_value in logstash? **URL:** \ **Category:** Logstash\ **Created:** [November 1, 2016, 5:11pm UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595 "2016-11-01T17:11:07Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Kulasangar\_Gowrisang](https://avatars.discourse-cdn.com/v4/letter/k/ce73a5/32.png) [@Kulasangar\_Gowrisang](https://discuss.elastic.co/u/Kulasangar_Gowrisang)\ **Post date:** [November 1, 2016, 5:11pm UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/1 "2016-11-01T17:11:07Z")
I'm quite unclear of what `sql_last_value` does when I give my statement as such: ``` statement => "SELECT * from mytable where id > :sql_last_value" ``` I can slightly understand the reason behind using it, where it doesn't browse through the whole db table in order to update fields instead it only updates the records which were added newly. Correct me if I'm wrong. So what I'm trying to do is, creating the index using `logstash` as such: ``` input { jdbc { jdbc_connection_string => "jdbc:mysql://hostmachine:3306/db" jdbc_user => "root" jdbc_password => "root" jdbc_validate_connection => true jdbc_driver_library => "/path/mysql_jar/mysql-connector-java-5.1.39-bin.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" schedule => "* * * * *" statement => "SELECT * from mytable where id > :sql_last_value" use_column_value => true tracking_column => id jdbc_paging_enabled => "true" jdbc_page_size => "50000" } } output { elasticsearch { #protocol => http index => "myindex" document_type => "message_logs" document_id => "%{id}" action => index hosts => ["http://myhostmachine:9402"] } } ``` Once I do this, the docs aren't getting uploaded at all to the index. Where am I going wrong? Any help could be appreciated. ---
**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:** [November 1, 2016, 9:21pm UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/2 "2016-11-01T21:21:10Z")
Simplify things by removing the elasticsearch configuration for now. Use a `stdout { codec => rubydebug }` output. What is the plugin doing? Crank up Logstash's loglevel to find out more. ---
**Author:** ![Kulasangar\_Gowrisang](https://avatars.discourse-cdn.com/v4/letter/k/ce73a5/32.png) [@Kulasangar\_Gowrisang](https://discuss.elastic.co/u/Kulasangar_Gowrisang)\ **Post date:** [November 2, 2016, 11:25am UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/3 "2016-11-02T11:25:23Z")
@magnusbaeck Yes I did what you asked me to do. Inserted the `codec` and checked with the `debug` mode as well. Part of the output: > [2016-11-02T16:52:00,276][INFO][logstash.inputs.jdbc] (0.002000s) SELECT count(_) AS `count` FROM (SELECT \* from TEST where id \> '2016-11-02 11:21:00') AS `t1` LIMIT 1 > [2016-11-02T16:52:00,279][DEBUG][logstash.inputs.jdbc] Executing JDBC query {:statement=\>"SELECT \* from TEST where id \> :sql\_last\_value", :parameters=\>{:sql\_last\_value=\>2016-11-02 11:21:00 UTC}, :count=\>0} > [2016-11-02T16:52:00,287][INFO][logstash.inputs.jdbc] (0.003000s) SELECT count(_) AS `count` FROM (SELECT \* from TEST where id \> '2016-11-02 11:21:00') AS `t1` LIMIT 1 > [2016-11-02T16:52:00,582][DEBUG][logstash.pipeline] Pushing flush onto pipeline What should I be checking on with the output ? I'm cracking my head with this. ๐Ÿ˜• Can't I use an id of a table in order to update the index with the new records which are added? I tried it with the `date` and `datetime` field and it works perfectly fine. But then I have to work around with the `id`. ---
**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:** [November 2, 2016, 2:06pm UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/4 "2016-11-02T14:06:14Z")
I suspect you've run the plugin at least once before setting `tracking_column`, so Logstash has saved a timestamp in ~/.logstash\_jdbc\_last\_run but never updates that file since it never gets any results for the resulting query. Try deleting the file or changing it to the id where you want to start. ---
**Author:** ![Kulasangar\_Gowrisang](https://avatars.discourse-cdn.com/v4/letter/k/ce73a5/32.png) [@Kulasangar\_Gowrisang](https://discuss.elastic.co/u/Kulasangar_Gowrisang)\ **Post date:** [November 2, 2016, 3:17pm UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/5 "2016-11-02T15:17:55Z")
@magnusbaeck I deleted the `.logstash_jdbc_last_run` file and changed it to a value as 0 so that it could pickup for the id field changes from the database records. But still no use. When I ran the logstash conf, the `.logstash_jdbc_last_run` is somehow having a `timestamp` value. I can't even imagine where is it picking up a timestamp from even after deleting it or even changing it to zero. Is there any jdbc property, which I'm missing above? 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:** [November 2, 2016, 3:24pm UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/6 "2016-11-02T15:24:41Z")
Okay. Then I'm not sure what's going on. I've never used the jdbc input myself. ---
**Author:** ![Kulasangar\_Gowrisang](https://avatars.discourse-cdn.com/v4/letter/k/ce73a5/32.png) [@Kulasangar\_Gowrisang](https://discuss.elastic.co/u/Kulasangar_Gowrisang)\ **Post date:** [November 2, 2016, 3:26pm UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/7 "2016-11-02T15:26:11Z")
@magnusbaeck Oh alright. Is there anything I could just follow up on this thing? Or any other recommended source to refer. ๐Ÿ™‚ Thanks. ---
**Author:** ![Kulasangar\_Gowrisang](https://avatars.discourse-cdn.com/v4/letter/k/ce73a5/32.png) [@Kulasangar\_Gowrisang](https://discuss.elastic.co/u/Kulasangar_Gowrisang)\ **Post date:** [November 3, 2016, 4:56am UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/8 "2016-11-03T04:56:37Z")
Somehow made it work. For those whom it might help this was my final `jdbc` input in `logstash`: ``` jdbc { jdbc_connection_string => "jdbc:mysql://myhostmachine:3306/mydb" jdbc_user => "root" jdbc_password => "root" jdbc_validate_connection => true jdbc_driver_library => "/mypath/mysql-connector-java-5.1.39-bin.jar" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_paging_enabled => "true" jdbc_page_size => "50000" schedule => "* * * * *" statement => "SELECT * from mytable where id > :sql_last_value" use_column_value => true tracking_column => "id" tracking_column_type => "numeric" clean_run => true last_run_metadata_path => "/path/.logstash_jdbc_last_run" } ``` Make sure you delete the `.logstash_jdbc_last_run` file before you run your `logstash` conf and have a different `last_run_metadata_path` if you're running multiple `logstash` instances. ---
**Author:** ![hearvishwas](https://avatars.discourse-cdn.com/v4/letter/h/cc9497/32.png) [@hearvishwas](https://discuss.elastic.co/u/hearvishwas)\ **Post date:** [December 5, 2016, 9:33am UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/9 "2016-12-05T09:33:45Z")
> [@Kulasangar\_Gowrisang](#): > > use\_column\_value =\> true > tracking\_column =\> "id" > tracking\_column\_type =\> "numeric" > clean\_run =\> true I am getting error "Unknown setting 'tracking\_column\_type' for jdbc {:level=\>:error}" ---
**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:** [December 5, 2016, 9:37am UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/10 "2016-12-05T09:37:43Z")
@hearvishwas, please consult the documentation for your version of Logstash (the option might not be available until in later releases) and start a new thread if you have follow-up questions. ---
**Author:** ![hearvishwas](https://avatars.discourse-cdn.com/v4/letter/h/cc9497/32.png) [@hearvishwas](https://discuss.elastic.co/u/hearvishwas)\ **Post date:** [December 5, 2016, 9:45am UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/11 "2016-12-05T09:45:31Z")
Hi @magnusbaeck Magnus Bรคck I am facing same issue which @Kulasangar_Gowrisang Kulasangar Gowrisangar is. I am able to run logstash with jdbc when I remove tracking\_column\_type =\> "numeric", but this time logstash is reading old records, so ES is getting filled up with duplicate records. By the way its not tracking\_column\_type, its just "type" ---
**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:30am UTC](https://discuss.elastic.co/t/how-should-i-use-sql-last-value-in-logstash/64595/12 "2017-07-06T04:30:00Z")