Reset/Flush JDBC connections pool

[dilip_muthukris]

Hi, is there a way to reset/flush the JDBC connections pool programmatically?  I've configured a connection to a MySQL database via the Apache Felix Web Console and it seems like I can accomplish this by clicking on the "Reset" button in the JDBC Connections Pool dialog.  However, is there a way to do this using the DataSourcePool object (which I have access to in an AEM component via sling.getService(DataSourcePool.class).

The reason why I want to do this is because the connection appears to be getting dropped intermittently with the following error message:

SQL state is 08S01, message is Communication link failure: java.net.SocketException, underlying cause: Connection reset

I've tried playing around with the pool size but it hasn't resolved the issue.  Any help would be much appreciated.  Thanks.

[smacdonald2008]

The DataSourcePool object does not expose a method to let you perform this task. This is the DataSourcePool class def: 

package com.day.commons.datasource.poolservice;

import java.util.Collection;

public abstract interface DataSourcePool
{
  public abstract <AdapterType> AdapterType getDataSource(String paramString, Class<AdapterType> paramClass)
    throws DataSourceNotFoundException, TypeNotAvailableException;
  
  public abstract Object getDataSource(String paramString)
    throws DataSourceNotFoundException;
  
  public abstract Collection<String> getAllJndiDataSourceNames();
}

I will look to see if there are other ways.

[dilip_muthukris]

Thanks.  Yes, I noticed that it doesn't allow me to do it.  I wonder if this should happen on the DataSource level or perhaps the Connection level.  I'm not sure.

[smacdonald2008]

I will check tomorrow if there is a mean operation to perform this task.

[dilip_muthukris]

smacdonald2008 wrote...

I will check tomorrow if there is a mean operation to perform this task.

Thank you.

[dilip_muthukris]

smacdonald2008 wrote...

The DataSourcePool object does not expose a method to let you perform this task. This is the DataSourcePool class def: 

package com.day.commons.datasource.poolservice;

import java.util.Collection;

public abstract interface DataSourcePool
{
  public abstract <AdapterType> AdapterType getDataSource(String paramString, Class<AdapterType> paramClass)
    throws DataSourceNotFoundException, TypeNotAvailableException;
  
  public abstract Object getDataSource(String paramString)
    throws DataSourceNotFoundException;
  
  public abstract Collection<String> getAllJndiDataSourceNames();
}

I will look to see if there are other ways.

So, I tried using the following cURL command from a Unix machine to simulate the "Save" operation on the JDBC Connections Pool configuration window in the web console.  Clicking on Save appears to reset the connection pool.  If I create a cron job on my Unix machine to run this command at regular intervals, would it "fix" this issue?  Also, do I need to include all of these POST parameters in the command?

curl -u admin:admin --data "apply=true&default.autocommit=false&jdbc.connection.uri=jdbc:mysql://<db_server_ip>:<db_port_number>/<db_name>&pool.max.wait.msec=1000&factoryPid=com.day.commons.datasource.jdbcpool.JdbcPoolService&propertylist=jdbc.driver.class,jdbc.connection.uri,jdbc.username,jdbc.password,jdbc.validation.query,default.readonly,default.autocommit,pool.size,pool.max.wait.msec,datasource.name,datasource.svc.properties&jdbc.password=<password>&jdbc.username=<username>&default.readonly=false&jdbc.driver.class=com.mysql.jdbc.Driver&pool.size=5&action=ajaxConfigManager&datasource.name=mysql&datasource.svc.properties=&jdbc.validation.query=" http://<cq5_server_ip>:<cq5_port_number>/system/console/configMgr/com.day.commons.datasource.jdbcpoo...

[smacdonald2008]

Looks like this command would require these arguments. I have never tried this - nor is there any examples on this specific use case. Try this and let us know if it works. It looks you are going in the right direction, 

[dilip_muthukris]

smacdonald2008 wrote...

Looks like this command would require these arguments. I have never tried this - nor is there any examples on this specific use case. Try this and let us know if it works. It looks you are going in the right direction, 

Yes, this seems to work.  However, this would only "truly" work if my assumption that clicking on "Save" in the JDBC Connections Pool dialog will reset the connections is correct.  Even so, this introduces a security issue because the passwords are viewable by those who have access to the machine from which this command is being run.  Ideally, I'm looking for a way to flush the connection pool programmatically or via some configuration on the web console.  Thanks for your help.

[Jörg_Hoh]

Shouldn't a datasource be resilient against this kind of error and recreate connections to the database if required?

Jörg

[dilip_muthukris]

Jörg Hoh wrote...

Shouldn't a datasource be resilient against this kind of error and recreate connections to the database if required?

Jörg

Is there a way to configure this resiliency, either programmatically, or via the web console?

[brackenk1997]

I know this is an old post but just curious if you ever found a solution to this?  We are also running into an issue after updating the username or password and get the following error

[EL Finest]: 2017-10-13 15:54:56.967--reconnecting to external connection pool

[EL Warning]: 2017-10-13 15:54:56.968--java.lang.IllegalStateException: Pool not open.

Stopping and staring the bundle that uses the jdbc pool connection fixes the issue but what we really want is for it to pickup the new user/password on the next connection attempt automatically.

[smacdonald2008]

I have never seen a fix for this after all of this time. I personally restart the bundle when i change this. Ironically - just finished working with a DataSourcePool for a new HTL/Sling Model article -- Scott's Digital Community: Creating a Mail List Component for the Experience Manager Toy Store Site