[
https://issues.apache.org/jira/browse/NIFI-8249?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17289362#comment-17289362
]
David commented on NIFI-8249:
-----------------------------
I've spent a long time browsing the Nifi code trying to find where the
"transfer relationship not specified" error is being printed from. I think I
remember seeing the message somewhere in a checkpoint() method for the process
session? Could there be some sort of checkpointing setting for Nifi that could
be in play?
This is my first project working with Nifi, so I am not terribly familiar with
all its components.
Unfortunate that you're unable to reproduce, the test flow I sent the
screenshot for is still running and throwing the error over and over. I'm not
sure what other settings could be in play.
Re: your previous comment: we did consider the high # of fetches to our source
DB. I ran probably close to 100 permutations of these settings in our
performance environment trying to determine the combination that produced the
fastest end-to-end flow speed.
I ran another performance test just now with 100k partition, 10k fetch and 10k
max rows/flowfile and our end-to-end speed for extracting 18M records was ~21
minutes, vs ~14 minutes with 100k partition, 100 fetch, 100 max rows/flowfile.
In our case the extra round-trips to the source database is worth it. We never
see any backup in our downstream processors, the slowest piece always seems to
be extracting the data from the source table.
> ExecuteSQLRecord "administratively yields" and rollls back session when fetch
> size < query partition size and an SQLException is thrown on subsequent next()
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: NIFI-8249
> URL: https://issues.apache.org/jira/browse/NIFI-8249
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
> Affects Versions: 1.11.4
> Reporter: David
> Assignee: Matt Burgess
> Priority: Major
> Attachments: ExecuteSQLRecord.png, GenerateTableFetch.png, Screen
> Shot 2021-02-23 at 3.00.47 PM.png, Screen Shot 2021-02-23 at 3.04.55 PM.png
>
>
> When ExecuteSQLRecord executes a query with a partition size > the fetch size
> and one of the fetches throws an SQLException, the exception appears to get
> "swallowed" and a more general "IOException: Could not obtain next record
> from ResultSet: routing to failure" in addition to a "transfer relationship
> not specified;Processor Administratively Yielded for 1 sec" errors are thrown.
> The problematic query/flowfile gets put back into the incoming queue and will
> continue to fail indefinitely until it is manually removed from the queue.
> If the fetch size >= to the query partition size, the exact SQL error message
> is thrown and the flowfile gets routed to the failure queue.
>
> Steps to reliably reproduce:
> # Create an DB table with an ID column (for partitioning queries against)
> and a VARCHAR column
> # Insert a row where the VARCHAR column data is a numeric value (ie '123')
> # Insert a row where the VARCHAR column data is a string value (ie 'abc')
> # Create a GenerateTableFetch processor where the "columns to return"
> includes TO_NUMBER(<varchar column>)
> # Create a ExecuteSQLRecord processor that accepts the "success" connection
> from GenerateTableFetch
> # Set ExecuteSQLRecord fetch size to 1
> # Run flow
> What should happen:
> * ExecuteSQLRecord should throw an java.sql.SQLSyntaxErrorException:
> ORA-01722: invalid number
> * Flowfile should get transferred to failure queue
> What happens:
> * Nifi throws one error and prints another, then puts the flowfile back into
> the incoming queue
> ** ExecuteSQLRecord throws "Unable to execute SQL select query <query> ...
> due to java.io.IOException: java.io.IOException: Could not obtain next record
> from ResultSet"
> ** Nifi prints errror "transfer relationship not specified; Processor
> Administratively Yielded for 1 sec"
> ** Flowfile is kept in current queue and penalized and re-processed again
> after penalty duration expires
> Thus, the problematic query will get executed indefinitely.
>
> Note: I believe the issue could be due to ResultSet .next() being called
> first in the constructor to ResultSetRecordSetWithCallback
> Subsequent .next() is called from resultSetWriter.write(recordSet)
> [https://github.com/apache/nifi/blob/aa741cc5967f62c3c38c2a47e712b7faa6fe19ff/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/sql/RecordSqlWriter.java#L73|https://github.com/apache/nifi/blob/aa741cc5967f62c3c38c2a47e712b7faa6fe19ff/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/sql/RecordSqlWriter.java#L73]
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
