SqlConnectionPooling maxConnLifetimeMillis and Apache Commons DBCP Database issue

[Blama]

Hi Isomorphic,

please see this error with SqlConnectionPooling and Apache Commons DBCP Database (I'm using the commons-dbcp-1.4.jar you deliver with the framework).

It seems there is some issue with maxConnLifetimeMillis (which you have in your docs), but I don't know what, as it seems to be correct w.r.t the Apache docs.
It does seem though that this setting is only in Apache 2.0.1+ and not in 1.4. Is this possible? Did you plan to ship a newer version of Commons DBCP?

(FYI: 2.0 uses the org.apache.commons.dbcp2 namespace, while 1.4 uses org.apache.commons.dbcp).

I'm testing with 12.0p here now, but I assume it was the same with 6.1p.

I have this configuration in my server.properties file (v12.0p_2018-12-13).

sql.defaultDatabase: Oracle
sql.useAnsiJoins: true

# Apache Commons DBCP Database connection pooling
sql.pool.enabled: true
sql.pool.minIdle: 3
sql.pool.testWhileIdle: true
sql.pool.timeBetweenEvictionRunsMillis: 15000
sql.pool.minEvictableIdleTimeMillis: 29000
[B]sql.pool.maxConnLifetimeMillis: 60000[/B]

sql.Oracle.database.type: oracle
sql.Oracle.autoJoinTransactions: true
sql.Oracle.database.supportsSQLLimit: false

sql.Oracle.interface.credentialsInURL: true
sql.Oracle.interface.type: dataSource

sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
sql.Oracle.driver.serverName: localhost
sql.Oracle.driver.portNumber: 1521
sql.Oracle.driver.databaseName: XE
sql.Oracle.driver.user: xxx
sql.Oracle.driver.password: xxx

sql.Oracle.driver.driverType: thin
sql.Oracle.driver.networkProtocol: tcp
sql.Oracle.driver.context:

which results in this log entry for the 1st DB access:

=== 2018-12-17 12:09:21,095 [ec-6] DEBUG AppBase - [builtinApplication.fetchWithoutRequiresRole] No userTypes defined, allowing anyone access to all operations for this application
=== 2018-12-17 12:09:21,096 [ec-6] DEBUG AppBase - [builtinApplication.fetchWithoutRequiresRole] No public zero-argument method named '_fetchWithoutRequiresRole' found, performing generic datasource operation
=== 2018-12-17 12:09:21,096 [ec-6] WARN  LMSSQLDataSource - [builtinApplication.fetchWithoutRequiresRole] addDefaultTemplateContext results in null-Language, defaulting to English.
=== 2018-12-17 12:09:21,100 [ec-6] DEBUG JSTranslater - [builtinApplication.fetchWithoutRequiresRole] disableStringInBrowser = false
=== 2018-12-17 12:09:21,100 [ec-6] INFO  SQLDataSource - [builtinApplication.fetchWithoutRequiresRole] Performing fetch operation with ...ommitted...
=== 2018-12-17 12:09:21,110 [ec-6] DEBUG SQLDriver - [builtinApplication.fetchWithoutRequiresRole] Oracle: Initializing SQL config using interface.type: datasource with driver: oracle.jdbc.pool.OracleDataSource
=== 2018-12-17 12:09:21,116 [ec-6] INFO  DataTools - [builtinApplication.fetchWithoutRequiresRole] While applying to oracle.jdbc.pool.OracleDataSource, couldn't setProperties:
{
    context:"No such property"
}
=== 2018-12-17 12:09:21,134 [ec-6] DEBUG SQLDriver - [builtinApplication.fetchWithoutRequiresRole] Oracle: Database metadata: {
    driverName:"Oracle JDBC driver",
    driverMajorVersion:11,
    driverMinorVersion:2,
    databaseProductName:"Oracle",
    databaseProductVersion:"Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production",
    databaseMinorVersion:2,
    databaseMajorVersion:11,
    JDBCMinorVersion:2,
    driverVersion:"11.2.0.3.0",
    JDBCMajorVersion:11
}
=== 2018-12-17 12:09:21,139 [ec-6] DEBUG SQLDataSource - [builtinApplication.fetchWithoutRequiresRole] DataSource 11 acquired SQLDriver instance 1053762653 during initialization
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
=== 2018-12-17 12:09:21,312 [ec-6] DEBUG Velocity - [builtinApplication.fetchWithoutRequiresRole] Velocity config: {
}
log4j:WARN No appenders could be found for logger (org.apache.Velocity).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
=== 2018-12-17 12:09:21,391 [ec-6] INFO  Velocity - [builtinApplication.fetchWithoutRequiresRole] Velocity Tools available, support enabled.
=== 2018-12-17 12:09:21,403 [ec-6] DEBUG SQLDataSource - [builtinApplication.fetchWithoutRequiresRole] DataSource 20 acquired SQLDriver instance 538846648 during initialization
=== 2018-12-17 12:09:21,403 [ec-6] DEBUG SQLDataSource - [builtinApplication.fetchWithoutRequiresRole] DataSource 24 acquired SQLDriver instance 1641800829 during initialization
=== 2018-12-17 12:09:21,412 [ec-6] INFO  SQLDataSource - [builtinApplication.fetchWithoutRequiresRole] derived query: SELECT $defaultSelectClause FROM $defaultTableClause$defaultAnsiJoinClause WHERE $defaultWhereClause
=== 2018-12-17 12:09:21,413 [ec-6] INFO  SQLDataSource - [builtinApplication.fetchWithoutRequiresRole] 11: Executing SQL query on 'Oracle': SELECT ...ommitted...
[B]=== 2018-12-17 12:09:21,457 [ec-6] INFO  DataTools - [builtinApplication.fetchWithoutRequiresRole] While applying to com.isomorphic.pool.ISCObjectPool, couldn't setProperties:
{
    maxConnLifetimeMillis:"No such property"
}[/B]
=== 2018-12-17 12:09:21,457 [ec-6] INFO  PoolManager - [builtinApplication.fetchWithoutRequiresRole] SmartClient pooling started for 'Oracle' objects

Best regards
Blama

[Isomorphic]

That is actually a DBCP 2.0 property and we are not using DBCP 2.0, so it was added to our docs by mistake.

Unfortunately DBCP 2.0 is not backwards compatible so you cannot simply drop in the .jar.

We know of no compelling reason to upgrade so we don't have any near-term plans to do so, but if there's something important to you here, you could sponsor it.

[Blama]

Hi Isomorphic,

OK, thanks. It's working for me as needed with the other eviction settings. I'll then remove that setting from my server.properties.
Can you also remove it from the docs?

Best regards
Blama

[Blama]

Another question:
You don't always ship the newest versions of Apache Commons libs, even if they don't change the namespace. Is there a reason for this?

Thank you & Best regards
Blama

[Isomorphic]

We wait until we see that something has changed that our customers may actually care about.

[Blama]

Hi Isomorphic,

please note that "maxConnLifetimeMillis" is still twice in the docs.

Best regards
Blama

[Blama]

Hi Isomorphic,

maxConnLifetimeMillis is no longer in the docs.

Thank you & Best regards
Blama

[Blama]

Hi Isomorphic,

short question, are other properties of Commons DBCP 1.4 also supported? I'm asking especially for maxWait. If so, I'd configure it using:

 sql.mydatabase.pool.maxWait: 10000

I assume they are, as you write

You configure the behavior of SQL connection pooling with the following server.properties settings (note, many of these settings map directly to settings in the underlying DBCP library; you can find out more about their effects in the [URL="http://commons.apache.org/proper/commons-dbcp/configuration.html"]DBCP docs[/URL]):

here at "Connection pool settings", but I wanted to double check.

Thank you & Best regards
Blama

[Blama]

Hi,

OK, I'm pretty sure it will work, did a Forum search for maxWait and found other users at least setting it here and here.

Best regards
Blama

[Blama]

Hi Isomorphic,

We wait until we see that something has changed that our customers may actually care about.

this might now be the case, see this issue and this response from a Apache Commons DBCP committer when asking for a new release of the commons-dbcp-1 version-line.

Best regards
Blama