Support Questions
Find answers, ask questions, and share your expertise

ConvertJSONToSQL vs eg. mysql decimal(5,4)



I am working with NIFI. there is an scene that export an mysql table to another mysql table, The tables are the same table structure. source table has a field 'a' that is decimal(5,4), target table has a field 'b' that is decimal(5,4) too. The issue was triggered. eg. the value 0.1494 from source, the result was cut off to 0.1490. The root cause seems the below code.

///From ConvertJSONToSQL, the ColumnSize() is precision length ,but the value length is 6, so the subString method change the value to 0.149 .. 
                final Integer colSize = desc.getColumnSize();                
                final JsonNode fieldNode = rootNode.get(fieldName);               
                 if (!fieldNode.isNull()) {                   
                        String fieldValue = fieldNode.asText();                   
                        if (colSize != null && fieldValue.length() > colSize) {                   
                        fieldValue = fieldValue.substring(0, colSize);         
                attributes.put("sql.args." + fieldCount + ".value", fieldValue);                }

Is this a bug?

How to resolve it?


Super Guru

I believe you are running into NIFI-1613. Unfortunately I'm not aware of any workaround at this time.

Hi @Paul Yang

If you have the option to use the latest Apache NiFi, it maybe possible as it provides 'decimal' Avro logical support since NiFi 1.3.0.

By using logical decimal type and PutDatabaseRecord processor, you can preserve decimal precision defined at the source MySQL table.

I created a simple example, currently ExecuteSQL and QueryDatabaseTable can generate Avro logical types.


Template is available here in Gist.

If you're receiving data in JSON format somewhere other than the source MySQL table, then I'd recommend using ConvertRecord processor to convert JSON into Avro with specifying desired Avro schema with decimal field before passing those into PutDatabaseRecord.

Hope this helps,



Thanks @kkawamura , currently, I change the decimal(5,4) to decimal(7,4) to workaround the bug.

It is so big bug.


HI, What is the reason the nifi team did not fix the bug even until the latest nifi version? for ConvertJsonToSql, is this a critical bug? or some other reason?

Hi @Paul Yang

I am not aware of any specific reason to not fix a bug. Unfortunately the original effort to fix the issue has been left inactive until now. I've picked it up again and proposed a fix against the latest Apache NiFi codebase. Hopefully it can be merged soon.

Thanks again for reporting this issue!

; ;