[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16384277#comment-16384277
]
Marcio Sugar edited comment on NIFI-4926 at 3/3/18 12:13 AM:
-
In {{QueryDatabaseTable.java}}, method {{onTrigger}}, line 278, a JDBC result
set is created but not used to control the _while_ loop two lines below. In
fact, 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.
Because only the number of rows is returned, {{QueryDatabaseTable}} doesn't
know the last {{rs.next()}} was false, which means (at least for the DB2 JDBC
Driver) the result set is now closed. Instead of breaking out of the while
loop, the processor once again calls {{convertToAvroStream}} and a
{{SqlException}} is thrown soon after.
_Notes:_
* Perhaps this logic works fine for other databases, but considering
{{resultSet}} was not created with try-with-resouces and I couldn't find any
explicit {{resultSet.close()}}, it stands to reason we could also have a
resource leakage here.
* Using {{resultSet.isAfterLast()}} may not be a good idea because the support
for that method is optional for {{TYPE_FORWARD_ONLY}} result sets.
* Checking the {{resultSet}} is closed right after entering
{{JdbcCommon.convertToAvroStream}} could work as a quick fix, but it would make
the whole thing even harder to understand and maintain. Maybe some refactoring
would be in order?
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 does its job
and returns the number of rows it used to populate the output Avro file.
Since no other indication the last resultSet.next() returned false (and as a
consequence the resultSet it's now closed) is passed back to
QueryDatabaseTable, so it cannot know 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).
Perhaps this logic works fine with other databases, but considering 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
[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16384277#comment-16384277
]
Marcio Sugar edited comment on NIFI-4926 at 3/2/18 11:36 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.
Since no other indication the last resultSet.next() returned false (and as a
consequence the resultSet it's now closed) is passed back to
QueryDatabaseTable, so it cannot know 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).
Perhaps this logic works fine with other databases, but considering 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 does its job
and returns the number of rows it used to populate the output Avro file.
Since no other indication the last resultSet.next() returned false (and it's
now closed) is passed back to QueryDatabaseTable, so it cannot know 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).
Perhaps this logic works fine with other databases, but considering 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
>
[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16384277#comment-16384277
]
Marcio Sugar edited comment on NIFI-4926 at 3/2/18 11:35 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.
Since no other indication the last resultSet.next() returned false (and it's
now closed) is passed back to QueryDatabaseTable, so it cannot know 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).
Perhaps this logic works fine with other databases, but considering 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 does its job
and returns the number of rows it used to populate the output Avro file but no
other indication the resultSet.next() returned false. It means
QueryDatabaseTable cannot know 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.
> 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
[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16384277#comment-16384277
]
Marcio Sugar edited comment on NIFI-4926 at 3/2/18 11:31 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 but no
other indication the resultSet.next() returned false. It means
QueryDatabaseTable cannot know 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 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.
> 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.: {}
>
[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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.:
[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16384277#comment-16384277
]
Marcio Sugar edited comment on NIFI-4926 at 3/2/18 11:11 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 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.
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.
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
>
[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16384277#comment-16384277
]
Marcio Sugar edited comment on NIFI-4926 at 3/2/18 10:57 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 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.
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.
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.
> 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)
[jira] [Comment Edited] (NIFI-4926) QueryDatabaseTable throws SqlException after reading entire DB2 table
[
https://issues.apache.org/jira/browse/NIFI-4926?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16384277#comment-16384277
]
Marcio Sugar edited comment on NIFI-4926 at 3/2/18 10:54 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 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.
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.
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
consumed by another method, JdbcCommon.convertToAvroStream, which is called
inside a lambda. The convertToAvroStream does its job but returns only the
number of rows it consumed to populate the Avro file, but QueryDatabaseTable
doesn't use that number to decide if it should break out of the while loop or
not.
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 resultSet would be left open or not.
> 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
>
