[ https://issues.apache.org/jira/browse/NIFI-8249?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David updated NIFI-8249: ------------------------ Summary: ExecuteSQLRecord "administratively yields" and rollls back session when fetch size < query partition size and an SQLException is thrown on subsequent next() (was: ExecuteSQLRecord "administratively yields" when fetch size < query partition size and an SQLException is thrown) > 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 > 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 > # Make sure SQLException is thrown in second or later calls to > ResultSet.next() > # Run flow > What should happen: > * ExecuteSQLRecord should throw an SQLException: ORA-22835: Buffer too small > for CLOB to CHAR or BLOB to RAW conversion > * 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. -- This message was sent by Atlassian Jira (v8.3.4#803005)