# Binary conversion error using Logstash to ElasticSearch

**URL:** <https://discuss.elastic.co/t/binary-conversion-error-using-logstash-to-elasticsearch/71368>\
**Category:** Logstash\
**Created:** [January 12, 2017, 12:09pm UTC](https://discuss.elastic.co/t/binary-conversion-error-using-logstash-to-elasticsearch/71368 "2017-01-12T12:09:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![markeaustin](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@markeaustin](https://discuss.elastic.co/u/markeaustin)\
**Post date:** [January 12, 2017, 12:09pm UTC](https://discuss.elastic.co/t/binary-conversion-error-using-logstash-to-elasticsearch/71368/1 "2017-01-12T12:09:15Z")

</div>

I am trying to use jdbc-sql to write data from a SQL Server database into ElasticSearch. One of the columns in each of the two tables is a VARBINARY(MAX). These are failing conversion with an error;

[2017-01-12T11:51:30,022][WARN][logstash.outputs.elasticsearch] Failed action.  
{:status=\>400, :action=\>["index", {:\_id=\>"40993", :\_index=\>"audit-201701", :\_typ  
e=\>"SystemAuditTable", :\_routing=\>nil}, 2017-01-12T11:51:28.538Z %{host} %{messa  
ge}], :response=\>{"index"=\>{"\_index"=\>"audit-201701", "\_type"=\>"SystemAuditTable  
", "\_id"=\>"40993", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception",  
"reason"=\>"failed to parse [encryptedpatient]", "caused\_by"=\>{"type"=\>"json\_pars  
e\_exception", "reason"=\>"Failed to decode VALUE\_STRING as base64 (MIME-NO-LINEFE  
EDS): Illegal character '\' (code 0x5c) in base64 content\n at [Source: org.ela  
sticsearch.common.bytes.BytesReference$MarkSupportingStreamInputWrapper@3eeec71a  
; line: 1, column: 109]"}}}}}  
[2017-01-12T11:51:30,022][WARN][logstash.outputs.elasticsearch] Failed action.  
{:status=\>400, :action=\>["index", {:\_id=\>"41362", :\_index=\>"audit-201701", :\_typ  
e=\>"SystemAuditTable", :\_routing=\>nil}, 2017-01-12T11:51:29.054Z %{host} %{messa  
ge}], :response=\>{"index"=\>{"\_index"=\>"audit-201701", "\_type"=\>"SystemAuditTable  
", "\_id"=\>"41362", "status"=\>400, "error"=\>{"type"=\>"mapper\_parsing\_exception",  
"reason"=\>"failed to parse [encryptedpatient]", "caused\_by"=\>{"type"=\>"json\_pars  
e\_exception", "reason"=\>"Failed to decode VALUE\_STRING as base64 (MIME-NO-LINEFE  
EDS): Illegal character '\' (code 0x5c) in base64 content\n at [Source: org.ela  
sticsearch.common.bytes.BytesReference$MarkSupportingStreamInputWrapper@79b87c;  
line: 1, column: 109]"}}}}}

The sql query is

SELECT TOP (50000)  
[Id]  
,[RevisionStamp]  
,[Type] as SystemAuditType  
,[Action]  
,[IpAddress]  
,[SectionId]  
,[UserId]  
,[PatientId]  
,[TreatmentId]  
,[Patient]  
,CAST([EncryptedPatient] AS VARBINARY(MAX)) AS EncryptedPatient  
FROM NewINRstarAudit.[dbo].[SystemAuditTable]

And the template for the index;

```
"SystemAuditTable":{  
  "_all":{  
    "enabled":false
  },
  "properties":{  
    "@timestamp":{  
      "type":"date"
    },
    "@version":{  
      "type":"text"
    },
    "type":{  
      "type":"text",
      "index":true
    },
    "id":{  
      "type":"long",
      "index":true
    },
    "revisionstamp":{  
      "type":"date",
      "index":true
    },
    "systemaudittype":{  
      "type":"text",
      "index":true
    },
    "action":{  
      "type":"text",
      "index":true
    },
    "ipaddress":{  
      "type":"text",
      "index":false
    },
    "sectionid":{  
      "type":"text",
      "index":true
    },
    "userid":{  
      "type":"text",
      "index":true
    },
    "patientid":{  
      "type":"text",
      "index":true
    },
    "treatmentid":{  
      "type":"text",
      "index":true
    },
    "encryptedpatient":{  
      "type":"binary",
      "store":true,
      "index":"false"
    }
  }
}

```

}

Thanks

Mark

---

<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 12, 2017, 12:16pm UTC](https://discuss.elastic.co/t/binary-conversion-error-using-logstash-to-elasticsearch/71368/2 "2017-01-12T12:16:51Z")

</div>

JSON does not allow raw binary data in fields, so if your field contains binary data I am not surprised it is causing an error. I think the usual way to get around this, if it has to be stored, is to transform it into a string using base64 encoding. This is the method used e.g. by the old mapper attachment plugin.

---

<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:** [February 9, 2017, 12:16pm UTC](https://discuss.elastic.co/t/binary-conversion-error-using-logstash-to-elasticsearch/71368/3 "2017-02-09T12:16:54Z")

</div>

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