# Index : MySQL to Elasticsearch 6.2 with Logstash 6.2
**URL:** https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573
**Category:** Elasticsearch
**Created:** [May 12, 2018, 2:04pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573 "2018-05-12T14:04:54Z")
**Posts on this page:** 12
**Page:** 2
### Author:  [@thiago](https://discuss.elastic.co/u/thiago)
#### Post date: [May 14, 2018, 1:25pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/21 "2018-05-14T13:25:48Z")
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:
```auto
curl 'localhost:9600/_node/stats/pipelines?pretty'
```
Also, please, post the output of the `_cat/indices?v`
---
### Author:  [@phenric](https://discuss.elastic.co/u/phenric)
#### Post date: [May 14, 2018, 1:29pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/22 "2018-05-14T13:29:27Z")
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](https://gist.github.com/phenric/3e7fe1c0eeadfc2df1224b506db71ab0)
---
### Author:  [@thiago](https://discuss.elastic.co/u/thiago)
#### Post date: [May 14, 2018, 1:38pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/23 "2018-05-14T13:38:33Z")
So according to this:
```auto
{
"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.
---
### Author:  [@phenric](https://discuss.elastic.co/u/phenric)
#### Post date: [May 14, 2018, 1:54pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/24 "2018-05-14T13:54:56Z")
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 ...
---
### Author:  [@phenric](https://discuss.elastic.co/u/phenric)
#### Post date: [May 14, 2018, 5:41pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/25 "2018-05-14T17:41:30Z")
DONE !
It's me... again... But I resolved my problem ! 🙂
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 ! 🙂
P.S. : I still have a question, if I remove a row from MySQL how to automatically removed it from Elasticsearch ?
Thx !
---
### Author:  [@thiago](https://discuss.elastic.co/u/thiago)
#### Post date: [May 14, 2018, 5:46pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/26 "2018-05-14T17:46:50Z")
I am glad things are working for you! Sometimes just the basic "turning it off and on again" is enough for solving issues 😉
> [@phenric](#):
>
> P.S. : I still have a question, if I remove a row from MySQL how to automatically removed it from Elasticsearch ?
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.
---
### Author:  [@Johnnycc1](https://discuss.elastic.co/u/Johnnycc1)
#### Post date: [May 14, 2018, 11:06pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/27 "2018-05-14T23:06:06Z")
> [@thiago](#):
>
> 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.
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.
---
### Author:  [@phenric](https://discuss.elastic.co/u/phenric)
#### Post date: [May 16, 2018, 9:57am UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/28 "2018-05-16T09:57:17Z")
@Johnnycc1 & @thiago thx for your replies, I will try it soon ! 😉
---
### Author:  [@phenric](https://discuss.elastic.co/u/phenric)
#### Post date: [May 16, 2018, 12:44pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/29 "2018-05-16T12:44:02Z")
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](https://blog.manifold.co/leveraging-the-power-of-elasticsearch-autocomplete-and-fuzzy-search-1d491d3e0b38) 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 !
---
### Author:  [@Johnnycc1](https://discuss.elastic.co/u/Johnnycc1)
#### Post date: [May 18, 2018, 11:10pm UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/30 "2018-05-18T23:10:22Z")
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 --\>
> **[swarmee/projects](https://github.com/swarmee/projects/tree/master/elastic-stack-geonames-cities)**
>
> projects - Various Projects Using Elasticsearch
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.
---
### Author:  [@phenric](https://discuss.elastic.co/u/phenric)
#### Post date: [May 19, 2018, 7:45am UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/31 "2018-05-19T07:45:09Z")
Thanks for all ! 🙂
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [June 16, 2018, 7:45am UTC](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573/32 "2018-06-16T07:45:09Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.
[Previous page](https://discuss.elastic.co/t/index-mysql-to-elasticsearch-6-2-with-logstash-6-2/131573.md?page=1)