Bug #4119 re-tested server prep stmt w/ 3.1.3
Submitted: 13 Jun 2004 6:27 Modified: 11 Nov 2009 15:29
Reporter: Ben Eng Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S1 (Critical)
Version:3.1.3 OS:Microsoft Windows (Microsoft Windows XP)
Assigned to: Mark Matthews CPU Architecture:Any

[13 Jun 2004 6:27] Ben Eng
Description:
I am using the JDBC driver in BEA WebLogic Server 8.1 with a TXDataSource, which is being used by MVCSoft JDO. The exact same configuration works perfectly well with the driver version 3.0.14 (and the entire 3.0 series up until now). Version 3.1.1 and 3.1.2 have not been able to function in this configuration. I am seeing the following exceptions (many occurrences) with 3.1.2.

First case:

java.sql.SQLException: Column Index out of range, 4 > 3. 
	at com.mysql.jdbc.ResultSet.checkColumnBounds(I)V(ResultSet.java:3965)
	at com.mysql.jdbc.ResultSet.getNativeLong(I)J(ResultSet.java:4202)
	at com.mysql.jdbc.ResultSet.getLong(I)J(ResultSet.java:1337)
	at weblogic.jdbc.wrapper.ResultSet_com_mysql_jdbc_ResultSet.getLong(I)J(Unknown Source)

Second case:

java.sql.SQLException: Invalid value for getLong() - '4♠design
lock_count
lock_count♀? ¶         ♂   ♥      I1110R╘♠♀¶↕1 ╘♠♀‼☼∟ NMIUQK001        ÷☺↕§♫╘♠♀¶
↕5☺  ' in column 3
        at com.mysql.jdbc.ResultSet.getLongFromString(Ljava.lang.String;I)J(ResultSet.java:5065)
        at com.mysql.jdbc.ResultSet.getNativeLong(I)J(ResultSet.java:4225)
        at com.mysql.jdbc.ResultSet.getLong(I)J(ResultSet.java:1337)
        at weblogic.jdbc.wrapper.ResultSet_com_mysql_jdbc_ResultSet.getLong(I)J(Unknown Source)

How to repeat:
If the stack traces are not obvious on inspection, please contact me at <ben@jetpen.com>.

I can supply a complete EAR for deployment in BEA WLS 8.1 and DDL for mysqld, with complete instructions on configuring the application server resources, and test cases for reproducing the problem.
[14 Jun 2004 1:56] Mark Matthews
Unfortunately, a WAR won't be very easy for us to debug. Is there any way you can generate a standalone testcase that just uses JDBC? Short of that, can you extract the SQL for  the prepared statement and DDL that creates the tables you are using?
[14 Jun 2004 2:01] Mark Matthews
If your JDO implementation doesn't have a way to dump queries, you might also want to add 'profileSQL=true' as a JDBC connection property for your MySQL connection, and the driver will dump all queries to STDERR, which will help immensely in generating a standalone testcase for this.

This issue seems complex, as the prepared statement code is tested rigourously both with our own unit and regression tests, and as well with Sun's JDBC compliance testsuite...If I had to venture a guess, I would say that this has something to do with a particular query, or some metadata being returned incorrectly, not 'vanilla' prepared statement usage.
[15 Jun 2004 1:36] Ben Eng
Changed the synopsis. The original error messages not reproducible. However, using the new 3.1.x JDBC driver in BEA WLS 8.1 with MVCSoft JDO remains a general problem. Under the exact same conditions the 3.0.x drivers work fine.
[15 Jun 2004 1:38] Ben Eng
Log of failed test with 3.1.2

Attachment: log-failure.txt.gz (application/x-gzip-compressed, text), 9.82 KiB.

[15 Jun 2004 2:06] Mark Matthews
Can you post the DDL, some example data, and a query that causes this error to happen?
[15 Jun 2004 2:34] Ben Eng
sample data from successful test run

Attachment: demo_db_dump.sql.gz (application/x-gzip-compressed, text), 17.03 KiB.

[19 Jun 2004 19:42] Ben Eng
As Mark suggested, using useServerPrepStmts=false as the first URL parameter causes all tests to pass now.

However, putting the useServerPrepStmts=false URL parameter after the user and password parameters causes it not to be recognized.

The server prepared statement parameter passing appears to be quite unhappy, at this point. Disabling them brings much joy for now.
[4 Aug 2004 16:37] Mark Matthews
Have you tested this with 3.1.3 or a nightly of 3.1.4 yet?
[8 Aug 2004 20:38] Ben Eng
Retesting with 3.1.3 works much better, but it is still running into some problems. The first one logged is the following. I think something is not being returned properly for the following query.

3455 INFO  [ExecuteThread: '13' for 
queue: 'weblogic.kernel.Default'] jdo  - select 
t1.type,t1.mvcoid,t1.lock_count,t1.startDate,t1.endDate,t1.adminState
 from PortConsumer t1 left join PhysicalPort t2 on t1.port=t2.mvcoid 
where ((t1.port=? and t1.startDate<=?) and t1.endDate>=?)

3455 INFO  [ExecuteThread: '13' for 
queue: 'weblogic.kernel.Default'] jdo  - param 
type=9#1:4dd2010a3cf500fe2c0f49fc00000071

3455 INFO  [ExecuteThread: '13' for 
queue: 'weblogic.kernel.Default'] jdo  - param type=13#2:2004-08-04 
18:08:13.058

3455 INFO  [ExecuteThread: '13' for 
queue: 'weblogic.kernel.Default'] jdo  - param type=13#3:2038-01-18 
21:14:07.0

3545 FATAL [ExecuteThread: '13' for 
queue: 'weblogic.kernel.Default'] test  - rollback due to exception

java.lang.NullPointerException

        at com.mvcsoft.jdo.runtime.framework.MVCJDOStateManager.setManagedObject
(Ljavax.jdo.spi.PersistenceCapable;)V(MVCJDOStateManager.java:1141)
[11 Nov 2009 15:29] Ben Eng
This bug is so old, it's no longer relevant.