Index : MySQL to Elasticsearch 6.2 with Logstash 6.2

Elastic Stack Elasticsearch
21

This is a DEBUG level message and should not be a problem. A real error would have been logged as ERROR level.

Considering localhost the address of the Logstash host, what's the output of:

curl 'localhost:9600/_node/stats/pipelines?pretty'

Also, please, post the output of the _cat/indices?v

22

For curl 'localhost:9600/_node/stats/pipelines?pretty'

{
"host" : "ns354122",
"version" : "6.2.4",
"http_address" : "127.0.0.1:9600",
"id" : "673f1c12-462a-4655-927f-ef4f2053b5be",
"name" : "ns354122",
"pipelines" : {
".monitoring-logstash" : {
"events" : null,
"plugins" : {
"inputs" : ,
"filters" : ,
"outputs" :
},
"reloads" : {
"last_error" : null,
"successes" : 0,
"last_success_timestamp" : null,
"last_failure_timestamp" : null,
"failures" : 0
},
"queue" : null
},
"main" : {
"events" : {
"duration_in_millis" : 0,
"in" : 0,
"out" : 0,
"filtered" : 0,
"queue_push_duration_in_millis" : 0
},
"plugins" : {
"inputs" : [ {
"id" : "544b514eb4d4ba0a39dd80ae002405706ffffa2220d81a3ec64b8f671e87fc87",
"events" : {
"out" : 0,
"queue_push_duration_in_millis" : 0
},
"name" : "jdbc"
}, {
"id" : "46256ae4d1cf9b68b666ffda76d13b16ce4a87ff4d947d7ad762338c95d33bdf",
"events" : {
"out" : 0,
"queue_push_duration_in_millis" : 0
},
"name" : "beats"
} ],
"filters" : [ {
"id" : "e7f400907608e12662961c253e8708900b15723abfefcfa4ac0bb95626430926",
"events" : {
"duration_in_millis" : 0,
"in" : 0,
"out" : 0
},
"name" : "date"
}, {
"id" : "75304a5fffa1079dd7d499b9aeac85499c53d87afc41831d975e634306fefcf1",
"events" : {
"duration_in_millis" : 0,
"in" : 0,
"out" : 0
},
"matches" : 0,
"failures" : 0,
"patterns_per_field" : {
"message" : 1
},
"name" : "grok"
}, {
"id" : "83ba4e3b99a9353a09e01cb1cd79bb6dd1b22a16f3a602f5b2c5c3a5a9fcdf35",
"events" : {
"duration_in_millis" : 0,
"in" : 0,
"out" : 0
},
"name" : "syslog_pri"
} ],
"outputs" : [ {
"id" : "73bbe3fb71c71ab4e8c6d6294ec4bab5eec65810f6d4933be5387291a64bec30",
"events" : {
"duration_in_millis" : 0,
"in" : 0,
"out" : 0
},
"name" : "elasticsearch"
}, {
"id" : "f2b6a606dedab1233cb2483bc6cbdff21cea9855dad124b80fb015807fefc56a",
"events" : {
"duration_in_millis" : 0,
"in" : 0,
"out" : 0
},
"name" : "elasticsearch"
} ]
},
"reloads" : {
"last_error" : null,
"successes" : 0,
"last_success_timestamp" : null,
"last_failure_timestamp" : null,
"failures" : 0
},
"queue" : {
"type" : "memory"
}
}
}
}

And for _cat/indices?v, I created a gist

23

So according to this:

{
  "id": "544b514eb4d4ba0a39dd80ae002405706ffffa2220d81a3ec64b8f671e87fc87",
  "events": {
    "out": 0,
    "queue_push_duration_in_millis": 0
  },
  "name": "jdbc"
}

No events are being produced by the JDBC plugin. So either the database is empty or the database is taking a really long time to return the rows. By default, the jdbc_fetch_size is 100 (for this MySQL Driver), so if the database table is not empty then the database is taking a long time to return 100 rows. You could:

  1. Try setting jdbc_fetch_size => 1 to see if there is any activity at all.
  2. Check if maybe the database table is locked somehow and the query is being blocked from executing.
24

Thanks for the informations. I've just tried with fetch_size equal to 1 but nothing changes. But because, I use MySQL from another physical server, I will install it and use it on the same machine. Maybe, this is the problem ...

25

DONE !
It's me... again... But I resolved my problem ! :slight_smile:
As I said, I moved my MySQL db to the same machine and I reconfigured it. Unfortunately, the problem remained. So I redownloaded the JDBC (mysql-connector-java-5.1.42-bin.jar) but another and ... It works now.

Maybe I forget some parts of the process. But this is mainly what I did.

@thiago, again, thank you for your time and the help that you gave me ! :slight_smile:

P.S. : I still have a question, if I remove a row from MySQL how to automatically removed it from Elasticsearch ?

Thx !

26

I am glad things are working for you! Sometimes just the basic "turning it off and on again" is enough for solving issues :wink:

I am afraid this is not how it works. The JDBC input plugin will simply read all the rows from a given query and stream them down the pipeline and there is no logic related to deleted and/or updated rows. You would need to implement your own strategy to handle that. For instance, one that is commonly used, if your table is small enough and can be flushed into Elasticsearch within minutes, then, every hour, you simply create a new index with what's in the table.

27

The approach we have always used in relation to deletes is to perform a logical delete (e.g. set status to inactive). This is always the best approach - and makes troubleshooting much easier.

Another approach is just to index everything all the time and filter data in your elastic queries that has not be updated for a week.

1 Like
28

@Johnnycc1 & @thiago thx for your replies, I will try it soon ! :wink:

29

One more thing, if I may.
I'm trying to use Elasticsearch with an auto-complete mode. So I should use a completion suggest field for some fields like extID and name. I found this tutorial that helps me a lot.

So, my question is, how can I set extID and name as completion suggest field using Logstash and JDBC ? Should I implement a filter in Logstash's conf ? How can I perform that ?

Thanks a lot for all the help that you give me !

30

Hey phenric,

I have a sample configuration of how to take a CSV (geo names dataset) process it through logstash, which creates the appropriate _mapping then loads the data over here -->

Basically your elasticsearch output configuration includes the template that is applied before the data is indexed. And you format the data in your logstash filter to meet that configuration.

You should be able to adapt this to your requirements.

31

Thanks for all ! :slight_smile:

32

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.