Announcement Announcement Module
No announcement yet.
MySQL procs and read-only transactions Page Title Module
Move Remove Collapse
Conversation Detail Module
  • 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

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

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

    if (this.connection.isReadOnly() && (this.firstCharOfStmt != 'S')) {
       throw SQLError.createSQLException(Messages.getString("PreparedStatement.20") //$NON-NLS-1$
    					+ Messages.getString("PreparedStatement.21"), //$NON-NLS-1$
    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, 11:33 AM.


    • #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"