Bug #38747 java.sql.SQLException: Connection is read-only. Queries leading to data modifica
Submitted: 12 Aug 2008 11:01 Modified: 10 Oct 2008 8:42
Reporter: Pavel Sher Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.6 OS:Any
Assigned to: CPU Architecture:Any
Triage: D3 (Medium)

[12 Aug 2008 11:01] Pavel Sher
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.
[12 Aug 2008 11:06] Tonci Grgin
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.
[12 Aug 2008 13:53] Pavel Sher
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.
[13 Aug 2008 9:03] Tonci Grgin
Test case, using SVN repository sources and our test fw

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

[13 Aug 2008 9:06] Tonci Grgin
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
[21 Aug 2008 18:39] Kevin Fries
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.
[29 Sep 2008 16:14] Mark Matthews
Fixed for 5.1.7.
[10 Oct 2008 8:42] Tony Bedford
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.