PreparedStatement cache corrupt?

[davidtinker]

We have an application that randomly generates the following error under heavy load:

java.sql.SQLException: Too many or too few host variables given.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:340)
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:2830)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3115)

I do not think there is anything wrong with the application code as 99.9% of the time it works fine. The ps that fails is usually an insert statement that just inserts 3 ints. Not much that can be wrong with that. However sometimes other statements fail with the same error.

When the error starts it happens frequently and after a while we have to restart JBoss or no one can get anything done. However the system often runs fine for 1 hour or two before there are problems.

We are using JBoss 2.4.3 with Informix JDBC driver. JBoss is running on a dual CPU Intel Linux box. We have seen the same problem on a single CPU Linux box. We are using Sun's JDK 1.3.1_02 HotSpot server VM.

This smells like a concurrency issue to me. I think somehow different threads are getting hold of the same PreparedStatement.

Our application just uses session beans and does not make any attempt to cache prepared statments itself. All ps references are held in local variables only and ps'es are closed in finally blocks.

As you can imagine this is urgent. The problem only manifested itself when we went live with 100 users ... Our app could be made to run on Weblogic in a day or two but we really don't want to have to do that.

Any ideas?

[davidjencks]

I think there is a way to set the ps cache size to 0. This might show if it is a ps cache problem or the connection pool that is thread-unsafe.

You could also try using the jca support from 2.4.4 and wrapping your driver. While equally likely to have threading errors, I find the code somewhat easier to understand.

[davidtinker]

We found that. We added some debugging info (printing out the addr of each ps etc) and seems like the JDBC driver is caching the underlying prepared statements itself. JBoss gives us a different ps each time.

I am going to find out if there is some way to turn this off. I think it is broken...