nifi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Bende (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (NIFI-1319) ExecuteSQL error when table uses unsigned int
Date Tue, 22 Dec 2015 15:23:46 GMT

    [ https://issues.apache.org/jira/browse/NIFI-1319?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15068245#comment-15068245
] 

Bryan Bende commented on NIFI-1319:
-----------------------------------

Also tested this against Postgres and SQLServer, both don't support unsigned ints, but calling
isSigned appears to be safe and both return true so int fields works as they did before.

> ExecuteSQL error when table uses unsigned int
> ---------------------------------------------
>
>                 Key: NIFI-1319
>                 URL: https://issues.apache.org/jira/browse/NIFI-1319
>             Project: Apache NiFi
>          Issue Type: Bug
>    Affects Versions: 0.4.0
>            Reporter: Bryan Bende
>            Assignee: Bryan Bende
>            Priority: Minor
>             Fix For: 0.5.0
>
>         Attachments: NIFI-1319.patch
>
>
> When a table has a column of type "unsigned int", the JDBC type of this column is 4 which
is INTEGER. When we create the Avro schema for the result set, we end up creating UNION:["int","null"]
for this field.
> When we process a row from the result set, the Java type for the value of this column
is Long, and when trying to write that Avro record it expects and int and produces the following
exception:
> ExecuteSQL[id=e7cc632a-4d8f-4362-b5e0-68faefd01c23] ExecuteSQL[id=e7cc632a-4d8f-4362-b5e0-68faefd01c23]
failed to process session due to org.apache.avro.file.DataFileWriter$AppendWriteException:
org.apache.avro.UnresolvedUnionException: Not in union ["null","int"]: 1: org.apache.avro.file.DataFileWriter$AppendWriteException:
org.apache.avro.UnresolvedUnionException: Not in union ["null","int"]: 1
> This problem was observed with MySQL and NiFi 0.4.0. To reproduce, create table:
> {code}
> CREATE TABLE SALARIES (
> ID int(10) unsigned primary key AUTO_INCREMENT,
> ZIPCODE int,
> SALARY double,
> AGE int,
> GENDER varchar(255)
> );
> INSERT INTO SALARIES (ZIPCODE, SALARY, AGE, GENDER) VALUES (12345, 100, 30, 'M');
> INSERT INTO SALARIES (ZIPCODE, SALARY, AGE, GENDER) VALUES (12345, 200, 31, 'M');
> I
> {code}
> Then create a flow with ExecuteSql -> ConvertAvroToJson -> LogAttribute and use
a query of "select * from salaries;".
> The problem did not happen testing against 0.3.0. At first glance it appears that fixes
in NIFI-972 may have introduced this regression problem. Prior to that we were not using the
union approach in the Avro schema which may have allowed this to work.
> We most likely need to factor in ResultSetMetadata.isSigned() when determining the type
to use in the Avro schema.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message