Bug 61103

Summary: StatementCache potentially caching non-functional PreparedStatement with DB2
Product: Tomcat Modules Reporter: Julian Reschke <julian.reschke>
Component: jdbc-poolAssignee: Tomcat Developers Mailing List <dev>
Status: NEW ---    
Severity: normal CC: kfujino
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: All   
URL: https://issues.apache.org/jira/browse/OAK-6237

Description Julian Reschke 2017-05-18 11:36:06 UTC
See <https://issues.apache.org/jira/browse/OAK-6237>.

It seems that StatementCache under certain circumstances returns an incorrect statement, causing setString on index 1 to fail with:

com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10145][10844][4.16.53] Invalid parameter 1: Parameter index is out of range. ERRORCODE=-4461, SQLSTATE=42815

Note:

- currently cleanly reproducible with jackrabbit oak trunk (and release branches)
- happens only for DB2 it seems
- disabling the StatementCache interceptor fixes the problem
- Oak currently uses 7.0.75, but the same problem happens with 7.0.77
Comment 1 Julian Reschke 2017-05-22 09:09:04 UTC
and it happens with 8.5.15 as well...
Comment 2 Julian Reschke 2017-09-21 14:04:31 UTC
Still present in 7.0.81.
Comment 3 Julian Reschke 2017-09-26 16:11:45 UTC
It seems I found the issue: s statement was indeed re-used, but on the first invocation the table wasn't present; with DB2, that state apparently is stored with the statement object. Will close this ticket once I fixed my code.
Comment 4 Julian Reschke 2017-09-27 11:44:27 UTC
Found in <https://blog.bertvanlangen.com/software-development/jdbc-statement-cache/>:

"The IBM Data Server Driver for JDBC and SQLJ does not check whether the definitions of target objects of statements in the internal statement cache have changed. If you execute SQL data definition language statements in an application, you need to disable internal statement caching for that application"

So caching PreparedStatements for DB2 is dangerous if it's a DDL statement, or if actually the state of the table changes between invocations (such as a transition from "does not exist" to "exist").

Now code can of course workaround this, but wouldn't it be better if the StatementCache would evict statements that have causes a non transient exception?