# Float values are imported incorrectly from mySQL DB

**URL:** <https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970>\
**Category:** Logstash\
**Created:** [November 29, 2019, 1:14pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970 "2019-11-29T13:14:52Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![vschaefer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vschaefer/32/40641_2.png) [@vschaefer](https://discuss.elastic.co/u/vschaefer)\
**Post date:** [November 29, 2019, 1:14pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/1 "2019-11-29T13:14:52Z")

</div>

I import data from a mySQL DB via Logstash into an Elasticsearch index.  
The database contains float values in a field.  
However, these values change during import.  
3378.2 from the mySQL DB becomes 3378.199951171875 in the Elasticsearch Index.  
Does anyone have an idea why that is and how I can fix the error?  
Is there a possibility to round the values by mutations during import?

Thanks for the help.

---

<div class="post-metadata">

**Author:** ![shin-higuchi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shin-higuchi/32/46524_2.png) [@shin-higuchi](https://discuss.elastic.co/u/shin-higuchi)\
**Post date:** [November 29, 2019, 1:18pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/2 "2019-11-29T13:18:58Z")

</div>

Could you share your logstash config file?

---

<div class="post-metadata">

**Author:** ![vschaefer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vschaefer/32/40641_2.png) [@vschaefer](https://discuss.elastic.co/u/vschaefer)\
**Post date:** [November 29, 2019, 1:46pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/3 "2019-11-29T13:46:46Z")

</div>

```
input {
  jdbc {
    jdbc_paging_enabled => true
    jdbc_page_size => 5000
    jdbc_driver_library => "/opt/logstash/mysql-connector-java-5.1.44-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://db:3306/lightscout_data_2020"
    jdbc_user => "root"
    jdbc_password => "**"
    statement => "SELECT * FROM `familien_artikeldaten_2020_de`"
    type=>"artikeldaten_2020_de"
  }
}

filter {
  mutate {
    convert => {
      "pkm_artikeldaten" => "string"
    }
  }
}

output {
  if [type] == "artikeldaten_2020_de" {
    elasticsearch {
      index => "artikeldaten_2020_de"
      hosts => "elasticsearch:9200"
          document_id => "%{pkm_artikeldaten}"
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [November 29, 2019, 2:26pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/4 "2019-11-29T14:26:33Z")

</div>

In a single precision float there are 23 bits to the mantissa. Everything else is a rounding error. 2^23 is 8MM, so you should not expect accuracy beyond 7 digits.

---

<div class="post-metadata">

**Author:** ![vschaefer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vschaefer/32/40641_2.png) [@vschaefer](https://discuss.elastic.co/u/vschaefer)\
**Post date:** [November 29, 2019, 2:32pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/5 "2019-11-29T14:32:28Z")

</div>

Okay, what can I do to import the data without the many decimal places?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [November 29, 2019, 2:40pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/6 "2019-11-29T14:40:22Z")

</div>

You could try using a ruby filter to call the round method of Float.

---

<div class="post-metadata">

**Author:** ![vschaefer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vschaefer/32/40641_2.png) [@vschaefer](https://discuss.elastic.co/u/vschaefer)\
**Post date:** [November 29, 2019, 5:09pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/7 "2019-11-29T17:09:01Z")

</div>

I have now added a Ruby filter and also installed the plugin via `bin/logstash-plugin install logstash-filter-ruby`.  
Unfortunately I get the following error now:

```
root@376f1ace7f99:/opt/logstash# bin/logstash 
Sending Logstash logs to /opt/logstash/logs which is now configured via log4j2.properties
[2019-11-29T17:05:02,335][INFO][logstash.runner] Starting Logstash {"logstash.version"=>"7.0.1"}
[2019-11-29T17:05:10,228][ERROR][logstash.plugins.registry] Tried to load a plugin's code, but failed. {:exception=>#<LoadError: no such file to load -- logstash/filters/"ruby">, :path=>"logstash/filters/\"ruby\"", :type=>"filter", :name=>"\"ruby\""}
[2019-11-29T17:05:10,259][ERROR][logstash.agent] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:main, :exception=>"LogStash::PluginLoadingError", :message=>"Couldn't find any filter plugin named '\"ruby\"'. Are you sure this is correct? Trying to load the \"ruby\" filter plugin resulted in this error: no such file to load -- logstash/filters/\"ruby\"", :backtrace=>["/opt/logstash/logstash-core/lib/logstash/plugins/registry.rb:211:in `lookup_pipeline_plugin'", "/opt/logstash/logstash-core/lib/logstash/plugin.rb:137:in `lookup'", "org/logstash/plugins/PluginFactoryExt.java:200:in `plugin'", "org/logstash/execution/JavaBasePipelineExt.java:50:in `initialize'", "/opt/logstash/logstash-core/lib/logstash/java_pipeline.rb:23:in `initialize'", "/opt/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:36:in `execute'", "/opt/logstash/logstash-core/lib/logstash/agent.rb:325:in `block in converge_state'"]}
[2019-11-29T17:05:10,833][INFO][logstash.agent] Successfully started Logstash API endpoint {:port=>9600}
[2019-11-29T17:05:15,631][INFO][logstash.runner] Logstash shut down.

```

Do I have to do anything else to use the plugin?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [November 29, 2019, 5:18pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/8 "2019-11-29T17:18:02Z")

</div>

The ruby filter is installed by default.

It looks to me as though you have

```
filter { "ruby" { ... } }

```

when you should have

```
filter { ruby { ... } }
```

---

<div class="post-metadata">

**Author:** ![vschaefer](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vschaefer/32/40641_2.png) [@vschaefer](https://discuss.elastic.co/u/vschaefer)\
**Post date:** [November 29, 2019, 5:21pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/9 "2019-11-29T17:21:11Z")

</div>

Yes, that's the solution. Thanks!

---

<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:** [December 27, 2019, 5:21pm UTC](https://discuss.elastic.co/t/float-values-are-imported-incorrectly-from-mysql-db/209970/10 "2019-12-27T17:21:16Z")

</div>

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