MySQL Bugs: #4642: Invalid value (JDBC) after possible server timeout. Prepared statements.

Bug #4642	Invalid value (JDBC) after possible server timeout. Prepared statements.
Submitted:	20 Jul 2004 6:49	Modified:	20 Dec 2004 23:59
Reporter:	Wil Williams	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:		OS:	Linux (Debian Linux)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:

When using a simple prepared statement with the code as follows (or similar):

  PreparedStatement ps = conn.prepareStatement("SELECT a,b from c where id=?"); 
  ps.setInt(1, getID());
  ResultSet rs = ps.executeQuery();
  while (rs.next()) {
      int a = rs.getInt(1);
      int b = rs.getInt(2);
      ...
  }
  rs.close();
  ps.close();
  
It works fine if no server connections have been made or if all server connections that have been made were made within the last few minutes (unsure of exact time).

This code is run many many times and works fine when using a single JVM with the latest JConnector. However, if the connections lie idle for some time under this JVM or a second JVM is used sometime later it crashes on the second readInt with an exception of form:
  java.sql.SQLException: Invalid value for getInt() - 'elvelayerSourceTagMappinglayerSourceTagMappingcolumnIndexcolumnIndex?' in column 2

[ The actual query above was "SELECT columnIndex, tagField, columnName, hasTagField from layerSourceTagMapping where layerSourceID = ?" ]

Note:

- I'm using the precompiled 4.1.3 beta from the web site.
- I'm using mysql-connector-java-3.1.2-alpha-bin.jar
- I have noticed that the aliasing has changed and that the 3.1.1 connector no longer works on prepared statements (did under 4.1.1)
- We are using a pool of connections that I believe is thread safe, hence no other prepared statement is being execute on this connection (or statement) concurrently.
- It works for a while but fails when connections are left in the pool for some time without being used.
- It works fine when I start up the server but fails "after a while". Actually, I just tried to confirm this by restarting the server and it failed first time.
- The system is very mature and has been being used in production for several years (but notably without mysql "SERVER" prepared statements added in 4.1).

If anyone has any advice, help it would be much appreciated.

I have slept on this bug for a few weeks but cannot work it out. The above is the best I could do.

Wil

How to repeat:
Unfortunately I have not managed to get a simple case that repeats this one 100% of the time.

Suggested fix:
Unclear, but thinking about it.

After much investigation it appears to be related to the ServerPreparedStatement internals in the driver. I'm about to download the driver source and have a look. It's also based on what's been done on the connection (maybe during garbage collection?). I can get it to be releatable on the first connection to the database without a connection pool in a swing application but not stand alone:

getting ps 
ps com.mysql.jdbc.ServerPreparedStatement[1] - SELECT id FROM dataset WHERE name=** NOT SPECIFIED **[B@ffa6eb
ps com.mysql.jdbc.ServerPreparedStatement[1] - SELECT id FROM dataset WHERE name='psma'[B@11d3226
java.sql.SQLException: java.lang.ArrayIndexOutOfBoundsException: 2
	at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:908)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1622)

Confirmed. Database protocol error in underlying inputstream. Temp: Revert to mysql-connector-java-3.0.14-production-bin.jar to avoid server prepared statements.

> - I have noticed that the aliasing has changed and that the 3.1.1
> connector no longer works on prepared statements (did under 4.1.1)

Define 'aliasing'. I'm not clear what you mean with this statement.

> - We are using a pool of connections that I believe is thread safe,
> hence no other prepared statement is being execute on this connection
> (or statement) concurrently.
> - It works for a while but fails when connections are left in the pool
> for some time without being used.

Define 'some time'. Is it longer than wait_timeout on the server? Are you using 'autoReconnect'?

> - It works fine when I start up the server but fails "after a while".
> Actually, I just tried to confirm this by restarting the server and it
> failed first time.

Restarting _which_ server, MySQL or your application?

Mark,

Thanks for the quick reply and sorry for the somewhat rash posting (seems sleeping on it for a while didn't help).

'alaising' - I think the driver works correctly. I "believe" the lines:
executeQuery("select name as n ..."
rs.getString("name");
may have been what was there. I can't be bothered checking with CVS, etc but I don't think there's an issue here. Clearly, even if this worked using old connectors it's bad code and not conforming to JDBC, etc.

'autoReconnect' - I'll look into this but don't think it's the problem at this stage.

'server' - mysql server (via "mysqladmin shutdown" and "safe_mysqld").

--
I've given up for the time being but would appreciate it you could leave this post in a transient state so I can work it out (maybe some weekend). I have program (a) which makes the queries off a simple connection. It works. I change it to use our pooling, it works. I combine it into our system but simply invoke the "select name" swing dialog from main (after setup from the same config file). It still works. Now program (b) which invokes the same setup file, loads a swing window, let's you choose from the menu, brings up the "select name" dialog and runs the same query. Program (b) fails "most" of the time (seems dependent on the table contents). Both programs log the same information and seem to make exactly the same sql/jdbc calls. I've traced it to a line from the driver (from nightly builds).

So, it appears to be a weird one somewhere between my code and the server when using prepared statements. It's a tough one and probably should be ignored until I can provide a concrete example of failure to rule out bugs on my side (but I've eliminated obvious thread and synchronisation issues). Will investigate further as time permits.

Could you please test the following build? It fixes a similar problem I was able to reproduce locally.

http://downloads.mysql.com/snapshots/mysql-connector-java-3.1/mysql-connector-java-3.1-nig...

I have been having very similar unpredictablly intermittent problems with the same driver (mysql-connector-java-3.1.3-beta-bin.jar) under the 4.1.3-beta-standard DB.  

However, it seems to occur LESS frequently with the 20040722 nightly build.  However, I still get ocassional errors such as the following:

Column Index out of range, 4 > 3.  SQLState: S1009 ErrorCode: 0

Please note that the exact code (entirely unalerted) works most of the time.  I am using PreparedStatements and the error that is intermittently returned is always in a different place and/or statement is the error itself also varies significantly.  However, in all cases, simply re-running the exact same code several times will produce successful outcomes.

Finally, I have also tried to 20040729 nightly build and found that it is less reliable (in terms of the above problem) than the 20040722 one.

I forgot to add the following in my previous post.

Please note that I do NOT appear to have ANY of the errors or "hickups" whatsover with the exact smae code and DB (4.1.3-beta-standard DB) when I use the mysql-connector-java-3.0.14-production-bin.jar driver.

There seems to be some instability in the 3.1.x driver still.

We've been able to repeat this behavior from the libmysql-based 'c' client as well, turns out the server is sometimes not sending the correct packets.

I'll leave this bug open until it is fixed in the server.

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/