MySQL Bugs: #14329: getWarnings() after stored procedure call fails

Bug #14329	getWarnings() after stored procedure call fails
Submitted:	26 Oct 2005 12:28	Modified:	3 Aug 2009 16:22
Reporter:	Noel Evans	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0.13-rc-standard	OS:	Linux (RHEL ES 3 / 2.4.21-15.0.4.EL)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any
Tags:	stored procedure

Description:

Intermittent failure of stmt.getWarnings() after executing a stored procedure query. Stack Trace is 

java.sql.SQLException: ResultSet is from UPDATE. No Data.
        at com.mysql.jdbc.ResultSet.next(ResultSet.java:6091)
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:704)
        at com.mysql.jdbc.SQLError.convertShowWarningsToSQLWarnings(SQLError.java:650)
        at com.mysql.jdbc.Statement.getWarnings(Statement.java:1504)
        at DBQueryer.main(DBQueryer.java:34)

Execution of the class below uncovers this error usually within 20,000 iterations on our test hardware.

Relevant versions are:

OS: Red Hat Enterprise Linux ES release 3 / 2.4.21-15.0.4.EL
Mysql: 5.0.13-rc-standard
Java: 1.4.2_06-b03
JDBC driver: mysql-connector-java 3.1.11

(this is a test case but the problem was discovered via Spring's JdbcTemplate which implicitly calls getWarnings())

How to repeat:

Stored Procedure:

CREATE PROCEDURE spSelectDual()
SELECT 1 FROM DUAL
;

Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
 
public class DBQueryer
{
 
    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static final String JDBC_URL    = "jdbc:mysql://localhost/??databaseName??";
    private static final String JDBC_USER   = "??username??";
    private static final String JDBC_PASS   = "??password??";
 
    private static final String query = "call spSelectDual()";
 
    public static void main(String[] args) throws Throwable {
 
        System.out.println("query:"+query);
 
        Class.forName(JDBC_DRIVER).newInstance();
        Connection conn = DriverManager.getConnection(JDBC_URL,JDBC_USER,JDBC_PASS);
 
        int i = 0;
 
        try
        {
            while (true)
            {
                if (++i % 1000 == 0)
                {
                        System.out.print('k');
                }
                PreparedStatement stmt = conn.prepareStatement(query);
                stmt.executeQuery();
                stmt.getWarnings();
                stmt.close();
            }
        }
        catch (Throwable t)
        {
            System.err.println("\nfailed on iteration: "+i);
            t.printStackTrace();
        }
    } 
}

I was able to repeat this. It seems to be some strange interaction between server-side prepared statements and stored procedures, as the server itself is violating the network protocol and sending more data than the client expects in some cases.

I'm going to talk to the stored procedure and internal execution guys to see what's going on.

In the meantime, the workarounds are to either use CallableStatements (as is expected by the JDBC specfication) to invoke your stored procedures, or disable usage of server-side prepared statements with the "useServerPrepStmts" property of your JDBC connection set to "false".

Is the issue only with this stored procedure, or do arbitrary (or particular) stored procedures exhibit this behavior?

MySQL-5.0 doesn't currently allow you to prepare() a stored procedure call that returns results, it should cause an error. It seems that "SELECT 1 FROM DUAL" doesn't get flagged correctly to cause an error.

In any case, if you want to use stored procedures with JDBC, you either need to use Connection.prepareCall(), or disable server-side prepared statements.

We encounter the issue on a set of stored procedures, all of which are essentially large sql-SELECT wrappers. However none of the procedures contain any OUT or INOUT parameters - could this be why MySQL-5.0 is allowing their preparation?

In any case, we have switched our use of Spring to use the preparedCall() method which appears to resolve the issue. 

Thanks for your help.

http://www.google.com/search?hl=en&q=mysql+stored+procedure&btnG=Google+Search
http://search.yahoo.com/search?p=mysql+stored+procedure&fr=yfp-t-501&toggle=1&cop=mss&ei=U...
http://www.freshprlinks.com/
http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html
http://weblinks.atshya.com/

http://www.linuxlords.net/forum/

I tried the test case and can't repeat the error in 5.4.4.
There are no spurious packets sent to the client.