DB Connection idle timeout isn't honored in Lucee 6

Hey folks,

We’re upgrading what’s left of our CFML application to from Lucee 5 → Lucee 6, and we’ve seen a behavior change around database connections.

All of our hosted environments proactively (database-side) close database connections after five minutes (typical best practice for managing RAM utilization), so we’ve long had Lucee configured to consider idle connections eviction candidates after four minutes:

this.datasources['whatever'] = {
        ....some stuff...
        , connectionTimeout: 4
        ...other stuff...
};

That doesn’t look to be doing anything at all in Lucee 6, and we’re running into dead connections.

I believe the cause is in ConfigImpl’s configuration of GenericObjectPoolConfig instances. It looks like Lucee has hard-coded a number of values (minEvictableIdleTimeMillis, timeBetweenEvictionRunsMillis, etc.) to 0, meaning these commonly-needed pooling properties are simply not doing anything.

In other words, there’s at least a bug here (Lucee is using a hard-coded 0 for minEvictableIdleTimeMillis and sets no time between eviction runs, so even with an evictable time, it’d never do anything).

We’ve worked around this by writing our own code that introspects the pools at runtime, reconfiguring them directly, but is there any plan to expose these properties officially? I get that Lucee has to support the web-based configuration forms, but I’d strongly recommend looking at how other Java-based frameworks (Micronaut, Spring Boot) allow these to be set through convention within code.

Having spent the morning with the Lucee source code, I don’t think it’d be hard to allow a similar approach, even if it’s just adding more properties to the DataSource class for these:

this.datasources['whatever'] = {
		...stuff...
		, minIdle: 5,
		, maxIdle: 25,
		, maxWaitMillis: 10000,
		, timeBetweenEvictionRunsMillis: 5000,
		, minEvictableIdleTimeMillis: 60000
		...other stuff...
};

Last, has Lucee considered NOT using commons-pool for database connection pools? If you’re sticking to commons, commons-dbcp is probably more appropriate.

Don’t forget to tell us about your stack!

OS: Docker image 6.0.1.83
Java Version: Docker image 6.0.1.83
Tomcat Version: Docker image 6.0.1.83
Lucee Version: Docker image 6.0.1.83

1 Like

Having same issue in trying to upgrade from Lucee 5 to 6.1.0.243. Keep seeing increasing # of datasource connections and eventually Postgresql complains “remaining connections reserved for superuser”. Only a server restart resolves the issue (temporarily). Using a combo of Hibernate ORM and reqular SQL queries. @Joe_Rinehart Were you able to find any solution / workaround?

Sounds like: [LDEV-4339] - Lucee

1 Like

Any update on this?
We would greatly appreciate the ability to set this idle timeout too as we are noticing high number of idle database connections

Did you try this with 6.2.x? Looks like this bug maybe fixed.

The best way to see an update in an opensource project like Lucee

  1. Report the bug with steps to reproduce the bug
  2. Fix it and make a pull request
  3. Sponsor the bug to be fixed or organization.

Lucee 6 does use commons-pool now

@SammyDeveilleASP please always mention which version of Lucee when asking questions

In Lucee 6 we have 2 timeout settings:

  • liveTimeout: Define how long a connection will be kept alive, before being closed
  • idleTimeout: Define how long an idle connection will be kept alive, before being closed

connectionTimeout in Lucee 6 is an alias for idleTimeout, Lucee 5 only had connectionTimeout what was an idle timeout.

I will run some test to verify this behaviour and i will improve the documentation for this.

Thanks for the quick response, folks! Echoing what OP mentioned previously:

We’re using Lucee version 6.2.0.321 and have attempted to tweak various settings, as shown below:

var NewDatasource = { 
    type: 'MySQL',
    ...
    custom: {			
        ...
        "maxWaitMillis": 60000,
        "minEvictableIdleTimeMillis" : 60000,
    	"timeBetweenEvictionRunsMillis" : 1000
    },
    connectionTimeout: 1,
    alwaysSetTimeout: true
};

var admin = new Administrator( "server", '{adminPassword}' );
admin.updateDatasource(ArgumentCollection = NewDatasource);
...

However, we are still seeing idle connections not being released, and when testing on a Lucee 5 build, the connections are properly released.

@Joe_Rinehart, would you mind sharing how you managed to reconfigure the data sources at runtime to get around this issue? Any insights would be greatly appreciated!

Moreover, is there a plan to address the 0 values being passed into those DatasourceConnPool arguments below? It would be great to understand if/when this might be fixed in a future release.

Thanks much for your help!

I’ve created a ticket for 6.2.2 about this

https://luceeserver.atlassian.net/browse/LDEV-5538