MySQL Bugs: #66504: PreparedStatements Closed when autocommit=false using Master-Master configuratio

Bug #66504	PreparedStatements Closed when autocommit=false using Master-Master configuratio
Submitted:	22 Aug 2012 19:49	Modified:	21 Nov 2013 14:47
Reporter:	Diogo Guerra	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.21	OS:	Solaris
Assigned to:	Filipe Silva	CPU Architecture:	Any

Description:
I'm not sure if this is a bug or an expected behavior.

I have two mysql servers configured in a Master-Master setup and if I have the autocommit to false, right after a commit is done, the PreparedStatement is closed. 

Right after the commit if I try to add some more data to a batch, I get the following exception:

MySQLNonTransientConnectionException: No operations allowed after statement closed

How to repeat:
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://master1,master2/mydb?failOverReadOnly=false", "user", "pass");

        execute(connection, "CREATE TABLE `MYTEST` (`FIELD1` VARCHAR(256) NOT NULL)");
        final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO `MYTEST` (`FIELD1`) VALUES (?) ");

        connection.setAutoCommit(false);

        preparedStatement.setString(1, "test");
        preparedStatement.addBatch();
        preparedStatement.executeBatch();

        if (!connection.getAutoCommit())
            connection.commit();

        preparedStatement.setString(1, "test");  >>>> get an exception here
        preparedStatement.addBatch();
        preparedStatement.executeBatch();

        if (!connection.getAutoCommit())
            connection.commit();

// In this code, if I change the setAutocommit line to true, everything goes fine.

When load balancing, the prepared statement is considered discarded after commit, because the driver is not going to seamlessly prepare on any other connections, and the connection is switched after commit(). Most (not all) real-world use cases grab a connection from the pool, do everything, then close() it (returning it to the pool) for one transaction, and never assume that connection state lasts beyond the scope of a transaction. The load balancing behavior is optimized for these use cases.

It is at least a bug in that this behavior should be documented.

Hi Mark,

Thanks for the clarification. As I posted in the beginning of the issue, I suspected that it could be an architectural design and not a bug.

Yesterday I've searched hours for prepared statements and replication/failover and  it is very hard to find some information about that.

It would be very useful in the future to have some documentation about that.

Just to leave out use case here, and why we don't prepare each statement each time we need:

We develop an application engine for high throughput computation and generate a lot of data that it is stored to the database. As the schema is known, we generate the insert statements and flush the data as batches every 10 seconds. (we can have 500 to 1000 statements being flushed every 10 seconds)

Correct me if I'm wrong, but the concept behind prepared statements is that we reuse the statement, changing only some parameters and the compile time of the statements is eliminated every time the statement is used.

So, what we do is to create a pool of connections (for example 5) and all the connections have for example 200 statements, then they are always reused to reduce the insert time.

Hi Diogo,

It isn't clear whether this test is being run in a single thread. If your application is sharing the connections across multiple threads (via prepared statements), then it is possible that one thread is handling the connection/failover error. Once the failover happens, the original connection and all of it's prepared statements are closed. They will need to be re-prepared after the failover on the new connection.

Are you able to reproduce this behavior in a single-threaded program? If so could you please provide us such a self contained test case?

Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Consolidated sections with multi-host information into a new section, in
which a Note was added for the treatment of session-scope data:

"If an application reuses session-scope data (for example, variables, SSPs)
beyond a transaction boundary, failures are possible, as a switch between the
physical connections (which is also a switch between sessions) might occur.
Therefore, the application should re-prepare the session data and also
restart the last transaction in case of an exception, or it should re-prepare
session data for each new transaction if it does not want to deal with
exception handling. "