Bug #38747 java.sql.SQLException: Connection is read-only. Queries leading to data modifica

Description:
I am using MySQL Connector/J 5.1.6, and I've got the following exception in my application:

java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1516)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1485)
at com.mysql.jdbc.RowDataDynamic.close(RowDataDynamic.java:195)
at com.mysql.jdbc.ResultSetImpl.realClose(ResultSetImpl.java:7473)
at com.mysql.jdbc.ResultSetImpl.close(ResultSetImpl.java:881)

After looking at code in MySQL driver I see that driver tries to execute statement:
stmt.executeUpdate("SET net_write_timeout=" + oldValue);

But if transaction is read only (as in my case), this code will throw an exception. I consider this is a bug in the MySQL driver since I do not expect any update statements when I am closing result set.

How to repeat:
I think stacktrace contains enough information to reproduce this bug.

Hi Pavel and thanks for your report.

I do not share your enthusiasm about usability of stack trace so please attach small but complete test case proving your point.

The following code reproduces bug in my case:

connection.setReadOnly(true);
PreparedStatement st = connection.prepareStatement("SELECT 1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
st.setFetchSize(Integer.MIN_VALUE);

ResultSet rs = st.executeQuery();
rs.close();

According to MySQL documentation combination of options ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY and Integer.MIN_VALUE turns on dynamic result set which I use in my code.

Test case, using SVN repository sources and our test fw

Attachment: TestBug38747.java (text/x-java), 1.97 KiB.

Hi Pavel. Verified as described with test case attached.

Besides what's visible in test case, my connection string contains:
&autoReconnect=false&connectTimeout=5000&socketTimeout=30000

We have the same problem with the 5.1.6 driver.  5.0.7 doesn't have the problem.  Appears that setting netTimeoutForStreamingResults=0 is a usable workaround The issue (looking at the source) seems to be that the driver doesn't let itself execute stmt.executeUpdate("SET net_write_timeout=" + oldValue);
Other calls in the driver which issue "SET " statements seem to use the call : executeSimpleNonQuery which does not restrict readonly connections from issuing SET commands.

Fixed for 5.1.7.

An entry was added to the 5.1.7 changelog:

Connector/J threw the following exception when using a read-only connection: 

        java.sql.SQLException: Connection is read-only. Queries leading to data 
        modification are not allowed.