MySQL Bugs: #42834: SELECT ... INTO Statement and Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO

Bug #42834	SELECT ... INTO Statement and Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Submitted:	13 Feb 2009 15:04	Modified:	5 Mar 2009 14:20
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
My mysql error log and windows application events grows unexpectedly large as a result of a missed point about the "SELECT ... INTO Statement".

How to repeat:
N/A

Suggested fix:
Adding the following notes to the documentation of "SELECT ... INTO Statement" may help the ones like me about the nasty surprise.

"SELECT ... INTO" form of acquiring variable values may cause 
"Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
 No data - zero rows fetched, selected, or processed" 
warning which is written everywhere (error log, event log, etc.).

There is nothing wrong about this warning. It points to a case that you "may" have not been ready to handle gracefully. See the example:
BEGIN
  DECLARE COL_A INT;
  ...
  -- say at this point COL_A is 1
  SELECT A INTO COL_A FROM T WHERE ID = 22; -- say there is no such a row
  -- in this case COL_A is still 1 !
  IF COL_A THEN
    -- statements here can cause unexpected results.
  END IF; 
END;

There are three ways to handle this situation while supressing the (Error: 1329) warnings :

1.a. Use SET statement. Most recommended in case of a single variable :
BEGIN
  DECLARE COL_A INT;
  ...
  -- say at this point COL_A is 1
  SET COL_A = (SELECT A INTO FROM T 
    WHERE ID = 22); -- say there is no such a row
  -- in this case COL_A is NULL
  IF COL_A THEN
    -- execution follows the designed path here ..
  END IF; 
END;

1.b. Use RETURN instead of SET at function return point :
BEGIN
  RETURN (SELECT A INTO FROM T 
    WHERE ID = 22); -- say there is no such a row
  -- in this case return value is NULL
END;

2. Use CONDITON HANDLER. Recommended and suitable for multiple variables :
BEGIN
  DECLARE COL_A, COL_B INT;
  ...
  -- say at this point COL_A is 1, COL_B is undefined yet
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 
      BEGIN -- handle the ER_SP_FETCH_NO_DATA case gracefully :
        SET COL_A = NULL; 
        SET COL_B = NULL; 
      END;
    SELECT A, B INTO COL_A, COL_B FROM T 
      WHERE ID = 22; -- say there is no such a row
  END;
  -- in this case COL_A is NULL
  IF COL_A THEN
    -- execution follows the designed path here ..
  END IF; 
END;

3. Use DO (). Suitable for multiple variables but not very explanatory for code reading and maintenance:
BEGIN
  DECLARE COL_A, COL_B INT;
  ...
  -- say at this point COL_A is 1, COL_B is undefined yet
  -- prepare the relevant variables beforehand : 
  SET COL_A = NULL; 
  SET COL_B = NULL; 

  DO ( SELECT A, B INTO COL_A, COL_B FROM T 
    WHERE ID = 22 ); -- say there is no such a row
  -- in this case COL_A is NULL
  IF COL_A THEN
    -- execution follows the designed path here ..
  END IF; 
END;

Thank you for the documentation request. I agree that http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html definitely needs information about possible error messages.

Actually, can you, please, send your my.ini file content and complete example of stored procedure that leads to  error messages with code 1329 being written to error log and windows application events log? I can not repeat it. All I get in simple cases is just a warning:

mysql> create procedure p ()
    -> begin
    ->   declare a int;
    ->   select c1 into a from t2 where c1 = 500;
    -> end;
    -> //
Query OK, 0 rows affected (0.27 sec)

mysql> call p()//
Query OK, 0 rows affected, 1 warning (0.17 sec)

mysql> show warnings//
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

that is NOT logged anywhere.

Regarding the generated log entries, it is my bad - they show up by the Event Scheduler. Here is how-to-repeat script :

DROP DATABASE IF EXISTS `testerr1329`;
CREATE DATABASE `testerr1329`;
USE `testerr1329`;

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `A` INTEGER NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM;

DELIMITER $$

DROP FUNCTION IF EXISTS `f` $$
CREATE FUNCTION `f` () RETURNS INT
  DETERMINISTIC
BEGIN
  DECLARE COL_A INT;
  SET COL_A = 1;
  SELECT `A` INTO COL_A FROM `t` WHERE `ID` = 22; -- there is no such a row
  -- in this case COL_A is still 1 !
  RETURN COL_A;
END $$

DROP PROCEDURE IF EXISTS `p` $$
CREATE PROCEDURE `p` ()
BEGIN
  DECLARE COL_A INT;
  SET COL_A = 1;
  SELECT `A` INTO COL_A FROM `t` WHERE `ID` = 22; -- there is no such a row
  -- in this case COL_A is still 1 !

  -- IF COL_A THEN
  --   SELECT 'UNINTENDED CODE EXECUTION !';
  -- ELSE
  --   SELECT 'Intended code execution';
  -- END IF;
