fix(db): DB_VALIDATION_QUERY="SELECT 1" in deployment examples disables JDBC4 Connection.isValid() — misleading variable semantics and HikariCP 3.4.2 upgrade required for keepalive

Note Impact of this issue reduced to just the lgtm-observability docker-compose rather than anything in production, so fixing this is just minor cleanup preventing confusion.

Problem Statement

Two distinct but related issues with HikariCP connection validation configuration.

---

Issue 1 — DB_VALIDATION_QUERY="SELECT 1" disables the JDBC4 isValid() path

How HikariCP 3.4.2 determines the validation strategy (PoolBase.java:102):

this.isUseJdbc4Validation = config.getConnectionTestQuery() == null;

This is a strict null check. HikariConfig.seal() normalises empty strings to null before PoolBase reads the value, so the current setenv.sh default (DB_VALIDATION_QUERY="") is safe — isValid() is used.

The problem: docker/docker-compose-examples/lgtm-observability/docker-compose.yml sets:

DB_VALIDATION_QUERY: 'SELECT 1'

"SELECT 1" is non-empty, is not normalised by seal(), so isUseJdbc4Validation = false. HikariCP executes a full Statement.execute("SELECT 1") round-trip on every connection validation instead of using Connection.isValid(). This is worse in every dimension:

	Connection.isValid()	connectionTestQuery = "SELECT 1"
Implementation	pgjdbc native validation, no PreparedStatement allocation	Full Statement.execute() round-trip
Recommended by HikariCP	✅ Explicit HikariCP docs recommend leaving connectionTestQuery null for JDBC4 drivers	❌ "Legacy fallback for JDBC3 drivers only"
PostgreSQL driver support	Full JDBC4 support since pgjdbc 9.x	Works but wasteful

Variable semantics are backwards: The name DB_VALIDATION_QUERY implies "set this to enable validation." The opposite is true — setting it disables the better validation path. This makes the LGTM example a trap for operators who follow it.

SystemEnvDataSourceStrategy.java:87–88 passes the raw value with no guard:

config.setConnectionTestQuery(
    systemEnvironmentProperties.getVariable(CONNECTION_DB_VALIDATION_QUERY));

Even with seal() normalising empty strings, the code intent and documentation are absent.

---

Issue 2 — HikariCP 3.4.2 has no keepaliveTime — upgrade required for #34832

Current version: HikariCP 3.4.2 (bom/application/pom.xml)
keepaliveTime added: HikariCP 4.0.0 (2020)
Current stable: HikariCP 5.x / 6.x / 7.x
Recommended upgrade target: HikariCP 6.2.1 (see analysis below)

The DB_KEEPALIVE_TIME env var, SystemEnvDataSourceStrategy keepalive configuration, and setenv.sh default proposed in issue #34832 cannot be implemented against HikariCP 3.4.2. HikariConfig.setKeepaliveTime() does not exist in this version.

Java version context: dotCMS builds with Java 11 source compatibility but runs on the Java 21 JVM. HikariCP's version requirements are runtime constraints — the JVM version is what matters, not the source compiler flag. Java 21 makes HikariCP 5.x, 6.x, and 7.x all viable at runtime.

Why 6.2.1 over 5.1.0:

Improvement	Version	Production impact
keepaliveTime defaults to 2 min (auto-enabled)	6.2.1	No explicit config needed — active by default
SQLTimeoutException no longer evicts connection	6.2.0	Critical for #34832: adding setQueryTimeout() without this causes HikariCP to shrink the pool on every timeout, worsening pool exhaustion
maxLifetime jitter increased 2.5% → 25%	6.1.0	Prevents 60-connection burst replacement in a ~45s window — spreads over 15 min
Enhanced debug logging when pool drains to zero	6.2.0	Faster diagnosis of DB restart / network partition events
validationTimeout correctly respected	6.3.1	Was silently ignored in some code paths in earlier versions

Note on 6.3.x: There was setSchema behavioral churn across 6.3.0, 6.3.1, and 7.0.1. If dotCMS does not set a default schema via HikariConfig (PostgreSQL uses search_path instead), this is a non-issue. Verify before targeting 6.3.x.

