[ 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 problem_tab...@mack-649.sql | \ 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 problem_tab...@mack-649.sql | \ 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)