Bug #31881 A statement is not aborted immediately if an error inside a stored routine
Submitted: 26 Oct 2007 14:49 Modified: 6 Mar 2010 19:59
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0/5.1 OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any

[26 Oct 2007 14:49] Konstantin Osipov
Description:
Statement execution is not aborted immediately if an error happened inside a stored routine.
If an error is not handled, this leads to a minor damage - wrong statistics:

mysql> drop table if exists t1, t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int unique);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> drop function if exists f1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop function if exists f2;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> delimiter |
mysql> 
mysql> create function f1() returns int
    -> begin
    ->   insert into t1 (a) values (1);
    ->   insert into t1 (a) values (1);
    ->   return 1;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> create function f2() returns int
    -> begin
    ->   insert into t2 (a) values (1);
    ->   return 2;
    -> end|
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> 
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select f1(), f2();
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
mysql> show status like 'Com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 3     | 
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

As you can see, although no row is inserted into t2, Com_insert shows that there were 3 inserts. That means that f2() got invoked, INSERT statement got invoked, it did not get to actual ha_write_row but incremented the stats.

How to repeat:
flush status;
drop table if exists t1, t2;
create table t1 (a int unique);
create table t2 (a int);
drop function if exists f1;
drop function if exists f2;

delimiter |

create function f1() returns int
begin
  insert into t1 (a) values (1);
  insert into t1 (a) values (1);
  return 1;
end|
create function f2() returns int
begin
  insert into t2 (a) values (1);
  return 2;
end|
delimiter ;

flush status;
select f1(), f2();
show status like 'Com_insert';
select * from t1;
select * from t2;

Suggested fix:
In select_send::send_data check for thd->net.report_error after calling item->send.
If an error, do not call ::send for the next item.
[26 Oct 2007 16:28] MySQL Verification Team
Thank you for the bug report. Verified as described.
[14 Apr 2008 13:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45336

ChangeSet@1.2634, 2008-04-14 15:47:15+02:00, thek@adventure.(none) +3 -0
  Bug#31881 A statement is not aborted immediately if an error inside a stored routine
  
  Statement execution is not aborted immediately if an error happened inside a stored
  routine.
  
  By moving the error check into the field processing loop, execution will
  be interupted on the first error which occurs in the statement. 
  to
[14 Apr 2008 14:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45349

ChangeSet@1.2634, 2008-04-14 16:43:30+02:00, thek@adventure.(none) +3 -0
  Bug#31881 A statement is not aborted immediately if an error inside a stored routine
  
  Statement execution is not aborted immediately if an error happened inside a stored
  routine.
  
  By moving the error check into the field processing loop, execution will
  be interupted on the first error which occurs in the statement.
[21 Apr 2008 14:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45768

ChangeSet@1.2621, 2008-04-21 17:06:15+02:00, thek@adventure.(none) +3 -0
  Bug#31881 A statement is not aborted immediately if an error inside a stored routine
    
  Statement execution is not aborted immediately if an error happened
  inside a stored routine.
    
  By moving the error check into the field processing loop, execution will
  be interupted on the first error which occurs in the statement.
[22 Apr 2008 9:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45807

ChangeSet@1.2621, 2008-04-22 11:53:07+02:00, thek@adventure.(none) +3 -0
  Bug#31881 A statement is not aborted immediately if an error inside a stored routine
    
  Statement execution is not aborted immediately if an error happened
  inside a stored routine.
    
  By moving the error check into the field processing loop, execution will
  be interupted on the first error which occurs in the statement.
[28 Apr 2008 9:51] Kristofer Pettersson
Patch approved by kostja
[22 May 2008 9:49] Bugs System
Pushed into 6.0.6-alpha
[10 Jul 2008 19:46] Paul DuBois
Noted in 6.0.6 changelog.

Occurrence of an error within a stored routine did not always cause
immediate statement termination.
[20 Nov 2009 23:07] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/91183

2931 Konstantin Osipov	2009-11-21
      Backport the test case for Bug#31881 "A statement is not aborted immediately if an error
       inside a stored routine" from 6.0-codebase.
[25 Nov 2009 13:33] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (version source revid:alik@sun.com-20091124194633-yc0achgq1ioyqzng) (merge vers: 6.0.14-alpha) (pib:13)
[25 Nov 2009 13:34] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091124193905-3iyzegd75k4givuz) (version source revid:kostja@sun.com-20091120230630-g5oe49ie8xkyd972) (merge vers: 5.6.0-beta) (pib:13)
[25 Nov 2009 15:33] Paul DuBois
Noted in 5.6.0 changelog.

Already fixed in 6.0.x.
[6 Mar 2010 11:01] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091125142014-7asc9sj33gzki0ym) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 19:59] Paul DuBois
Moved 5.6.0 changelog entry to 5.5.3.