"plain_228763b2-647f-435e-9951-40326c5620b6", enable_metric=>true, charset=>"UTF-8">, workers=>1, manage_template=>true, template_name=>"logstash", template_overwrite=>false, doc_as_upsert=>false, script_type=>"inline", script_lang=>"painless", script_var_name=>"event", scripted_upsert=>false, retry_initial_interval=>2, retry_max_interval=>64, retry_on_conflict=>1, action=>"index", ssl_certificate_verification=>true, sniffing=>false, sniffing_delay=>5, timeout=>60, pool_max=>1000, pool_max_per_route=>100, resurrect_delay=>5, validate_after_inactivity=>10000, http_compression=>false>}
```
---
### Author:  [@groverjatin17](https://discuss.elastic.co/u/groverjatin17)
#### Post date: [October 23, 2018, 6:07am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/3 "2018-10-23T06:07:44Z")
Hello. thanksfor your replye.
No it does not view new records. Every time same console line appears. if I have inserted 2 new records the value should have been 4 because it should have been the last row. Nor does in Kibana.
I think they get deleted after i stop logstash from my VM and restart by changing the index name or something else to try in the original logstash.conf file. But the database table remains the same.
Regarding the warning. I did google it and they are saying in current version it should not bother. So i will hold on to it for a while.
Moreover, I stopped everything and tried running it again. And now the script is showing 0 instead of 2 in the logs of the logstash. And the worse part is the index does not appear in ElasticSearch or Kibana. So I dont even have the data which runs for the first time as well. ☹
---
### Author:  [@groverjatin17](https://discuss.elastic.co/u/groverjatin17)
#### Post date: [October 23, 2018, 7:04am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/4 "2018-10-23T07:04:43Z")
To add to the moreover part. I mentioned that ran the same log.conf by changing the index name and document\_type right, whereas the rest remains the same.
I just checked that the "mytable" dummy table is empty now. It had 3 rows all gone.
Also, I cannot even drop that table it says "Interested source is busy".
Please help me find what is happening.
---
### Author:  [@redX](https://discuss.elastic.co/u/redX)
#### Post date: [October 23, 2018, 8:01am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/5 "2018-10-23T08:01:01Z")
Why have you specified the output `stdout { codec => json_lines }`? You do not have json. Try `rubydebug`
I tried a minimal working example myself and it is working fine.
I did my test with sql server. I do not have an Oracle DB laying around.
DB setup:
```
CREATE TABLE [dbo].[thetable](
[user_id] [int] IDENTITY(1,1) NOT NULL,
[text] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[thetable]([text]) VALUES('a')
INSERT INTO [dbo].[thetable]([text]) VALUES('b')
INSERT INTO [dbo].[thetable]([text]) VALUES('c')
```
Logstash conf:
```
input {
jdbc {
jdbc_driver_library => "C:\ProgramFiles\ms-jdbc-driver-6.0\enu\jre8\sqljdbc42.jar"
jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
jdbc_connection_string => "jdbc:sqlserver://sql-server\inst:56000"
jdbc_user => "user"
jdbc_password => "password"
schedule => "* * * * *"
statement => "SELECT * from [dbo].[thetable] WHERE user_id > :sql_last_value ORDER by user_id"
use_column_value => true
tracking_column => user_id
tracking_column_type => "numeric"
clean_run => true
record_last_run => true
}
}
output {
stdout {
codec => rubydebug
}
}
```
Output is:
```
(0.049986s) SELECT * from [dbo].[thetable] WHERE user_id > 0 ORDER by user_id
{
"@version" => "1",
"user_id" => 1,
"text" => "a",
"@timestamp" => 2018-10-23T07:49:03.136Z
}
{
"@version" => "1",
"user_id" => 2,
"text" => "b",
"@timestamp" => 2018-10-23T07:49:03.145Z
}
{
"@version" => "1",
"user_id" => 3,
"text" => "c",
"@timestamp" => 2018-10-23T07:49:03.145Z
}
```
When I insert 2 new records, I get:
```
(0.001570s) SELECT * from [dbo].[thetable] WHERE user_id > 3 ORDER by user_id
{
"@version" => "1",
"user_id" => 4,
"text" => "d",
"@timestamp" => 2018-10-23T07:50:00.207Z
}
{
"@version" => "1",
"user_id" => 5,
"text" => "e",
"@timestamp" => 2018-10-23T07:50:00.207Z
}
```
The only thing I omitted is the `last_run_metadata_path => "/path/.logstash_jdbc_last_run"`part.
It seems your config is basically working.
Try removing the `last_run_metadata_path`, removing all outputs and only use the `rubydebug`one, and expand from there.
What ever is deleting your records, I expect it to be something else than Logstash.
---
### Author:  [@groverjatin17](https://discuss.elastic.co/u/groverjatin17)
#### Post date: [October 23, 2018, 8:58am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/6 "2018-10-23T08:58:51Z")
Hi redX,
Thank you for your answer. i actually tried exactly what you mentioned.
I commented the last run metadata parameter and changed codec to rubydebug.
Changed the index name and document\_type name.
But unfortunately it is the still same actually. 😢
This is the config I ran:-
> Input{
> jdbc{
> jdbc\_connection\_string =\> "jdbc:{oracle:thin:@host:port/XE"
> jdbc\_user =\> "user"
> jdbc\_password =\> "pass"
> jdbc\_driver\_library =\> "/path/JatinSandbox/KibanaDbTry/ojdbc7.jar"
> jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver"
> #jdbc\_validate\_connection =\> true
> #statement =\> "select \* from mytable"
> statement =\> "SELECT \* from mtable WHERE user\_id \> :sql\_last\_value ORDER by user\_id"
> schedule =\> "\* \* \* \* \*"
> use\_column\_value =\> true
> tracking\_column =\> "user\_id"
> tracking\_column\_type =\> "numeric"
> clean\_run =\> true
> record\_last\_run =\> true
> #last\_run\_metadata\_path =\> "/home/toms/.logstash\_jdbc\_last\_run"
> }
>
> }
> output {
> stdout{
> codec =\> rubydebug
> }
> elasticsearch {
> index =\> "batchdatas"
> hosts =\> "hostname"
> document\_type =\> "schedulemine"
> document\_id =\> "%{user\_id}"
> }
>
> }
The output is same. Logstash try to run the statement again and again with 0 as last sql run value. And the index is not listed in Elasticsearch or Kibana
```
[2018-10-23T08:40:08,802][INFO][logstash.inputs.jdbc] (0.072629s) SELECT * from mtable WHERE user_id > 0 ORDER by user_id
[2018-10-23T08:41:00,160][INFO][logstash.inputs.jdbc] (0.002166s) SELECT * from mtable WHERE user_id > 0 ORDER by user_id
[2018-10-23T08:42:00,348][INFO][logstash.inputs.jdbc] (0.001678s) SELECT * from mtable WHERE user_id > 0 ORDER by user_id
```
---
### Author:  [@redX](https://discuss.elastic.co/u/redX)
#### Post date: [October 23, 2018, 11:16am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/7 "2018-10-23T11:16:02Z")
Please give me your database table structure and some insert statement to reproduce the problem.
If you execute the query `SELECT * from mtable WHERE user_id > 0 ORDER by user_id` manually against your database, do you get records?
---
### Author:  [@groverjatin17](https://discuss.elastic.co/u/groverjatin17)
#### Post date: [October 23, 2018, 11:30am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/8 "2018-10-23T11:30:32Z")
Its simple actually. I am also trying this out before i implement on real table. Dont want real data to be deleted.
> Create table mytable(user\_id int, names varchar(20));
and then few run first 3 statements where I run first 3 at first and then rest 2 after i run logstash file to confirm schedule does it job.
> insert into mytable(user\_id, names) values(1,'sham');
> insert into mytable(user\_id, names) values(2,'shama');
> insert into mytable(user\_id, names) values(3,'shamb');
> insert into mytable(user\_id, names) values(4,'shamc');
> insert into mytable(user\_id, names) values(5,'shamd');
And about the statement parameter in Config file. I did get the result on friday where the first 3 records were present not the remaining 2 which i inserted after running the file.
But since yesterday I am unable to see even the index in ES.
---
### Author:  [@balumurari1](https://discuss.elastic.co/u/balumurari1)
#### Post date: [October 25, 2018, 7:48am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/9 "2018-10-25T07:48:12Z")
If you were using Oracle DB to load the data to ES, check below code,
> [@Logstash doesn't apply my ES Template when I upload data](https://discuss.elastic.co/t/logstash-doesnt-apply-my-es-template-when-i-upload-data/153813/6):
>
> The input code is fine, in the output did you mention elasticsearch to get the result, Try this, input { jdbc { jdbc\_driver\_library =\> "xxxx\oracle-10g\ojdbc14.jar" jdbc\_driver\_class =\> "oracle.jdbc.driver.OracleDriver" jdbc\_connection\_string =\> "jdbc:oracle:thin:@localhost:1521:DATABASE" jdbc\_user =\> "ROMAINROM" jdbc\_password =\> "ROMAINROM" statement =\> "SELECT TOP 10 \* FROM TABLE" jdbc\_paging\_enabled =\> "true" jdbc\_page\_size =\> "50000" } } output{ elasticsearch { codec =\> json h…
To load the changes occurring in the databse use schedule =\> "\*/2 \* \* \* \*",
schedule for every 2 min
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 22, 2018, 7:48am UTC](https://discuss.elastic.co/t/logstash-jdbc-input-table-data-not-in-sync-with-kibana-index/153395/10 "2018-11-22T07:48:12Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.