getting stale data (jdbc / innodb)
Posted by: Christian Pflugradt
Date: May 08, 2014 10:45AM


I'm using a mySQL database for session handling within an application (among other things). Recently as I found out that myISAM doesn't support foreign keys and transactions, I updated the database engines for all tables in this database to InnoDB. Since then I have been getting stale information through JDBC. As I've investigated quite a lot online but didn't find anyone with the same problem or anything that would help me I'll describe precisely in the following lines what is meant to be done, what is not working and what I have found out during troubleshooting. I'll add information about my environment and possibly less relevant information about how I use the JDBC connections in the final part.

I have a table called Sessions with two integer fields ca─║led su and session (and some other fields irrelevant for the demonstration). su will usually have only two possible values, either 0 or 1.

I do first insert a new row, setting su to 0 and session to a large number or update an existing row, changing only the value in the session field setting su to 0 and session to a larger number as well. Some time later with the same connection I update su to 1. After that I always commit the update. Later on within the same connection I select the su field value where session = 'the same number from the where clause in the update statement' and I get the stale value 0, even though I've included sql_no_cache in the selection statement.

I verified that the value on the database has actually been updated.
1.) I inserted a new row, setting su to 0.
2.) I updated su on this new row to 1 and committed the change.
3.) I selected the field through the mysql console and retrieved the correct up to date value.
4.) I selected the field through the same jdbc connection I used for step one and two and got the stale value.

Apparently the issue is somewhere in the established connection because when I changed the code to create a new connection after each statement sent through jdbc the problem didn't occur, this is however not a workaround I'd like to use.

Summarized in one sentence: I seem to experience the problem that my jdbc connection is correctly altering data in the database but fails to retrieve the updated, current information through the same jdbc connection even though I explicitely select non cached data.

I'm using a mySQL database version 5.0.96 running on SLES 11. I've been trying three different JDBC connectors from which are of version 5.1.26, 5.1.30 and 5.0.8 - all of them returned the same stale values. I am more or less using the standard server configuration, only that I later on changed the database engine to InnoDB (after all tables were created and filled with data).

Background information on the scenario described: The application performing the mechanism described is a server application. The clients themselves don't execute any queries but only call the according methods provided by the server component. Each user has a separate connection stored in a concurrent map. When a connection stored in this map is requested to be used for a query it'll always happen within a block synchronized on the connection object.

I'd be very grateful if you have any ideas what could be going wrong. Let me know if you need any further information.

Options: ReplyQuote

Written By
getting stale data (jdbc / innodb)
May 08, 2014 10:45AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.