MySQL Bugs: #51006: InnoDB does not correctly rollback statement on error inside stored function

Bug #51006	InnoDB does not correctly rollback statement on error inside stored function
Submitted:	8 Feb 2010 19:30	Modified:	9 Feb 2010 9:05
Reporter:	Kristian Nielsen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1.42, 5.0, 5.1, 5.5.99	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When a statement executed inside a stored function fails, innodb does not
correctly roll back the partially executed statement, leaving an inconsistent
state in the database.

Normally, a statement that fails partly through is rolled back:

  CREATE TABLE t (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
  INSERT INTO t VALUES (0,0), (2,0), (3,0);
  UPDATE t SET a=a+1;
-> ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
  SELECT * FROM t;
->
+---+------+
| a | b    |
+---+------+
| 0 |    0 |
| 2 |    0 |
| 3 |    0 |
+---+------+

But see below "How to repeat" how this does not work correctly inside a stored
function.

How to repeat:
  CREATE TABLE t (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
  CREATE TABLE t2 (c int) ENGINE=InnoDB;
  INSERT INTO t VALUES (0,0), (2,0), (3,0);

  delimiter |
  CREATE FUNCTION f1(x int) RETURNS int
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @r = 1;
    SET @r = 0;
    UPDATE t SET a=a+1;
    UPDATE t SET b=b+x;
    RETURN @r;
  END|
  delimiter ;

  INSERT INTO t2 VALUES (f1(2));
  SELECT * FROM t;

Result:
+---+------+
| a | b    |
+---+------+
| 1 |    2 |
| 2 |    2 |
| 3 |    2 |
+---+------+

The problem is that the statement "UPDATE t SET a=a+1" first changes a to 1 in
the first row. Then it attempts to change a to 3 in the second row, but fails
due to a uniqueness constraint. And in this case, the statement is not rolled
back correctly, leaving the result of partially executing the statement in the
database.

Suggested fix:
See also this mailing list threads which discusses a possible problem with the storage engine API related to this:

    http://lists.mysql.com/internals/37662

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

See comment "[2 May 2008 17:55] Sveta Smirnova" in bug #36424 for details and workaround

Sveta, thanks for checking into this.

I am sorry, I should have been more clear about exactly what the expected
output should be and how it differs from the actual output.

The bug you refered to is about _rolling back_ a whole transaction using explicit ROLLBACK.

The problem in this bug is about _committing_ a transaction in which some statements succeed and some statements fail.

The relevant page from the documentation seems to be this one:

  http://dev.mysql.com/doc/refman/5.1/en/innodb-error-handling.html

  "According to the standard, any error during an SQL statement should cause rollback of that statement."

  "A duplicate-key error rolls back the SQL statement"

The test case I gave shows an example where a duplicate-key error does _not_ roll back the SQL statement. The statement is "UPDATE t SET a=a+1", and it leaves the first row updated (a=1). The expected result according to the documentation is that the update of a to 1 is rolled back when the statement is rolled back due to duplicate key error. So this is the expected output:

+---+------+
| a | b    |
+---+------+
| 0 |    2 |
| 2 |    2 |
| 3 |    2 |
+---+------+

Note that this differs from the actual output in the value of a in the first row (1 instead of 0).

Using an explicit ROLLBACK as suggested in Bug#36424 would do something different: it would rollback the entire transaction, giving this result:

+---+------+
| a | b    |
+---+------+
| 0 |    0 |
| 2 |    0 |
| 3 |    0 |
+---+------+

Also see this very similar test case, using a stored procedure call instead of a function call inside an INSERT statement. This test case gives the expected results:

  CREATE TABLE t (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
  CREATE TABLE t2 (c int) ENGINE=InnoDB;
  INSERT INTO t VALUES (0,0), (2,0), (3,0);

  delimiter |
  CREATE PROCEDURE p1(x int)
  BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @r = 1;
    SET @r = 0;
    UPDATE t SET a=a+1;
    UPDATE t SET b=b+x;
    INSERT INTO t2 VALUES (@r);
  END|
  delimiter ;

  BEGIN;
  CALL p1(2);
  COMMIT;
  SELECT * FROM t;

+---+------+
| a | b    |
+---+------+
| 0 |    2 |
| 2 |    2 |
| 3 |    2 |
+---+------+

It is quite unexpected that these two very similar examples should produce different results. It also seems to go against what is documented, as the quotes from the manual above show.

As discussed in the (long) email thread refered to above, this seems to be related to the fact that the stored function call appears inside another SQL statement. So the statement "UPDATE t SET a=a+1" is "nested" inside the "INSERT INTO t2 VALUES (f1(2))" statement. This nesting apparently confuses the InnoDB logic that rolls back failing _statements_ (not transactions).

Thank you for the feedback.

You are right: I missed changes in `a` column. Verified as described.

The rollback is blocked in MySQL ha_rollback_trans() per following statement:

ha_rollback_trans()
{
  ...
  if (thd->in_sub_stmt)
  {
    /*
      If we are inside stored function or trigger we should not commit or
      rollback current statement transaction. See comment in ha_commit_trans()
      call for more information.
    */
    if (!all)
      DBUG_RETURN(0);
   ...
}

ha_commit_trans
 @todo
    Since we don't support nested statement transactions in 5.0,
==>    we can't commit or rollback stmt transactions while we are inside
==>    stored functions or triggers. So we simply do nothing now.
    TODO: This should be fixed in later ( >= 5.1) releases.

And the current case is a stored function

1279      if (thd->in_sub_stmt)
(gdb) next
1286        if (!all)
(gdb) next
1287          DBUG_RETURN(0);
(gdb) p thd->in_sub_stmt
$29 = 2

stack:
ha_rollback_trans
ha_autocommit_or_rollback
close_thread_tables
sp_lex_keeper::reset_lex_and_exec_core
sp_instr_stmt::execute
sp_head::execute_function
Item_func_sp::execute_impl 
Item_func_sp::execute 
...
fill_record
fill_record_n_invoke_before_triggers
mysql_insert
mysql_execute_command

If we skip(jump over) this check in the debugger, the rollabck of the update comes out expected:

(gdb) jump 1293
Continuing at 0x74acfd.
Breakpoint 9, row_undo_ins (node=0x2aaaaad998b8) at row/row0uins.c:275
275             row_undo_ins_parse_undo_rec(node);

mysql>  INSERT INTO t2 VALUES (f1(2));
Query OK, 1 row affected (24 min 24.92 sec)

mysql>  select * from t;
+---+------+
| a | b    |
+---+------+
| 0 |    2 |
| 2 |    2 |
| 3 |    2 |
+---+------+
3 rows in set (4.58 sec)

So this rollback is intentionally being blocked for a stored function, and is clearly stated in the code for ha_commit_trans():

    "we can't commit or rollback stmt transactions while we are inside
    stored functions or triggers. So we simply do nothing now."

So it should send back to MySQL to evaluate further action (better documenting such behavior etc.)

Thanks
Jimmy

High risk since taking savepoint for each statement in a stored function/trigger may involve a significant overhead.

Workaround: set a savepoint explicitly.

Bug #69872 "UPDATE in stored function is not rolled back" was marked as a duplicate of this bug report.