Bug #40031 CallableStatement.execute() should allow CALL on read-only procedures
Submitted: 15 Oct 2008 0:09 Modified: 16 Oct 2008 14:43
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S4 (Feature request)
Version:5.1.7 OS:Any
Assigned to: Todd Farmer CPU Architecture:Any

[15 Oct 2008 0:09] Todd Farmer
Description:
When using the replication driver with autoReconnect=true, C/J checks in PreparedStatement.execute() (also called by CallableStatement.execute()) to determine if the first character of the statement is an "S", in an attempt to block all statements that are not read-only-safe (eg, non-SELECT statements).  However, this also blocks CALLs to stored procedures, even if the stored procedures are defined as SQL READ DATA or NO SQL.

C/J should check whether the stored procedure being called is safe for read-only (by checking the value of the corresponding row's SQL_DATA_ACCESS column in information_schema.routines) and allow processing of stored procedures that are declared as NO SQL or SQL READ DATA.

How to repeat:
Test case attached (note that ConnectionRegressionTest.getMasterSlaveProps() was modified from private to protected).

Suggested fix:
Allow execution of NO SQL or SQL READ DATA defined stored procedures against a read-only slave.
[15 Oct 2008 0:10] Todd Farmer
test case

Attachment: ReadOnlyCallableStatementTest.java (application/octet-stream, text), 2.42 KiB.

[15 Oct 2008 5:33] Todd Farmer
http://lists.mysql.com/commits/56227
[16 Oct 2008 14:43] Tony Bedford
An entry was added to the 5.1.7 changelog:

When using the replication driver with autoReconnect=true, Connector/J checks in PreparedStatement.execute (also called by CallableStatement.execute) to determine if the first character of the statement is an ā€œSā€, in an attempt to block all statements that are not read-only-safe, for example non-SELECT statements. However, this also blocked CALLs to stored procedures, even if the stored procedures were defined as SQL READ DATA or NO SQL.