[
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 Oracle DB table with an ID column (for partitioning queries
against) and a CLOB column
# Insert a row with where the CLOB column data is larger than 4000 characters
# Insert x rows (where x is larger than the below ExecuteSQLRecord's fetch
size) where the CLOB column data is less than 4000 characters
# Create a GenerateTableFetch processor where the "columns to return" includes
TO_CHAR(<clob column>)
# Create a ExecuteSQLRecord processor that accepts the "success" connection
from GenerateTableFetch
# Create some other processor for the success and failure relationships from
ExecuteSQLRecord (this is just to make sure all transfer relationships are
specified)
# Set ExecuteSQLRecord fetch size to some value smaller than the partition
size in GenerateTableFetch
# Run flow
What should happen:
* ExecuteSQLRecord should throw an SQLException: ORA-22835: Buffer too small
for CLOB to CHAR or BLOB to RAW conversion
What happens:
* ExecuteSQLRecord throws two errors and puts the flowfile back into the
incoming queue
** Unable to execute SQL select query <query> ... due to java.io.IOException:
java.io.IOException: Could not obtain next record from ResultSet
** transfer relationship not specified; Processor Administratively Yielded for
1 sec
Thus, the problematic query will get executed indefinitely.
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 Oracle DB table with an ID column (for partitioning queries
against) and a CLOB column
# Insert a row with where the CLOB column data is larger than 4000 characters
# Insert x rows (where x is larger than the below ExecuteSQLRecord's fetch
size) where the CLOB column data is less than 4000 characters
# Create a GenerateTableFetch processor where the "columns to return" includes
TO_CHAR(<clob column>)
# Create a ExecuteSQLRecord processor that accepts the "success" connection
from GenerateTableFetch
# Create some other processor for the success and failure relationships from
ExecuteSQLRecord (this is just to make sure all transfer relationships are
specified)
# Set ExecuteSQLRecord fetch size to some value smaller than the partition
size in GenerateTableFetch
# Run flow
What should happen:
* ExecuteSQLRecord should throw an SQLException: ORA-22835: Buffer too small
for CLOB to CHAR or BLOB to RAW conversion
What happens:
* ExecuteSQLRecord throws two errors and puts the flowfile back into the
incoming queue
** Unable to execute SQL select query <query> ... due to java.io.IOException:
java.io.IOException: Could not obtain next record from ResultSet
** transfer relationship not specified; Processor Administratively Yielded for
1 sec
Thus, the problematic query will get executed indefinitely.
> ExecuteSQLRecord "administratively yields" when fetch size < query partition
> size and an SQLException is thrown
> ---------------------------------------------------------------------------------------------------------------
>
> 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
> 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 Oracle DB table with an ID column (for partitioning queries
> against) and a CLOB column
> # Insert a row with where the CLOB column data is larger than 4000 characters
> # Insert x rows (where x is larger than the below ExecuteSQLRecord's fetch
> size) where the CLOB column data is less than 4000 characters
> # Create a GenerateTableFetch processor where the "columns to return"
> includes TO_CHAR(<clob column>)
> # Create a ExecuteSQLRecord processor that accepts the "success" connection
> from GenerateTableFetch
> # Create some other processor for the success and failure relationships from
> ExecuteSQLRecord (this is just to make sure all transfer relationships are
> specified)
> # Set ExecuteSQLRecord fetch size to some value smaller than the partition
> size in GenerateTableFetch
> # Run flow
> What should happen:
> * ExecuteSQLRecord should throw an SQLException: ORA-22835: Buffer too small
> for CLOB to CHAR or BLOB to RAW conversion
> What happens:
> * ExecuteSQLRecord throws two errors and puts the flowfile back into the
> incoming queue
> ** Unable to execute SQL select query <query> ... due to
> java.io.IOException: java.io.IOException: Could not obtain next record from
> ResultSet
> ** transfer relationship not specified; Processor Administratively Yielded
> for 1 sec
> Thus, the problematic query will get executed indefinitely.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
