You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "Vos, Walter" <wa...@ns.nl> on 2018/08/08 12:55:16 UTC
PutDatabaseRecord runs out of connections
Hi,
I'm an application admin for NiFi (among others) within my company. NiFi is very new to me and to my colleagues as well.
We're running into an issue with one of our flows where a PutDatabaseRecord processor halts with the following error: " Cannot get a connection, pool error Timeout waiting for idle object". Writing is done to a SQL Server database.
The flow executes daily, and this doesn't happen every time. Currently when this happens, we disable the controller service and re-enable it. That gets the data through.
I was wondering if somebody here could shed some light on possible solutions or on where to investigate further. So far I've read a couple of things:
* I could increase the max wait time for the controller service
* I could increase the max total connections for the controller service
* I could use a different driver. I've read suggestions that com.microsoft.sqlserver.jdbc.SQLServerDriver is buggy and the jTDS driver (http://jtds.sourceforge.net/index.html ) should be used instead
* I've also read that this error could occur if connections are used but not closed. I'm expecting the processor to take care of this, but developers are only human too, so.... :)
How could I debug this situation and do you have any thoughts on the above? What could the side effects of changing those controller service settings? Will a different driver help? Or how could I check whether the processor closes its connections?
Kind regards,
Walter
________________________________
Deze e-mail, inclusief eventuele bijlagen, is uitsluitend bestemd voor (gebruik door) de geadresseerde. De e-mail kan persoonlijke of vertrouwelijke informatie bevatten. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van (de inhoud van) deze e-mail (en eventuele bijlagen) aan derden is uitdrukkelijk niet toegestaan. Indien u niet de bedoelde geadresseerde bent, wordt u vriendelijk verzocht degene die de e-mail verzond hiervan direct op de hoogte te brengen en de e-mail (en eventuele bijlagen) te vernietigen.
Informatie vennootschap<http://www.ns.nl/emaildisclaimer>
RE: PutDatabaseRecord runs out of connections
Posted by "Vos, Walter" <wa...@ns.nl>.
Hi Carlos,
Thanks so much for your reply. I hadn't considered nor encountered that option yet. I've implemented it today, so we'll see what happens tomorrow. Unfortunately there is no way for us to reproduce the error scenario so I can't really test it in that sense. We'll know it didn't work if the error occurs again and otherwise we'll slowly forget it happened in the first place ;-)
Kind regards,
Walter
Van: Carlos Manuel Fernandes (DSI) [mailto:carlos.antonio.fernandes@cgd.pt]
Verzonden: woensdag 8 augustus 2018 15:44
Aan: users@nifi.apache.org
Onderwerp: RE: PutDatabaseRecord runs out of connections
I walter,
try setting validation query property on database controller service. Probably in you installation there is some firewall which drops idle connections. With this property set (ex: select 1 for Sqlserver) if the processor get an invalid connection that connection is dropped and a new one is generated.
I hope this help.
Carlos
From: Vos, Walter [mailto:walter.vos@ns.nl]
Sent: quarta-feira, 8 de agosto de 2018 13:55
To: 'users@nifi.apache.org'
Subject: PutDatabaseRecord runs out of connections
Hi,
I'm an application admin for NiFi (among others) within my company. NiFi is very new to me and to my colleagues as well.
We're running into an issue with one of our flows where a PutDatabaseRecord processor halts with the following error: " Cannot get a connection, pool error Timeout waiting for idle object". Writing is done to a SQL Server database.
The flow executes daily, and this doesn't happen every time. Currently when this happens, we disable the controller service and re-enable it. That gets the data through.
I was wondering if somebody here could shed some light on possible solutions or on where to investigate further. So far I've read a couple of things:
* I could increase the max wait time for the controller service
* I could increase the max total connections for the controller service
* I could use a different driver. I've read suggestions that com.microsoft.sqlserver.jdbc.SQLServerDriver is buggy and the jTDS driver (http://jtds.sourceforge.net/index.html ) should be used instead
* I've also read that this error could occur if connections are used but not closed. I'm expecting the processor to take care of this, but developers are only human too, so.... :)
How could I debug this situation and do you have any thoughts on the above? What could the side effects of changing those controller service settings? Will a different driver help? Or how could I check whether the processor closes its connections?
Kind regards,
Walter
________________________________
Deze e-mail, inclusief eventuele bijlagen, is uitsluitend bestemd voor (gebruik door) de geadresseerde. De e-mail kan persoonlijke of vertrouwelijke informatie bevatten. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van (de inhoud van) deze e-mail (en eventuele bijlagen) aan derden is uitdrukkelijk niet toegestaan. Indien u niet de bedoelde geadresseerde bent, wordt u vriendelijk verzocht degene die de e-mail verzond hiervan direct op de hoogte te brengen en de e-mail (en eventuele bijlagen) te vernietigen.
Informatie vennootschap<http://www.ns.nl/emaildisclaimer>
________________________________
Deze e-mail, inclusief eventuele bijlagen, is uitsluitend bestemd voor (gebruik door) de geadresseerde. De e-mail kan persoonlijke of vertrouwelijke informatie bevatten. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van (de inhoud van) deze e-mail (en eventuele bijlagen) aan derden is uitdrukkelijk niet toegestaan. Indien u niet de bedoelde geadresseerde bent, wordt u vriendelijk verzocht degene die de e-mail verzond hiervan direct op de hoogte te brengen en de e-mail (en eventuele bijlagen) te vernietigen.
Informatie vennootschap<http://www.ns.nl/emaildisclaimer>
RE: PutDatabaseRecord runs out of connections
Posted by "Carlos Manuel Fernandes (DSI)" <ca...@cgd.pt>.
I walter,
try setting validation query property on database controller service. Probably in you installation there is some firewall which drops idle connections. With this property set (ex: select 1 for Sqlserver) if the processor get an invalid connection that connection is dropped and a new one is generated.
I hope this help.
Carlos
From: Vos, Walter [mailto:walter.vos@ns.nl]
Sent: quarta-feira, 8 de agosto de 2018 13:55
To: 'users@nifi.apache.org'
Subject: PutDatabaseRecord runs out of connections
Hi,
I'm an application admin for NiFi (among others) within my company. NiFi is very new to me and to my colleagues as well.
We're running into an issue with one of our flows where a PutDatabaseRecord processor halts with the following error: " Cannot get a connection, pool error Timeout waiting for idle object". Writing is done to a SQL Server database.
The flow executes daily, and this doesn't happen every time. Currently when this happens, we disable the controller service and re-enable it. That gets the data through.
I was wondering if somebody here could shed some light on possible solutions or on where to investigate further. So far I've read a couple of things:
* I could increase the max wait time for the controller service
* I could increase the max total connections for the controller service
* I could use a different driver. I've read suggestions that com.microsoft.sqlserver.jdbc.SQLServerDriver is buggy and the jTDS driver (http://jtds.sourceforge.net/index.html ) should be used instead
* I've also read that this error could occur if connections are used but not closed. I'm expecting the processor to take care of this, but developers are only human too, so.... :)
How could I debug this situation and do you have any thoughts on the above? What could the side effects of changing those controller service settings? Will a different driver help? Or how could I check whether the processor closes its connections?
Kind regards,
Walter
________________________________
Deze e-mail, inclusief eventuele bijlagen, is uitsluitend bestemd voor (gebruik door) de geadresseerde. De e-mail kan persoonlijke of vertrouwelijke informatie bevatten. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van (de inhoud van) deze e-mail (en eventuele bijlagen) aan derden is uitdrukkelijk niet toegestaan. Indien u niet de bedoelde geadresseerde bent, wordt u vriendelijk verzocht degene die de e-mail verzond hiervan direct op de hoogte te brengen en de e-mail (en eventuele bijlagen) te vernietigen.
Informatie vennootschap<http://www.ns.nl/emaildisclaimer>