Bug #19889 Persistent Connections Don't Re-Query Correctly

• Submitted: 17 May 2006 15:32
• Modified: 17 May 2006 15:41
• Reporter: A P
• Status: Not a Bug
• Category: Connector / J
• Severity: S1 (Critical)
• Version: J: 3.1.12, MySQL: 5.0
• OS: Application WinXP, mySQL: Linux
• CPU Architecture: Any

[17 May 2006 15:32] A P

Description:
In Java, when opening up a connection and querying the Database, updates done previously in a separate applicaiton connected to the same database (i.e. mySQL Gui) are detected. However, if this connection is kept open and then an update is done again in the separate connection (i.e. GUI), the new RecordSet (as a result of the same query) does not return the new changes. They are only detected if the connection is closed and re-opened.

How to repeat:
Create a table in mySQL with a key and a processed_tmstp as a Timestamp.  Run the code below, while the code is running, make an update to set Timestamp to null, the application should detect the null and print it out. In fact, it doesn't. If you stop and run the application again, the changes will be seen.

import java.sql.*;

public class Connect_mySQLTest {
	protected static Connection conMySql = null;

	public static void main(String[] args) {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			String userName = "xxx";
			String password = "xxxaaa";
			String url = "jdbc:mysql://serverxxx/stx";
			Class.forName("com.mysql.jdbc.Driver").newInstance();

			System.out.println("Database connection established");
			int loopy=1;
			while (loopy == 1) {
				try {
					if (conMySql == null || conMySql.isClosed()) {
						conMySql = DriverManager.getConnection(url, userName, password);
						conMySql.setAutoCommit(false);
					}
				} catch (Exception e) {
					System.out.println("ERROR: Cannot Open DB Connection to MySQL!");
				}

				getMySQLCallResultSet();
			}
			if (conMySql != null)
				try {
					conMySql.close();
				} catch (SQLException eSQL) {
				}

		} catch (Exception e) {
			System.err.println("Exception : " + e.getMessage());
			e.printStackTrace();
		}
	}
	
    private static int getMySQLCallResultSet()
    {
	long local_callkey = 0;
	
	try
	{	    
	    Statement getKey = conMySql.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
	    ResultSet rsGetKey = getKey.executeQuery("SELECT c.local_callkey FROM stx.calls c " +
			    " WHERE " +
			    " c.processed_tmstp IS NULL");
	    if (rsGetKey.next())
	    {
			local_callkey = rsGetKey.getLong(1);
			if (local_callkey > 0)
			{		    
				System.out.println("We need to process call key: " + local_callkey);
			    if(rsGetKey != null) try {rsGetKey.close();}
			    catch(SQLException eSQL) {System.out.println("GetCallDataThread.....(): Exception! " + eSQL.toString());}
			    if(getKey != null) try {getKey.close();}
			    catch(SQLException eSQL) {System.out.println("GetCallDataThread.....(): Exception! " + eSQL.toString());}
			    
			    return 1;
			}
	    }
	}
	catch (Exception e)
	{
	    System.out.println("Error: in getMySQLCallResultSet " + e.getMessage());
	}
	return 0;
    }
   }

[17 May 2006 15:40] A P

The text should read "...the application should detect the non-null key" and print it out."

[17 May 2006 15:41] Mark Matthews

MySQL's default isolation level is REPEATABLE_READ.

Since you're not committing or rolling back on the "open" connection, you're working with a "snapshot" of data since the connection was set to autoCommit =false.

You either need to commit or rollback (or reduce your isolation level) to see changes made by other transactions.