MySQL Bugs: #75333: Connector J with Tomcat is Read Only

Bug #75333	Connector J with Tomcat is Read Only
Submitted:	29 Dec 2014 16:30	Modified:	31 Dec 2014 19:28
Reporter:	Mahmoud Youssef	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.25	OS:	Linux (Ubuntu 12.04)
Assigned to:		CPU Architecture:	Any
Tags:	Jasper Server, jdbc, Transaction Read Only

Description:
I am using Jasper Server for reporting and BI. I am using the Connector J 5.1.25 with the Tomcat server. Using a MySQL JDBC data source, I query data to populate the reports. Currently I upgraded one of my MySQL servers to 5.6.22 from 5.5.40. Now, with the JDBC driver, stored procedures that create temporary tables cannot with 5.6.22 as it defaults the transaction session to read only. I've verified this by running a report with "SHOW SESSION VARIABLES LIKE '%tx%';" and this is the results :

tx_isolation		REPEATABLE-READ
tx_read_only		ON

By default the tx_read_only variable should be OFF. The GLOBAL variable of tx_read_only is off. Why does the JDBC driver put this to ON? Is there a way to set the default Connector J transaction mode?

How to repeat:
Query a MySQL JDBC Source that is 5.6.5 and above. SHOW SESSION VARIABLES LIKE '%tx%'; Make sure the global variable is either omitted or set to OFF.

Suggested fix:
"SET TRANSACTION READ WRITE "

This is cumbersome as it is not compatible with versions before 5.6.

Hi Mahmoud,

While both Connector/Java and MySQL have read-only properties, neither are enabled by default. MySQL Server 5.6 added optimizations for read-only workloads, and introduces SET TRANSACTION READ ONLY:

http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

As of Connector/Java 5.1.23, this syntax is used with 5.6 servers when Connection.setReadOnly(true) is called:

"Connection.setReadOnly() will take advantage of server-side support for read-only transactions present in MySQL 5.6 and newer. Calling .isReadOnly() will incur a round-trip if useLocalSessionState is not enabled."

http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-23.html

It seems that something in your code (or libraries you use) is calling Connection.setReadOnly(true), which enforces read-only operations - including those on temporary tables within stored programs. To work around this, you should eliminate calls to setReadOnly(true) when performing operations which do write operations.

It's worth noting that there's significant value in the read-only optimizations, as well as tying the read-only state of the driver to that of the transaction on the server. In earlier versions of Connector/Java, a very basic client-side check was done against the SQL being executed to ensure it was read-only. Basically, the client had to parse the SQL statement, eliminating comments, checking multi-statements, etc. and seeing if it started with an "S" (for "SELECT"). That all goes away when we can trust the server to enforce read-only properties.

Reverting to client-side enforcement of read-only operations - which is what would happen if we use the suggested SET TRANSACTION READ WRITE command - eliminates all of the performance benefits of read-only transactions in MySQL Server 5.6, and re-introduces poor and performance-sapping client-side checking.

If there are reasons that setReadOnly(false) does *NOT* work for your use case, please do let us know so we can consider that in more detail. There are potential use cases which conflict because setReadOnly() is also the trigger for switching between master and slaves in a replication-aware scale-out connection. Stored procedures which create and manipulate data in temporary tables may be safe (even desirable) to execute on a replication slave.