Uploaded image for project: 'Apache NiFi'
  1. Apache NiFi
  2. NIFI-1409

ExecuteSQL gives transfer error when SQLException occurs with no input flowfile

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.5.0
    • Component/s: None
    • Labels:
      None

      Description

      When the result set from ExecuteSQL has no columns, a bulletin is issued saying a flowfile was not transferred:

      13:35:12 ESTERROR05845c66-73a4-49ff-a973-ad986fb651bf
      ExecuteSQL[id=05845c66-73a4-49ff-a973-ad986fb651bf] Unable to execute SQL select query SELECT * FROM test due to org.apache.nifi.processor.exception.ProcessException: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.. No incoming flow file to route to failure: org.apache.nifi.processor.exception.ProcessException: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
      13:35:12 ESTERROR05845c66-73a4-49ff-a973-ad986fb651bf
      ExecuteSQL[id=05845c66-73a4-49ff-a973-ad986fb651bf] ExecuteSQL[id=05845c66-73a4-49ff-a973-ad986fb651bf] failed to process due to org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=c9077d5c-d873-4bf2-a8c0-4cc92a6f3309,claim=,offset=0,name=258316166470532,size=0] transfer relationship not specified; rolling back session: org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=c9077d5c-d873-4bf2-a8c0-4cc92a6f3309,claim=,offset=0,name=258316166470532,size=0] transfer relationship not specified

      If there is any problem with the query (such as selecting a column that doesn't exist, or SELECT * from a table with no columns), a SQLException is thrown, which becomes a ProcessException in the ExecuteSQL processor. In addition, the exception handling tries to route the flowfile to failure, but for the newly created flowfile (to contain the result set), the reference has since been lost, and thus the flowfile is never transferred.

      At present, if it desired that an empty result set causes a route to failure, then a workaround is to add a GenerateFlowFile processor (with a 0-byte size) in front of the ExecuteSQL processor. This bypasses the logic error because the incoming flowfile is reused and eventually transferred to failure because of the exception thrown.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                mattyb149 Matt Burgess
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: