Hi,
I am trying to load data from MariaDB to elasticsearch using JDBC input plugin and elasticsearch output plugin.
both logstash and elasticsearch versions are 5.5.0
My logstash.conf file is:
input {
jdbc {
jdbc_driver_library => "mysql-connector-java-5.1.43-bin.jar"
jdbc_driver_class => "com.mysql.jdbc.Driver"
jdbc_connection_string => "jdbc:mysql://localhost:3306/exampledb"
jdbc_user => ""
parameters => { "source" => "example" }
jdbc_password => ""
jdbc_fetch_size => "5000"
schedule => " * * * *"
statement => "SELECT * from schedules"
}
}
output {
stdout { codec => json_lines }
elasticsearch {
hosts => "localhost:9200"
index => "/schedules"
}
}
When i run logstash with the above config file, i can see the data fetched from MariaDB, but after few seconds or a minute i see the data is refetched from MARIADB and it never goes to elasticsearch.
I have been scratching on this issue since very log, but unable to crack.
Output log file:
root@abt-dev:/logstash/logstash-5.5.0/bin# ./logstash -f logstash.conf
ERROR StatusLogger No log4j2 configuration file found. Using default configuration: logging only errors to the console.
Sending Logstash's logs to /logstash/logstash-5.5.0/logs which is now configured via log4j2.properties
[2017-07-31T11:41:05,606][INFO ][logstash.setting.writabledirectory] Creating directory {:setting=>"path.queue", :path=>"/logstash/logstash-5.5.0/data/queue"}
[2017-07-31T11:41:05,609][INFO ][logstash.setting.writabledirectory] Creating directory {:setting=>"path.dead_letter_queue", :path=>"/logstash/logstash-5.5.0/data/dead_letter_queue"}
[2017-07-31T11:41:05,627][INFO ][logstash.agent ] No persistent UUID file found. Generating new UUID {:uuid=>"3dd9ad06-c15e-4a9f-bd58-15969d4534bd", :path=>"/logstash/logstash-5.5.0/data/uuid"}
[2017-07-31T11:41:06,078][INFO ][logstash.outputs.elasticsearch] Elasticsearch pool URLs updated {:changes=>{:removed=>[], :added=>[http://localhost:9200/]}}
[2017-07-31T11:41:06,083][INFO ][logstash.outputs.elasticsearch] Running health check to see if an Elasticsearch connection is working {:healthcheck_url=>http://localhost:9200/, :path=>"/"}
[2017-07-31T11:41:06,170][WARN ][logstash.outputs.elasticsearch] Restored connection to ES instance {:url=>#Java::JavaNet::URI:0x531660e2}
[2017-07-31T11:41:06,171][INFO ][logstash.outputs.elasticsearch] Using mapping template from {:path=>nil}
[2017-07-31T11:41:06,226][INFO ][logstash.outputs.elasticsearch] Attempting to install template {:manage_template=>{"template"=>"logstash-", "version"=>50001, "settings"=>{"index.refresh_interval"=>"5s"}, "mappings"=>{"default"=>{"_all"=>{"enabled"=>true, "norms"=>false}, "dynamic_templates"=>[{"message_field"=>{"path_match"=>"message", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false}}}, {"string_fields"=>{"match"=>"", "match_mapping_type"=>"string", "mapping"=>{"type"=>"text", "norms"=>false, "fields"=>{"keyword"=>{"type"=>"keyword", "ignore_above"=>256}}}}}], "properties"=>{"@timestamp"=>{"type"=>"date", "include_in_all"=>false}, "@version"=>{"type"=>"keyword", "include_in_all"=>false}, "geoip"=>{"dynamic"=>true, "properties"=>{"ip"=>{"type"=>"ip"}, "location"=>{"type"=>"geo_point"}, "latitude"=>{"type"=>"half_float"}, "longitude"=>{"type"=>"half_float"}}}}}}}}
[2017-07-31T11:41:06,232][INFO ][logstash.outputs.elasticsearch] New Elasticsearch output {:class=>"LogStash::Outputs::ElasticSearch", :hosts=>[#Java::JavaNet::URI:0x7b7cbd95]}
[2017-07-31T11:41:06,234][INFO ][logstash.pipeline ] Starting pipeline {"id"=>"main", "pipeline.workers"=>2, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>5, "pipeline.max_inflight"=>250}
[2017-07-31T11:41:06,341][INFO ][logstash.pipeline ] Pipeline main started
[2017-07-31T11:41:06,406][INFO ][logstash.agent ] Successfully started Logstash API endpoint {:port=>9600}
[2017-07-31T11:42:00,824][INFO ][logstash.inputs.jdbc ] (0.010000s) SELECT * from schedules
{"enddate":"2017-07-20T11:30:38.000Z","@timestamp":"2017-07-31T06:12:00.846Z","logfile":" \nJob name: Full_Data_Load\nLogging started on: 20-07-2017 17:00:36\n \nMariaDB connection successfully established - function: dbconnect\n \nSuccessfully extracted DATA TIME from head of TXT file\n \nSuccessfully extracted Total no of records from tail of TXT file - function: gettail\n \nSuccessfully inserted job details in the database - function: insertdb\n \nSuccessfully extracted relevant lines from source file - function pulllines\n \nNo of records from data source: 370\nNo of inserts in the database: 370\nSuccessfully completed data load in to the database. Updating Schedule in the database.\n","datatime":"07/19/2017 18:51:14","@version":"1","id":12,"source":"ed2go","startdate":"2017-07-20T11:30:36.000Z","script_name":"Full_Data_Load","status":"SUCCESS"}
{"enddate":"2017-07-20T11:31:40.000Z","@timestamp":"2017-07-31T06:12:00.848Z","logfile":" \nJob name: Delta_Data_Load\nLogging started on: 20-07-2017 17:01:38\n \nMariaDB connection successfully established - function: dbconnect\n \nSuccessfully extracted DATA TIME from head of TXT file\n \nSuccessfully extracted Total no of records from tail of TXT file - function: gettail\n \nSuccessfully inserted job details in the database - function: insertdb\n \nSuccessfully extracted relevant lines from source file - function pulllines\n \nSuccessfully completed splitting lines into INSERT, UPDATE and DELETE lists - function: splitdmls\n \nNo of records from data source: 89\nNo of inserts in the database: 0\nNo of updates in the database: 89\nNo of deletes in the database: 0\nSuccessfully completed data load in to the database. Updating job schedule in the database.\n","datatime":"07/19/2017 18:51:14","@version":"1","id":13,"source":"ed2go","startdate":"2017-07-20T11:31:38.000Z","script_name":"Delta_Data_Load","status":"SUCCESS"}
And the same select output repeats on the screen.