Mysql read data immediately after writing?

Question

I am using a MySQL DB and a Java JDBC client to access it.

I have a Table that contains session information. Each session is associated with a SessionToken. This token is a Base64 encoded String of a Hash of some of the session values. It should be unique. And is defined as varchar(50) in the db.

When I try to lookup a session by its token I query the database using an sql statement like this:

select SessionId, ClientIP, PersonId, LastAccessTime, SessionCreateTime from InkaSession where SessionToken like 'exK/Xw0imW/qOtN39uw5bddeeMg='

I have a UnitTest that tests this functionality, and it consistently fails, because the query does not return any Session, even tough, I have just written the session to the DB.

My Unit test does the following:

Create Connection via DriverManager.getConnection
Add a session via Sql Insert query
close the connection

create Connection via DriverManager.getConnection
look for the session via sql select
unit test fails, because nothing found

When I step through this UnitTest with the debugger and copy past the select sql that is about to be sent to the db into a mysql command line, it works fine, and I get the session row back.

I also tried to retrive an older session from the db by asking for an older SessionToken. This works fine as well. It only fails, if I ask for the SessionToken immediately after I inserted it.

All connections are on AutoCommit. Nevertheless I tried to set the Transaction Level to "Read Uncommited". This did not work either.

Has anyone any further suggestions?

Answer 1

This is typically caused by the connection not being committed between insert and select.

Did you basically do the following?

statement.executeUpdate("INSERT INTO session (...) VALUES (...)");
connection.commit();
resultSet = statement.executeQuery("SELECT ... FROM session WHERE ...");

Edit I tried the following SSCCE on MySQL 5.1.30 with Connector/J 5.1.7:

public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    try {
        connection = DriverManager.getConnection("jdbc:mysql://localhost/javabase", "root", null);
        statement = connection.createStatement();
        statement.executeUpdate("INSERT INTO foo (foo) VALUES ('foo')");
        resultSet = statement.executeQuery("SELECT id FROM foo WHERE foo = 'foo'");
        if (resultSet.next()) {
            System.out.println(resultSet.getLong("id"));
        } else {
            System.out.println("Not inserted?");
        }
    } finally {
        SQLUtil.close(connection, statement, resultSet);
    }
}

Works flawlessly. Maybe an issue with your JDBC driver. Try upgrading.

Answer 2

Solved: The two token strings where not identical. One of them had a couple of Zero bytes at the end. (Due to the encrypting and decrypting and padding...) The two strings where visually identical, but MySQL and Java both said, they where not. (And they where right as usual)