MySQL Bugs: #11746: Stored Procedures: Update failure not handled by any of the error handlers

Bug #11746	Stored Procedures: Update failure not handled by any of the error handlers
Submitted:	5 Jul 2005 20:23	Modified:	15 Sep 2005 17:43
Reporter:	sujay koduri	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0.4 beta, 5.0.13-BK	OS:	Linux (Red Hat Linux 9.0)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
In a stored procedure, if we try to update a row which is non-existing, there should be some way to find whether the update call has succeded or not. If it is a failure, it should be handled by one of the error handlers. But none of them are able to catch it. 

How to repeat:
I include  a sample stored proc that falls into this situation.

CREATE TABLE g1(COL INTEGER);

create procedure unit_swap()
BEGIN
  DECLARE b INT DEFAULT 0;
  DECLARE c INT;
  DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING SET b = 1;

     UPDATE g1 SET SOME='CCCC' WHERE COL='999';//Assuming Table 'g1' is empty
     IF b = 1 THEN //It should come here, but not coming
         INSERT INTO G2 (COL) VALUES ('999');
     END IF;

     COMMIT;

END;

Suggested fix:

when Update statement returns '0 rows affected', let that get handled by 'NOT FOUND' error handler.

miguel@hegel:/share/dbs/5.0$ bin/mysql -uroot db7
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 5.0.9-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE G2(COL INTEGER);
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter $$
mysql> create procedure unit_swap()
    -> BEGIN
    ->   DECLARE b INT DEFAULT 0;
    ->   DECLARE c INT;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING SET b = 1;
    -> 
    ->      UPDATE G2 SET SOME='CCCC' WHERE COL='999';
    ->      IF b = 1 THEN 
    ->          INSERT INTO G2 (COL) VALUES ('999');
    ->      END IF;
    -> 
    ->      COMMIT;
    -> 
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call unit_swap();
Query OK, 0 rows affected (0.02 sec)

mysql> select * from G2;
+------+
| COL  |
+------+
|  999 |
+------+
1 row in set (0.01 sec)

The stored proc which we looked at is working because the update statement has a syntax error and that getscaught by SQLEXCEPTION. I am including two more procedures where UPDATE statement has no error but it returns '0 rows updated'. Here the NOT FOUND handler is not catching this.

Stored Proc 1:

mysql> create table stats (ss varchar(10), id varchar(10), count INTEGER, PRIMARY KEY(id))//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE G2(COL INTEGER)//
Query OK, 0 rows affected (0.02 sec)

mysql> create procedure unit_swap()
    -> BEGIN
    ->   DECLARE b INT DEFAULT 7;
    ->   DECLARE c INT;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING SET b = 1111;
    ->
    ->      UPDATE stats SET ss='CCCC' WHERE id='999';
    ->  IF b = 1111 THEN
    ->         INSERT INTO stats (ss, id, count) VALUES ('ABCD', '111', b);
    ->  END IF;
    ->  COMMIT;
    ->
    -> END;//
Query OK, 0 rows affected (0.00 sec)

mysql> call unit_swap()//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stats//
Empty set (0.00 sec)

mysql>

The UPDATE simply doesn't raise any condition, so there's nothing for the handler to catch.

Possibly this should be regarded as a bug in UPDATE, or a feature request.

Verified on 5.0.13-BK also:

mysql> delimiter //
mysql> create table stats (ss varchar(10), id varchar(10), count INTEGER,
    -> PRIMARY KEY(id))//
Query OK, 0 rows affected (0,03 sec)

mysql> CREATE TABLE G2(COL INTEGER)//
Query OK, 0 rows affected (0,01 sec)

mysql> create procedure unit_swap()
    -> BEGIN
    ->    DECLARE b INT DEFAULT 7;
    ->   DECLARE c INT;
    ->    DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING SET b=1111;
    ->
    ->   UPDATE stats SET ss='CCCC' WHERE id='999';
    ->    IF b = 1111 THEN
    ->     INSERT INTO stats (ss, id, count) VALUES ('ABCD', '111', b);
    ->    END IF;
    ->   COMMIT;
    -> END;//
Query OK, 0 rows affected (0,08 sec)

mysql> call unit_swap()//
Query OK, 0 rows affected (0,02 sec)

mysql> select * from stats//
Empty set (0,00 sec)

mysql> select version()//
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0,00 sec)

It looks like a bug when compared to quotes from Standard. But note, that Oracle currently works just as MySQL does:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> CREATE TABLE G2(COL INTEGER);

Table created.

SQL> create table stats1 (ss varchar(10), id varchar(10) primary key, count INTEGER);

Table created.

SQL> set serveroutput on;

SQL> create or replace procedure unit_swap as
  2    b int := 7;
  3    c int;
  4  BEGIN
  5    UPDATE stats1 SET ss='CCCC' WHERE id='999';
  6  EXCEPTION
  7    when others then
  8      dbms_output.put_line(SQLERRM);
  9      INSERT INTO stats1 (ss, id, count) VALUES ('ABCD', '111', b);
 10  END;
 11  /

Procedure created.

SQL> exec unit_swap;

PL/SQL procedure successfully completed.

SQL> select * from stats1;

no rows selected

So, as one can see, no exceptions were raised. It seems reasonable to me, because in Oracle worls NO_DATA_FOUND is raised only in SELECT ... INTO statement. Update of any number of rows (including 0) is OK, why not?

WL#4194