On 7.x: Viable on Java 21 but the primary addition (HikariCredentialsProvider for dynamic credential rotation via Vault/Secrets Manager) is not relevant for this upgrade cycle.

What still works without upgrade:

• tcpKeepAlive=true in DB_BASE_URL (pgjdbc JDBC URL property, HikariCP-independent) ✅
• DB_MAXWAIT/DB_MAX_WAIT naming bug fix (#34832) ✅
• setQueryTimeout() changes (#34832) ✅

What requires HikariCP upgrade:

• keepaliveTime pool-level connection pinging ❌
• Any HikariCP 4.0+ features (e.g., connectionInitSql improvements, JMX enhancements) ❌

Without keepalive, minimum idle connections that are never checked out can be silently killed by the AWS NAT gateway (~350s idle TCP timeout) without HikariCP knowing. The next attempt to use a minimum-idle connection after NAT timeout would fail, requiring reconnect — this is the silent failure mode that keepalive prevents.

---

Files

• docker/docker-compose-examples/lgtm-observability/docker-compose.yml — DB_VALIDATION_QUERY: 'SELECT 1'
• dotCMS/src/main/java/com/dotmarketing/db/SystemEnvDataSourceStrategy.java — line 87–88
• bom/application/pom.xml — HikariCP 3.4.2

---

Acceptance Criteria

Tier 1 — Fix DB_VALIDATION_QUERY semantics (safe, no HikariCP upgrade needed)

• DB_VALIDATION_QUERY: 'SELECT 1' removed from docker/docker-compose-examples/lgtm-observability/docker-compose.yml (or replaced with empty string "")
• SystemEnvDataSourceStrategy.java adds explicit null/blank guard before calling setConnectionTestQuery() — document that passing any non-empty value disables Connection.isValid() and is intended for JDBC3-only legacy databases:

  String testQuery = systemEnvironmentProperties.getVariable(CONNECTION_DB_VALIDATION_QUERY);
  if (testQuery \!= null && \!testQuery.isBlank()) {
      // NOTE: setting connectionTestQuery disables JDBC4 Connection.isValid() for ALL
      // connection validation. Only set this for pre-JDBC4 databases. PostgreSQL/pgjdbc
      // fully supports isValid() — leave this unset for optimal validation.
      config.setConnectionTestQuery(testQuery);
  }
  

• setenv.sh: comment added to DB_VALIDATION_QUERY default clarifying that empty/unset = isValid() (preferred), non-empty = disables isValid()
• Audit all other docker-compose examples and k8s ConfigMaps for DB_VALIDATION_QUERY set to non-empty values

Tier 2 — HikariCP upgrade (prerequisite for keepalive)

• HikariCP upgraded from 3.4.2 → 6.2.1 in bom/application/pom.xml
• SystemEnvDataSourceStrategy.java updated to support keepaliveTime via DB_KEEPALIVE_TIME env var (as specified in #34832) — note: keepaliveTime defaults to 2 minutes automatically in 6.2.1 even without explicit config; the env var allows operator override
• Verify setQueryTimeout() from #34832 does not trigger connection eviction (fixed in 6.2.0 — SQLTimeoutException no longer treated as evictable offense)
• setenv.sh default DB_KEEPALIVE_TIME=120000 added
• Regression tested: HikariCP major version bump may change behaviour for maxLifetime, idleTimeout, connectionTimeout edge cases — full connection pool integration test pass required
• #34832 keepaliveTime acceptance criteria unblocked after Tier 2 completes

Start with docker/docker-compose-examples/lgtm-observability/docker-compose.yml, dotCMS/src/main/java/com/dotmarketing/db/SystemEnvDataSourceStrategy.java, bom/application/pom.xml, and setenv.sh. Verify the current validation-query handling and HikariCP version before deciding whether to complete Tier 1 only or the upgrade in Tier 2. Done means the selected acceptance criteria are implemented, other non-empty DB_VALIDATION_QUERY values are audited, and connection-pool integration tests pass.