Additional configuration properties for DBCPConnectionPool

[Tim Zimmerman <ic...@hotmail.com>]

Without going into too much detail (unless it would be useful). I am facing
an issue where long standing connections are eventually failing when
SQLServer attempts to bulk insert a file from NAS (we are using
integratedSecurity and the SQLServer is configured with kerberos
authentication and delegation). The process does work but at some point the
bulk insert is denied access to the file and it appears to be an issue with
the kerberos ticket becoming stale. 

The solution seems to be to time out connections that have been idle for
some time and allow the pool to reduce to zero connections. This forces a
new connection and authorization when needed. The connection seems to work
for as long as needed and after it is idle for some time it is removed from
the pool.

These additional configurations are available in the underlying
DBCPConnectionPool but are not exposed through the service configuration. Is
there a specific reason they have not been exposed? I have downloaded the 
source and made the changes myself but before I go any further I would like
to make sure I am not being too clever for my own good.

Also, if there is not a specific reason they have not been exposed, would
this be something that would be considered for a future release?



--
Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/

Re: Additional configuration properties for DBCPConnectionPool

[Tim Zimmerman <ic...@hotmail.com>]

Matt,
  Thank you, and I apologize , I forgot to provide the version number. I am
using 1.5.0. I have modified the DBCPConnectionPool to expose a few
properties that I think will help.

  I will create an Improvement Jira after I confirm the changes that I made
truly help me resolve the issue.



--
Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/

Re: Additional configuration properties for DBCPConnectionPool

[Matt Burgess <ma...@apache.org>]

Tim,

We can certainly expose some DBCP properties as processor properties,
we'd have to enumerate them explicitly since the user-defined ones are
used for the connection. Please feel free to write up an Improvement
Jira [1] to cover whichever properties you'd like to see added to
DBCPConnectionPool.

Also, we've had some issues with Kerberos ticket expiration that were
addressed in NiFi 1.5.0 [2], not sure if that would help you out or
not.

Regards,
Matt

[1] https://issues.apache.org/jira/projects/NIFI
[2] https://issues.apache.org/jira/browse/NIFI-3472

On Fri, Feb 23, 2018 at 5:38 PM, Tim Zimmerman <ic...@hotmail.com> wrote:
> Matt,
>   Thank you for the quick response. I do not think these are connection
> properties but properties of the connection pool. The properties I am
> looking to set are the BasicDataSource Configuration parameters such as
> minIdle and minEvictableIdleTimeMillis.
>
>   I do use the validation query but the problem is the connection to the
> database is still valid while, I may get this wrong but in layman's terms,
> the kerberos authentication for the user who has connected is "stale". The
> result is, NiFi is able to query the database, create temp tables etc but
> when it attempts to perform the bulk insert of a file on a third (NAS)
> server it fails. I suppose my validation query could be an attempt at some
> bulk insert but that seems a little inefficient.
>
>   Right now I have a test set up with four connection pools and four flows.
> Two ran for some time, maybe a week but are now continually failing. The
> other two continue to work. The difference is that the two that continue to
> work have either had the pool reduced to zero at some point due to idle
> connections being closed or the connections were never allowed to idle i.e.
> executing the bulk insert every 10 seconds.
>
>   The two that are failing were allowed to maintain a minimum of one
> connection and ran less frequently. One runs every three hours the other
> once a week. While the bulk insert for these fails, querying the database
> and creating/dropping tables continues to succeed.
>
>
>
> --
> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/

Re: Additional configuration properties for DBCPConnectionPool

[Tim Zimmerman <ic...@hotmail.com>]

Matt,
  Thank you for the quick response. I do not think these are connection
properties but properties of the connection pool. The properties I am
looking to set are the BasicDataSource Configuration parameters such as
minIdle and minEvictableIdleTimeMillis.

  I do use the validation query but the problem is the connection to the
database is still valid while, I may get this wrong but in layman's terms,
the kerberos authentication for the user who has connected is "stale". The
result is, NiFi is able to query the database, create temp tables etc but
when it attempts to perform the bulk insert of a file on a third (NAS)
server it fails. I suppose my validation query could be an attempt at some
bulk insert but that seems a little inefficient.

  Right now I have a test set up with four connection pools and four flows.
Two ran for some time, maybe a week but are now continually failing. The
other two continue to work. The difference is that the two that continue to
work have either had the pool reduced to zero at some point due to idle
connections being closed or the connections were never allowed to idle i.e.
executing the bulk insert every 10 seconds.

  The two that are failing were allowed to maintain a minimum of one
connection and ran less frequently. One runs every three hours the other
once a week. While the bulk insert for these fails, querying the database
and creating/dropping tables continues to succeed.



--
Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/

Re: Additional configuration properties for DBCPConnectionPool

[Matt Burgess <ma...@apache.org>]

Tim,

What version of NiFi are you using? As of 1.1.0 [1], you can specify a
Validation Query on the DBCPConnectionPool, this is used by DBCP to
validate that a connection is "good" before offering it to the client.
For idle/timed-out connections, the validation query should fail and
DBCP should attempt to get a new connection.

For the additional configuration, if a property cannot be added via
the JDBC URL, then it can be added as a user-defined property (as of
NiFi 1.2.0 [2]), the code calls dataSource.addConnectionProperty() for
each of the supplied key/value pairs. Please see the processor doc [3]
for more details.

Regards,
Matt

[1] https://issues.apache.org/jira/browse/NIFI-2381
[2] https://issues.apache.org/jira/browse/NIFI-3426
[3] https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-dbcp-service-nar/1.5.0/org.apache.nifi.dbcp.DBCPConnectionPool/index.html

On Fri, Feb 23, 2018 at 4:00 PM, Tim Zimmerman <ic...@hotmail.com> wrote:
> Without going into too much detail (unless it would be useful). I am facing
> an issue where long standing connections are eventually failing when
> SQLServer attempts to bulk insert a file from NAS (we are using
> integratedSecurity and the SQLServer is configured with kerberos
> authentication and delegation). The process does work but at some point the
> bulk insert is denied access to the file and it appears to be an issue with
> the kerberos ticket becoming stale.
>
> The solution seems to be to time out connections that have been idle for
> some time and allow the pool to reduce to zero connections. This forces a
> new connection and authorization when needed. The connection seems to work
> for as long as needed and after it is idle for some time it is removed from
> the pool.
>
> These additional configurations are available in the underlying
> DBCPConnectionPool but are not exposed through the service configuration. Is
> there a specific reason they have not been exposed? I have downloaded the
> source and made the changes myself but before I go any further I would like
> to make sure I am not being too clever for my own good.
>
> Also, if there is not a specific reason they have not been exposed, would
> this be something that would be considered for a future release?
>
>
>
> --
> Sent from: http://apache-nifi-users-list.2361937.n4.nabble.com/