[
https://issues.apache.org/jira/browse/NIFI-5612?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16624178#comment-16624178
]
Colin Dean edited comment on NIFI-5612 at 9/21/18 9:06 PM:
-----------------------------------------------------------
I'm 90% confident that I've deduced the problem to be with fields that are
{{int\(x) unsigned default '0'}}, where `x in (1,2)`. Some are NOT NULL.
I distilled the SQL schema of the database we’re crawling down to just the
eight tables we seemingly cannot retrieve because of the Avro type error.
I then filtered out any column from that schema that isn’t a smallint, tinyint,
or int with a precision less than 9:
{code}
cat [email protected] | \
grep -v varchar | \
grep -v enum | \
grep -v datetime | \
grep -v KEY | \
grep -v text | \
grep -v date | \
grep -v time | \
grep -v char\( | \
grep -v decimal | \
grep -v double\( | \
grep -v int\(11 | \
grep -v int\(16 | \
grep -v int\(10 > int-columns-only.sql
{code}
(I'm sure there was a more efficient way to do that. I don't have access to the
database right now.)
I went down the list of columns, searching for each column’s type in its
entirety, e.g. {{int(4) NOT NULL default '0'}} and noting the number of hits in
the whole SQL schema.
Any time that number was really low – under 30 – I can more reasonably look at
each search hit, look at its table, and determine if that table is in the
whitelist. I eventually tossed this and started searching regardless of hits
after a full pass at all under 50.
Whitelist? We're crawling databases using a whitelist of tables but the SQL
schema dump we have is from the whole database. Yikes, I know. 2.2 MB of
schema!
* If the type is found in a table is in our whitelist _and_ was crawled
successfully, then I strike it out because *that type works fine*.
* If the type is found in a table is in the whitelist _and_ was not crawled
successfully, I continue on.
* If the type isn’t found in a table that succeeded or only found in tables
that _didn’t succeed_, I mark it as suspect and move to the next type to
search. I'd mark other fields in the eight tables so I didn't duplicate a
search.
Three outcomes of each check:
1. Type not suspect, found in a working table.
2. Type suspect because it was not found in a working table.
3. Type already examined.
The dead giveaway was that one table had only five relevant fields, only one of
which was _not_ found in a working table.
Each table that isn't working has:
A, B, G, H - {{int(1) unsigned NOT NULL default '0'}}
C - {{int(1) unsigned default '0'}}
D, F - {{int(2) unsigned NOT NULL default '0'}}
E - {{int(2) unsigned NOT NULL default '1'}}
B also has {{int(6) unsigned NOT NULL default 'x'}}, where {{x in (0,1)}}. Both
of these types were only used in tables that didn't work.
My next step will be to run ExecuteSQL against a database with a table with
columns of just these types to see what happens.
was (Author: colindean):
I'm 90% confident that I've deduced the problem to be with fields that are
{{int(x) unsigned default '0'}}, where `x in (1,2)`. Some are NOT NULL.
I distilled the SQL schema of the database we’re crawling down to just the
eight tables we seemingly cannot retrieve because of the Avro type error.
I then filtered out any column from that schema that isn’t a smallint, tinyint,
or int with a precision less than 9:
{code}
cat [email protected] | \
grep -v varchar | \
grep -v enum | \
grep -v datetime | \
grep -v KEY | \
grep -v text | \
grep -v date | \
grep -v time | \
grep -v char\( | \
grep -v decimal | \
grep -v double\( | \
grep -v int\(11 | \
grep -v int\(16 | \
grep -v int\(10 > int-columns-only.sql
{code}
(I'm sure there was a more efficient way to do that. I don't have access to the
database right now.)
I went down the list of columns, searching for each column’s type in its
entirety, e.g. {{int(4) NOT NULL default '0'}} and noting the number of hits in
the whole SQL schema.
Any time that number was really low – under 30 – I can more reasonably look at
each search hit, look at its table, and determine if that table is in the
whitelist. I eventually tossed this and started searching regardless of hits
after a full pass at all under 50.
Whitelist? We're crawling databases using a whitelist of tables but the SQL
schema dump we have is from the whole database. Yikes, I know. 2.2 MB of
schema!
* If the type is found in a table is in our whitelist _and_ was crawled
successfully, then I strike it out because *that type works fine*.
* If the type is found in a table is in the whitelist _and_ was not crawled
successfully, I continue on.
* If the type isn’t found in a table that succeeded or only found in tables
that _didn’t succeed_, I mark it as suspect and move to the next type to
search. I'd mark other fields in the eight tables so I didn't duplicate a
search.
Three outcomes of each check:
1. Type not suspect, found in a working table.
2. Type suspect because it was not found in a working table.
3. Type already examined.
The dead giveaway was that one table had only five relevant fields, only one of
which was _not_ found in a working table.
Each table that isn't working has:
A, B, G, H - {{int(1) unsigned NOT NULL default '0'}}
C - {{int(1) unsigned default '0'}}
D, F - {{int(2) unsigned NOT NULL default '0'}}
E - {{int(2) unsigned NOT NULL default '1'}}
B also has {{int(6) unsigned NOT NULL default 'x'}}, where {{x in (0,1)}}. Both
of these types were only used in tables that didn't work.
My next step will be to run ExecuteSQL against a database with a table with
columns of just these types to see what happens.
> org.apache.avro.UnresolvedUnionException: Not in union ["null","int"]: 0
> ------------------------------------------------------------------------
>
> Key: NIFI-5612
> URL: https://issues.apache.org/jira/browse/NIFI-5612
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
> Affects Versions: 1.5.0, 1.6.0, 1.7.1
> Environment: Microsoft Windows, MySQL Enterprise 5.0.80
> Reporter: Colin Dean
> Priority: Major
> Labels: ExecuteSQL, avro, nifi
>
> I'm seeing this when I execute {{SELECT * FROM <tablename>}} on a few tables
> but not on dozens of others in the same database.
> {code}
> 2018-09-13 01:11:31,434 WARN [Timer-Driven Process Thread-8]
> o.a.n.controller.tasks.ConnectableTask Administratively Yielding
> ExecuteSQL[id=cf5c0996-eddf-3e05-25a3-c407c5edf990] due to uncaught
> Exception: org.apache.avro.file.DataFileWriter$AppendWriteException:
> org.apache.avro.UnresolvedUnionException: Not in union ["null","int"]: 0
> org.apache.avro.file.DataFileWriter$AppendWriteException:
> org.apache.avro.UnresolvedUnionException: Not in union ["null","int"]: 0
> at org.apache.avro.file.DataFileWriter.append(DataFileWriter.java:308)
> at
> org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:462)
> at
> org.apache.nifi.processors.standard.ExecuteSQL.lambda$onTrigger$1(ExecuteSQL.java:252)
> at
> org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2625)
> at
> org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.java:242)
> at
> org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165)
> at
> org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203)
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.apache.avro.UnresolvedUnionException: Not in union
> ["null","int"]: 0
> at
> org.apache.avro.generic.GenericData.resolveUnion(GenericData.java:709)
> at
> org.apache.avro.generic.GenericDatumWriter.resolveUnion(GenericDatumWriter.java:192)
> at
> org.apache.avro.generic.GenericDatumWriter.writeWithoutConversion(GenericDatumWriter.java:110)
> at
> org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:73)
> at
> org.apache.avro.generic.GenericDatumWriter.writeField(GenericDatumWriter.java:153)
> at
> org.apache.avro.generic.GenericDatumWriter.writeRecord(GenericDatumWriter.java:143)
> at
> org.apache.avro.generic.GenericDatumWriter.writeWithoutConversion(GenericDatumWriter.java:105)
> at
> org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:73)
> at
> org.apache.avro.generic.GenericDatumWriter.write(GenericDatumWriter.java:60)
> at org.apache.avro.file.DataFileWriter.append(DataFileWriter.java:302)
> ... 15 common frames omitted
> {code}
> I don't know if I can share the database schema – still working with my team
> on that – but looking at it, I think it has something to do with the
> signedness of int(1) or tinyint(1) because those two are the only numerical
> types common to all of the table.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
