DataSourcePool Connection Issues

[mark_mcintyre]

Greetings,

We've been experiencing some issues using CQ's DataSourcePool connecting to a MySQL database. Specifically, it will sometimes return an invalid connection (one that throws a CommunicationsException when it's attempted to be used), and over time the pool appears to run out of valid connections entirely (throwing a org.apache.commons.dbcp.SQLNestedException with the message: "Cannot get a connection, pool error Timeout waiting for idle object.").

I notice that the examples provided on http://dev.day.com/docs/en/cq/current/developing/jdbc.html and http://forums.adobe.com/community/digital_marketing_suite/cq5 don't close connections retrieved from the pool. With most pool implementations, connections from the pool should be closed to help inform the pool manager when you're done with an active connection. Is this not how the CQ DataSourcePool works?

Is there anything else I could be missing as well?

Any help would be greatly appreciated.

[smacdonald2008 · Accepted solution]

See this community article -- it walks you through (step by step) creating a DataSourcePool and injecting it into an OSGi bundle:

http://scottsdigitalcommunity.blogspot.ca/2013/09/injecting-datasourcepool-service-into.html

As you can see, the connection that uses the DataSourcePool is closed when the code is done using it. 

HTH

[mark_mcintyre]

Thanks for the quick reply.

I'm not having issues injecting the DataSourcePool per se; I am able to configure and use the DataSourcePool initially without noticable issues. However, after some time (perhaps a few hours) the connections in the pool become invalid, until eventually the only response from the pool is timeout exceptions.

Is there anything else I can do? Would you happen to know if I should be closing the pool connections or not?

[smacdonald2008]

After you call into the connection (that is based on the DataSourcePool) -- and use it to perform database operations, close the connection. If you keep them open - it causes issues. 

[mark_mcintyre]

Thanks for the confirmation.

I do, indeed, close the connections (it's the examples posted on day.com that don't, so I was unsure if I should be leaving them open instead), so I'm happy to hear this likely isn't the problem. However, now I'm not sure what might be causing invalid connections to remain in the pool.

Do you have any other advice?

[smacdonald2008]

I am going to check to make sure that there is no known issues with this. I have not heard of any - i will update this thread on Tues. However -- this should not be happening. 

[smacdonald2008]

I have not heard of any issues. If this behaviour continues - please consider opening a ticket at:

http://helpx.adobe.com/marketing-cloud/experience-manager.html

Thanks

[rohitn62196663]

Hello all,

I have a similar problem with day commons connections pool. After some time the connections to the data base are getting timed out. Is it an issue with the connections pool iotself. Any solution for this. Below is the error I got.

SQL exception while storing data in table java.sql.SQLRecoverableException: IO Error: Connection timed out

I even added the validation query i.e. "select 1 from dual" but still its throwing the connection time out errors.

[smacdonald2008]

If the Connection Pool is timing out -- try writing  your own Java connection. See if that works better - see: 

http://scottsdigitalcommunity.blogspot.ca/2013/08/querying-and-persisting-adobe-aem-data.html

PS - in future - start a new thread as opposed to hooking into a solved green one - you will get better responses. 

[rohitn62196663]

Thanks for the reply !!!

I reported the issue in a separate link too but didn't get any reply. Below is the link:

http://help-forums.adobe.com/content/adobeforums/en/experience-manager-forum/adobe-experience-manage...
 

I would like to know whether its a known issue with connections pool.