[
https://issues.apache.org/jira/browse/NIFI-1319?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15069674#comment-15069674
]
Aldrin Piri commented on NIFI-1319:
-----------------------------------
Reviewing.
> 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)
