Function returning “No data - zero rows fetched, selected, or processed” in MySQL 5.5

Question

I have upgraded MySQL version to 5.5.16

After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed.

In those functions, cursors are there, but the control is not even going to the cursor.

I have also used continue Handler as follows,

Declare continue handler for NOT FOUND
Begin
  set Done = 1;
End ;

I have tried using continue handler in following way,

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'

Even after changing it is returning same warning.

Answer 1

You are using 'NOT FOUND' and SQLSTATE '02000'. There were bugs reports that addressed this:

• http://bugs.mysql.com/bug.php?id=42834 (March 5, 2009 : STATUS Closed)
• http://bugs.mysql.com/bug.php?id=55843 (August 9, 2010 : STATUS Closed)
• http://bugs.mysql.com/bug.php?id=59169 (December 25, 2010 : STATUS Duplicate)

Here is something enlightening about the whay MySQL does error handling.

The book MySQL Stored Procedure Programming has a whole chapter on this subject. On your particular problem, you need what's on pages 132,133 under the subheading Handler Conditions.

It states on page 132 that there are three ways to define an error:

• MySQL error code
• ANSI-standard SQLSTATE code
• Named Condition

You must change the code to

Declare continue handler for 1329 
Begin 
  set Done = 1; 
End ; 

I found that error code here : http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html

It says as follows:

Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)

Message: No data - zero rows fetched, selected, or processed

The book I mentioned recommends getting away from ANSI error codes and using the direct MySQL error codes. The reason? Page 133 has a box "SQLSTATE or MySQL Error Code?", which says the following:

In theory, using the SQLSTATE codes will make your code more portable to other database platforms and might therefore seem to be the best choice. Hoewver, there are a number of reasons to use MySQL error codes rather than the SQLSTATE codes when writing MySQL stored programs:

In reality, it is unlikely that you will move your stored programs to another RDBMS. The Oracle and SQL Server stored program languages are totally incompatible with MySQL. The DB2 stored program language is somewhat compatible (both are based on the SQL:2003 standard). It is very likely, however, that you will use MySQL-specific syntax as you write your application, which will prevent your stored code from being portable.

Not all MySQL error codes have SQLSTATE equivalents. Although every MySQL error code is associated with some SQLSTATE error code, often it will be a general-purpose SQLSTATE that is not specfic (such as HY000). Therefore, you will almost certainly have to code some handlers that refer directly to MySQL error codes. You'll probably find that the advantages of using a consistent handler format will outweigh the theorectical portability advantage of SQLSTATE error codes.