Question
SQLState 42S02 leaves Tomcat JDBC pool connections in service after schema change
Sora Finch
0 reputation · 14 Sept 2023, 00:20 UTC
72.9K views1
Loading…
0 reputation · 14 Sept 2023, 00:20 UTC
Apache Tomcat's built-in JDBC connection pool (org.apache.tomcat.jdbc.pool.DataSource, available since Tomcat 7) exposes rollbackOnReturn to reset transaction state when a connection is returned. After a database schema change, a connection may encounter a table-not-found failure such as SQLState 42S02 or ORA-00942. The pool's validation settings (testOnBorrow, testOnReturn, validationQuery) verify basic connectivity but do not inspect transaction state or schema-change errors.
This creates an unresolved operational question: when an idle pooled connection has raised a schema-change-related error, the pool appears to retain it unless a validation query later fails. It is unclear whether rollbackOnReturn alone is sufficient to mark such a connection for discard, or whether the pool requires an explicit flush or application restart to guarantee that all connections reflect the new metadata.
Does rollbackOnReturn cause the pool to discard connections that previously raised SQLState 42S02? Is there a documented pool attribute that invalidates a connection on specific SQLState or vendor error codes without relying on validation failure? After a DDL statement triggers an implicit commit, does the pool treat the connection as clean even if a later statement fails with a schema-change error?
33775 reputation · 14 Sept 2023, 05:58 UTC
No, rollbackOnReturn does not cause the pool to discard connections that previously raised SQLState 42S02. Its sole purpose is to ensure that any active transaction is rolled back before the connection is returned to the pool. If the JDBC driver allows the rollback to succeed after a schema-related error, the pool considers the connection healthy and retains it for future use.
The Tomcat JDBC pool (org.apache.tomcat.jdbc.pool.DataSource) manages connections based on physical health and explicit validation queries. When a DDL statement occurs (such as dropping or renaming a table), the physical connection to the database remains open, but the metadata cached by that specific session may become stale.
tomcattestOnBorrow or testOnReturn typically use a simple validationQuery (e.g., SELECT 1). This verifies that the database is reachable but does not verify if the specific tables or schemas the application requires still exist.rollbackOnReturn finds no active transaction to roll back and leaves the connection "clean."connection.rollback(). If the error (42S02) did not invalidate the physical socket, the rollback succeeds, and the connection returns to the pool in a valid state.Since there is no built-in attribute to invalidate connections based on specific SQLState, you must use alternative strategies to ensure the pool reflects the new schema:
validationQuery to check for the existence of a critical table. However, this adds performance overhead as it runs on every borrow/return.DataSource to intercept SQLEceptions with SQLState 42S02. When detected, the wrapper can call dataSource.evictObject() or close the connection manually to force the pool to discard it.Note: This analysis assumes the use of the standard org.apache.tomcat.jdbc.pool. If you are using HikariCP or the older Tomcat DBCP-based JDBC pool, the behavior regarding error-based invalidation may differ.
Use comments to ask for clarification. Post a solution as an answer.
No question comments on this page.