END $$

DROP EVENT IF EXISTS `e` $$
CREATE EVENT `e`
ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01'
DISABLE
DO
BEGIN
  -- Both `p`() and `f`() generate an error log entry . So select either one :

  CALL `p`();
  -- DO `f`();
END $$

DELIMITER ;

ALTER EVENT `e` ENABLE;
DO SLEEP(5); -- wait for some 1329 logs generated ..
ALTER EVENT `e` DISABLE;

DROP DATABASE IF EXISTS `testerr1329`;

Since the event scheduler has no other interface than the error log, there is nothing wrong with this behaviour.

But in practice, database designers should still do their best to suppress the "expected" Error#1329 messages.
Some can inadvertently add an event that pop ups frequently and refers to such a stored procedure/function - flooding the logs.

Besides, Error#1329 has a history of some troubles.

About un/expected execution path issue :

Some database designers thing that if no rows found, "SELECT ... INTO Statement" sets the variables listed in the INTO part to NULL. This is simply not true. At least current MySQL 5.1 behaivour is not touching those variables in this case. I doubt this would change in MySQL 5.1 since it is in the General Availibity phase. Confused database designers should be enlightened by the reference manual.

I think the three samples given in the original post could be a good guide.

OK, so we do not have unexpected server behaviour here. Just need clarifications in the manual about both too many rows and no data found conditions possible with SELECT ... INTO.

To sum up, the "SELECT ... INTO" statement manual needs
- clarifications about possible too many rows and no data found conditions,
- a note that states, "Beware of a flood risk in the error-log : Event scheduler logs all the warnings including the one in unhandled-no-data-found case (Error#1329).", and
- (optionally) best practises to set "Variables in Stored Programs" by a SELECT query to always get the intended result.

Yet another my fault. The following is invalid :

"3. Use DO (). Suitable for multiple variables but not very explanatory for code reading and maintenance"

As it produce "Error#1241: Operand should contain 1 column(s)" when applied to SELECT ... INTO statement having multiple variables.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Modified description in
http://dev.mysql.com/doc/refman/5.1/en/select.html:

The INTO clause can name a list of one or more variables, which can
be user-defined variables, or parameters or local variables within a
stored function or procedure body (see ). The selected values are
assigned to the variables. The number of variables must match the
number of columns. The query should return a single row. If the query
returns no rows, a warning with error code 1329 occurs (No data), and 
the variable values remain unchanged. If the query returns multiple
rows, error 1172 occurs (Result consisted of more than one row). If
it is possible that the statement may retrieve multiple rows, you can
use LIMIT 1 to limit the result set to a single row.

In the context of such statements that occur as part of events
executed by the Event Scheduler, diagnostics messages (not only
errors, but also warnings) are written to the error log, and, on
Windows, to the application event log. For additional information,
see http://dev.mysql.com/doc/refman/5.1/en/events-status-info.html.

Similar changes were made to http://dev.mysql.com/doc/refman/5.1/en/select-into-statement.html.

Added to http://dev.mysql.com/doc/refman/5.1/en/events-status-info.html:

For SELECT ... INTO var_list statements, if the query returns
multiple rows, error 1172 occurs (Result consisted of more than one
row). In the context of such statements that occur as part of events
executed by the Event Scheduler, diagnostics messages (not only
errors, but also warnings) are written to the error log, and, on
Windows, to the application event log. For frequently executed
events, it is possible for this to result in many logged messages.
For either condition, you can avoid this problem by declaring a
condition handler; seehttp://dev.mysql.com/doc/refman/5.1/en/declare-handler.html. For
statements that may may retrieve multiple rows, another strategy is
to use LIMIT 1 to limit the result set to a single row.

Two minor issues :

1. Addition into http://dev.mysql.com/doc/refman/5.1/en/select.html
made between
  "If you use INTO DUMPFILE instead of INTO OUTFILE, .."
  and 
  "Note | Any file created by INTO OUTFILE or INTO DUMPFILE is .."
It is better to reposition the insertion after the "Note ..".

2. Addition into http://dev.mysql.com/doc/refman/5.1/en/events-status-info.html
should include 1329 as the ones made into "SELECT" and "SELECT ... INTO var_list".

Thanks Valeriy and Paul.

I agree that http://dev.mysql.com/doc/refman/5.1/en/events-status-info.html (and 6.0 version of it) should be also changed to mention 1329.

Thanks, I've made those changes. They'll show up after the next docs rebuild.