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)
• Status: Closed
• 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.