[
https://issues.apache.org/jira/browse/NIFI-8249?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
David updated NIFI-8249:
------------------------
Description:
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#L71]
was:
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.
> 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
>
> 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#L71]
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
