[ 
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16384277#comment-16384277
 ] 

Marcio Sugar edited comment on NIFI-4926 at 3/2/18 11:14 PM:
-------------------------------------------------------------

In QueryDatabaseTable.java, method onTrigger, line 278, a resultSet is created 
but not used to control the while loop two lines below. The resultSet is 
handled to another method, JdbcCommon.convertToAvroStream. which does its job 
and returns the number of rows it used to populate the output Avro file. 
QueryDatabaseTable doesn't use that number to decide when it should break out 
of the while loop and once again calls convertToAvroStream. This time the 
method throws an exception when trying to create the Schema (first line of 
JdbcCommon.convertToAvroStream, line 256), which makes sense considering last 
time rs.next() returned false.

Perhaps this logic works fine with other databases, but since the resultSet was 
created without using try-with-resoruces and I couldn't find any explicit 
resultSet.close(), I'm wondering if the it would be left open or not.

Note: Using resultSet.isAfterLast() may not a good idea. It may be driver-, 
database-, or result-set-type dependent.


was (Author: msugar):
In QueryDatabaseTable.java, method onTrigger, line 278, a resultSet is created 
but not used to control the while loop two lines below. The resultSet is 
handled to another method, JdbcCommon.convertToAvroStream, which is called 
inside a lambda. convertToAvroStream does its job but returns only the number 
of rows it used to populate the output Avro file. QueryDatabaseTable doesn't 
use that number to decide when it should break out of the while loop and once 
again convertToAvroStream is called. This time the latter throws an exception 
when trying to create the Schema (first line of 
JdbcCommon.convertToAvroStream), which makes sense considering the last 
rs.next() returned false.

Perhaps this logic works fine with other databases, but since the resultSet was 
created without using try-with-resoruces and I couldn't find any explicit 
resultSet.close(), I'm wondering if the it would be left open or not.

Note: Using resultSet.isAfterLast() may not a good idea. It may be driver-, 
database-, or result-set-type dependent.

> QueryDatabaseTable throws SqlException after reading entire DB2 table
> ---------------------------------------------------------------------
>
>                 Key: NIFI-4926
>                 URL: https://issues.apache.org/jira/browse/NIFI-4926
>             Project: Apache NiFi
>          Issue Type: Bug
>          Components: Extensions
>    Affects Versions: 1.5.0
>         Environment: ubuntu 16.04
> nifi 1.5.0
> db2 v10.5.0.7
> JDBC driver db2jcc4-10.5.0.6
>            Reporter: Marcio Sugar
>            Priority: Major
>
> I'm trying to replicate a table from one database to another using NiFi. My 
> flow is just a  QueryDatabaseTable connected to a PutDatabaseRecord. The 
> former fails with this SQLException after reading the whole table: 
> {code:java}
> 2018-03-02 15:20:44,688 INFO [NiFi Web Server-2017] 
> o.a.n.c.s.StandardProcessScheduler Starting 
> QueryDatabaseTable[id=e83d9370-0161-1000-d7d6-702ae791aaf1]
> 2018-03-02 15:20:44,692 INFO [StandardProcessScheduler Thread-2] 
> o.a.n.c.s.TimerDrivenSchedulingAgent Scheduled 
> QueryDatabaseTable[id=e83d9370-0161-1000-d7d6-702ae791aaf1] to run with 1 
> threads
> 2018-03-02 15:20:44,692 DEBUG [Timer-Driven Process Thread-2] 
> o.a.n.p.standard.QueryDatabaseTable 
> QueryDatabaseTable[id=e83d9370-0161-1000-d7d6-702ae791aaf1] Returning CLUSTER 
> State: StandardStateMap[version=54, values={}]
> 2018-03-02 15:20:44,693 DEBUG [Timer-Driven Process Thread-2] 
> o.a.n.p.standard.QueryDatabaseTable 
> QueryDatabaseTable[id=e83d9370-0161-1000-d7d6-702ae791aaf1] Executing query 
> SELECT * FROM FXSCHEMA.USER
> 2018-03-02 15:20:45,159 INFO [Flow Service Tasks Thread-1] 
> o.a.nifi.controller.StandardFlowService Saved flow controller 
> org.apache.nifi.controller.FlowController@77b729c4 // Another save pending = 
> false
> 2018-03-02 15:21:41,577 INFO [Timer-Driven Process Thread-2] 
> o.a.n.p.standard.QueryDatabaseTable 
> QueryDatabaseTable[id=e83d9370-0161-1000-d7d6-702ae791aaf1] 
> StandardFlowFileRecord[uuid=fc5e66c0-14ef-4ed5-8d84-7c4d582000b7,claim=StandardContentClaim
>  [resourceClaim=StandardResourceClaim[id=1520022044698-4, container=default, 
> section=4], offset=0, 
> length=222061615],offset=0,name=264583001281149,size=222061615] contains 
> 652026 Avro records; transferring to 'success'
> 2018-03-02 15:21:41,578 ERROR [Timer-Driven Process Thread-2] 
> o.a.n.p.standard.QueryDatabaseTable 
> QueryDatabaseTable[id=e83d9370-0161-1000-d7d6-702ae791aaf1] Unable to execute 
> SQL select query SELECT * FROM FXSCHEMA.USER due to 
> org.apache.nifi.processor.exception.ProcessException: Error during database 
> query or conversion of records to Avro.: {}
> org.apache.nifi.processor.exception.ProcessException: Error during database 
> query or conversion of records to Avro.
> at 
> org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$0(QueryDatabaseTable.java:291)
> at 
> org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2571)
> at 
> org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:285)
> at 
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1122)
> at 
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:147)
> at 
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
> at 
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:128)
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
> at 
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
> at 
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
> at 
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at 
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.19.26] 
> Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
> at com.ibm.db2.jcc.am.kd.a(Unknown Source)
> at com.ibm.db2.jcc.am.kd.a(Unknown Source)
> at com.ibm.db2.jcc.am.kd.a(Unknown Source)
> at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(Unknown Source)
> at com.ibm.db2.jcc.am.ResultSet.getMetaDataX(Unknown Source)
> at com.ibm.db2.jcc.am.ResultSet.getMetaData(Unknown Source)
> at 
> org.apache.commons.dbcp.DelegatingResultSet.getMetaData(DelegatingResultSet.java:322)
> at 
> org.apache.commons.dbcp.DelegatingResultSet.getMetaData(DelegatingResultSet.java:322)
> at 
> org.apache.nifi.processors.standard.util.JdbcCommon.createSchema(JdbcCommon.java:452)
> at 
> org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream(JdbcCommon.java:256)
> at 
> org.apache.nifi.processors.standard.QueryDatabaseTable.lambda$onTrigger$0(QueryDatabaseTable.java:289)
> ... 13 common frames omitted
> {code}
> According to [DB2's 
> documentation|http://www-01.ibm.com/support/docview.wss?uid=swg21461670], 
> this particular exception could be avoided by adding this setting (semicolon 
> included) to the JDBC connection URL:
> {code:java}
> allowNextOnExhaustedResultSet=1;{code}
> But it didn't make a difference.
> I also tried to set the 'Fetch Size' and 'Max Rows Per Flow File' to 
> different values, to no avail.
> Setting the 'Additional WHERE clause' to get just a small number of rows 
> didn't help either.
> It looks like the connection is been prematurely closed by the processor.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to