# Sql\_last\_value, store value even no document indexed
**URL:** https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695
**Category:** Logstash
**Created:** [January 3, 2020, 11:53am UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695 "2020-01-03T11:53:06Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@pilo](https://discuss.elastic.co/u/pilo)
#### Post date: [January 3, 2020, 11:53am UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695/1 "2020-01-03T11:53:06Z")
Hello everyone,
I'm using logstash jdbc to transform my data in postgresql to Elasticsearch.
Because my sql query is too large, about 1 millions records, i have to use schedule to transform about 5 row each time, and it repeats every second.
I use `tracking_column` to track **ID** column.
The problem is that sometime there are two consecutive rows that the gap is bigger than 100 and logstash stops at that point and `:sql_last_value` too.
My config file is like following
```auto
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_user => "${JDBC_USER}"
jdbc_password => "${JDBC_PASSWORD}"
jdbc_paging_enabled => true
use_column_value => true
tracking_column_type => "numeric"
tracking_column => "decision_id"
last_run_metadata_path => "/usr/share/logstash/config/decision_last_value.yml"
record_last_run => true
statement => "
select d.id as decision_id, daet.content as element_content
from decision d
left join decision_element dae on dae.decision_id = d.id
WHERE decision_id > :sql_last_value AND decision_id < :sql_last_value + 5
"
type => "decision"
schedule => "* * * * * *"
}
}
filter {
mutate {
add_field => {
"[my_join_field][name]" => "decision"
"[my_join_field][parent]" => "%{case_id}"
}
}
}
output {
elasticsearch {
hosts => "elasticsearch:9200"
user => "elastic"
password => "changeme"
index => "case"
}
}
```
For example, i have `decision_id` in two consecutive row is **14** and **120**. Logstash doesn't move on at **decision\_id** = 14 and `:sql_last_value` stay at 14 because there is no document between `14 < decision_id < 19`. Are there anyway to update `:sql_last_value` or maybe another way ?
Thank alot.
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [January 3, 2020, 3:49pm UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695/2 "2020-01-03T15:49:41Z")
pilo
what happens if you run your sql without where clause?
is it gone a over load database.
elasticsearch should be able to handle many million records in one go.
few other thing I see from your config is that
it is gone a execute this query every second
it is gone a duplicate record if you run this again from other system or after removing last\_run\_metadata file.
---
### Author:  [@pilo](https://discuss.elastic.co/u/pilo)
#### Post date: [January 4, 2020, 1:40am UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695/3 "2020-01-04T01:40:51Z")
Hello, thank for your reply.
When i run your sql without where clause, i have javaheapsize outofmemory error.
Do you have another approach for this error ?
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [January 6, 2020, 5:56pm UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695/4 "2020-01-06T17:56:31Z")
you need to increase your java heap size
elkm01 ~]# grep Xms /etc/logstash/jvm.options
-Xms19g
how much memory your system has? assign little more memory to it. by default it might be 1gig which is not large enough.
---
### Author:  [@pilo](https://discuss.elastic.co/u/pilo)
#### Post date: [January 7, 2020, 1:40pm UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695/5 "2020-01-07T13:40:18Z")
Hello, thank you for your reply.
In fact i raised java heap size to 3g and can't go further.
Otherwise, after doing some search, i found this:
> [@Logstash jdbc input plugin pagination not working on version 7.4.2](https://discuss.elastic.co/t/logstash-jdbc-input-plugin-pagination-not-working-on-version-7-4-2/211783):
>
> Using jdbc input plugin, pagination is not working on version 7.4.2. Using version 7.0.0 it works fine. Any ideas what's missing? Logstash config: input { jdbc { jdbc\_driver\_library =\> "${LOGSTASH\_LIB\_DIR}/mysql-connector-java.jar" jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" jdbc\_connection\_string =\> "jdbc:${DATABASE\_URL}?sessionVariables=group\_concat\_max\_len=1000000" jdbc\_user =\> "${DATABASE\_USER}" jdbc\_password =\> "${DATABASE\_PASSWORD}" statement\_filepath =\> "${LO…
It's so weird that no one talk about this. I tried version 7.0.0 and it works.
Which version did you use ?
This is my config:
```auto
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_user => "${JDBC_USER}"
jdbc_password => "${JDBC_PASSWORD}"
jdbc_paging_enabled => true
jdbc_page_size => 10000
statement_filepath=> "/usr/share/logstash/config/indexing_sql/decision_indexing.sql"
type => "decision"
}
}
filter {
mutate {
add_field => {
"[my_join_field][name]" => "decision"
"[my_join_field][parent]" => "%{case_id}"
}
}
}
output {
elasticsearch {
hosts => "elasticsearch:9200"
user => "elastic"
password => "changeme"
index => "case"
routing => "%{case_id}"
}
}
```
Do you have any idea what's missing ?
---
### Author:  [@elasticforme](https://discuss.elastic.co/u/elasticforme)
#### Post date: [January 7, 2020, 4:34pm UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695/6 "2020-01-07T16:34:36Z")
I am not sure but I think after some verson in 7.x type has been remove. which type do not know. search on it.
type =\> decision
try very simple thing without much. first test that your data is being pulled from database and display on screen
```
input {
jdbc {
jdbc_connection_string => "jdbc:postgresql://${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}"
jdbc_driver_class => "org.postgresql.Driver"
jdbc_user => "${JDBC_USER}"
jdbc_password => "${JDBC_PASSWORD}"
jdbc_paging_enabled => true
jdbc_page_size => 10000
statement_filepath=> "/usr/share/logstash/config/indexing_sql/decision_indexing.sql"
}
}
filter {}
output {
stdout { codec => rubydebug }
}
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [February 4, 2020, 4:34pm UTC](https://discuss.elastic.co/t/sql-last-value-store-value-even-no-document-indexed/213695/7 "2020-02-04T16:34:42Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.