# When using logstash to index data from MySQL to ElasticSearch,only the first row is being displayed

**URL:** <https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086>\
**Category:** Logstash\
**Created:** [January 26, 2016, 6:10am UTC](https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086 "2016-01-26T06:10:36Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sreevatsa\_Vadiraj](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Sreevatsa\_Vadiraj](https://discuss.elastic.co/u/Sreevatsa_Vadiraj)\
**Post date:** [January 26, 2016, 6:10am UTC](https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086/1 "2016-01-26T06:10:36Z")

</div>

This is my logstash configuration file:

```
      input
{
    jdbc 
    {

     jdbc_driver_library => "/home/vatsa/logstash/mysql-connector-java-5.1.38-bin.jar"

     jdbc_driver_class => "com.mysql.jdbc.Driver"

     jdbc_connection_string => "jdbc:mysql://localhost:3306/stud"

     jdbc_user => "root"
     jdbc_password => ""
      statement => "select * from det"
   }
}

```

output

{  
elasticsearch  
{

```
index => "det"
document_type => "contact"
document_id => "%{uid}"
hosts => "localhost:9200"

```

}stdout { codec =\> json\_lines }  
}

Logstash startup and shutdown is created.But when i do a get request using,

curl -XGET 'localhost:9200/det/\_search?pretty&q=\*' ,this is the output:

"hits" : { "total" : 1, "max\_score" : 1.0, "hits" : [{ "\_index" : "det", "\_type" : "contact", "\_id" : "%{uid}", "\_score" : 1.0, "\_source":{"ID":5,"NAME":"SHUBH","USN":"099","@version":"1","@timestamp":"2016-01-26T05:42:08.362Z"} }] }

But there are 5 entities in the table.What is the reason for this??How to solve this??

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [January 26, 2016, 6:28am UTC](https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086/2 "2016-01-26T06:28:36Z")

</div>

Your database record does not seem to contain a field called 'uid', which means that the string '%{uid}' is used for all records, causing the same document to be updated multiple times. Use the stdout output plugin with a ruby debug codec to see the exact structure of your event and the fields available in Logstash.

---

<div class="post-metadata">

**Author:** ![Sreevatsa\_Vadiraj](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Sreevatsa\_Vadiraj](https://discuss.elastic.co/u/Sreevatsa_Vadiraj)\
**Post date:** [January 26, 2016, 7:10am UTC](https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086/3 "2016-01-26T07:10:45Z")

</div>

Hey,  
Thanks a lot [Sir.It](http://Sir.It) worked.

But my MySQL table has 191000 rows.when i try "logstash/bin/logstash -f /home/vatsa/logs/conts-out.conf",it runs for a while and shows this error.

java.lang.OutOfMemoryError: Java heap space  
Dumping heap to /home/vatsa/logstash/heapdump.hprof  
Exception flushing buffer at interval! {:error=\>"Java heap space", :class=\>"Java::JavaLang::OutOfMemoryError", :level=\>:warn}  
Error: Your application used more memory than the safety cap of 1G.  
Specify -J-Xmx####m to increase it (#### = cap size in MB).  
Specify -w for full OutOfMemoryError stack trace

I dint understand the error.How to fix this.  
Thanks in advance.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [January 26, 2016, 8:39am UTC](https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086/4 "2016-01-26T08:39:13Z")

</div>

Does it make a difference if you e.g. set the [jdbc\_fetch\_size](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html#plugins-inputs-jdbc-jdbc_fetch_size) parameter, e.g. to 10000?

---

<div class="post-metadata">

**Author:** ![Sreevatsa\_Vadiraj](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@Sreevatsa\_Vadiraj](https://discuss.elastic.co/u/Sreevatsa_Vadiraj)\
**Post date:** [January 26, 2016, 9:50am UTC](https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086/5 "2016-01-26T09:50:02Z")

</div>

No sir.The java heapspace error is thrown..

this is the error :  
java.lang.OutOfMemoryError: Java heap space  
Dumping heap to /home/vatsa/logstash/heapdump.hprof  
Exception  
flushing buffer at interval! {:error=\>"Java heap space",  
:class=\>"Java::JavaLang::OutOfMemoryError", :level=\>:warn}  
Error: Your application used more memory than the safety cap of 1G.  
Specify -J-Xmx####m to increase it (#### = cap size in MB).  
Specify -w for full OutOfMemoryError stack trace

I couldnt understand the error.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 5:14am UTC](https://discuss.elastic.co/t/when-using-logstash-to-index-data-from-mysql-to-elasticsearch-only-the-first-row-is-being-displayed/40086/6 "2017-07-06T05:14:21Z")

</div>