Announcement Announcement Module
Collapse
No announcement yet.
MySQL procs and read-only transactions Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL procs and read-only transactions

    Hi all

    I'm using Spring 2.0, Hibernate 3.1 and MySQL 5.1

    I have read-only transactions setup for any get* methods in my service package

    eg
    Code:
    <tx:advice id="transactionAdvice" transaction-manager="transactionManager">
        	<tx:attributes>
          		<tx:method name="get*" read-only="true"/>
          		<tx:method name="*" />
    		</tx:attributes>
    	</tx:advice>
    One of my get methods ends up calling a MySQL stored procedure. When this happens I get an exception

    Code:
    java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:713)
    at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:688)
    ...
    After a bit of digging, I noticed the following code in com.mysql.jdbc.PreparedStatement.execute(PreparedS tatement.java:713)

    Code:
    if (this.connection.isReadOnly() && (this.firstCharOfStmt != 'S')) {
       throw SQLError.createSQLException(Messages.getString("PreparedStatement.20") //$NON-NLS-1$
    					+ Messages.getString("PreparedStatement.21"), //$NON-NLS-1$
    					SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
    		}
    So, basically, the mysqlconnector jdbc driver is throwing an exception if a SQL statement from a readonly connection it is executing doesnt start with 'S'. I'm guessing its expecting the word 'SELECT' to be the first word for all readonly statements.

    My problem is I need to call a stored proc, and the statement being executed is something like { call proc_myprocedure(?,?,?) }

    Has anyone out there worked out how I can have a read-only transaction call a MySQL statement that doesnt start with 'S'?

    Any clever alternative approaches I can try?

  • #2
    You might have more luck on the MySQL forums, it's not a clever solution but otherwise I guess you'd just have to remove the readOnly setting.
    Last edited by karldmoore; Aug 29th, 2007, 10:33 AM.

    Comment


    • #3
      It has been a while, but I thought I'd post how to get mysql stored procs to run via a read-only connection

      1. you need at least mysql-connector-java-5.1.7-bin.jar
      2. you need to add READS SQL DATA to your stored proc definition
      3. you need to make sure you dont have "noAccessToProcedureBodies=true" specified in your connection string

      If you do all of the above, mysql recognises your stored procedure is one that doesnt do any updating of data, so it wont throw a "java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed"

      Comment

      Working...
      X