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
Triage: Triaged: D3 (Medium)

[26 Oct 2005 12:28] Noel Evans
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();
        }
    } 
}
[26 Oct 2005 13:40] Mark Matthews
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".
[26 Oct 2005 19:12] Mark Matthews
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.
[28 Oct 2005 9:06] Noel Evans
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.
[26 Sep 2007 5:19] null ded
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/
[17 Feb 2008 6:31] sdsfce sdsfce
http://www.linuxlords.net/forum/
[3 Aug 2009 16:23] Konstantin Osipov
I tried the test case and can't repeat the error in 5.4.4.
There are no spurious packets sent to the client.