From: Kristian Nielsen
Date: February 11 2010 7:50am
Subject: Re: Finding the start of a statement
List-Archive: http://lists.mysql.com/internals/37703
Message-Id: <874olo5hzc.fsf@knielsen-hq.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Kristian Nielsen <knielsen@stripped> writes:

> This made me think that InnoDB must solve the problem of getting statement
> start in some way. But then I did this test:
>
>   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;
>
>   +---+------+
>   | a | b    |
>   +---+------+
>   | 1 |    2 |
>   | 2 |    2 |
>   | 3 |    2 |
>   +---+------+
>
> So here the UPDATE of table t is executed partially and _not_ rolled back!
> The first row is updated, but the second and third rows are not.

I filed this as a bug:

    http://bugs.mysql.com/bug.php?id=51006

 - Kristian.