Hi Kristian,
This is a good example which seems to illustrate my problem.
The UPDATE statements in f1() are nested in the insert statement:
INSERT INTO t2 VALUES (f1(2));
InnoDB does not "recognize" the boundaries of the nested statements
(possibly due to a bug, as you say).
But, I am wondering if this is at all possible given the current
engine API calls.
On Feb 1, 2010, at 12:54 PM, Kristian Nielsen wrote:
> Paul McCullagh <paul.mccullagh@stripped> writes:
>
>> Yes, it is obscure, and as far as I can tell, this solution does not
>> work when we are dealing with _nested_ statements.
>
> Hm, is it really legal in MySQL to du an UPDATE (/DELETE/INSERT)
> inside a
> function called from an SQL statement?
>
> Indeed, from a quick test it appears so.
>
> From my understanding of how InnoDB is supposed to work, if an update
> statement fails, that _statement_ is rolled back, but not the entire
> transaction. So there is an implicit savepoint at the start of each
> update
> statement.
>
> 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 update, but the second and third rows are not.
>
> Isn't this a bug?
>
> In any case, it seems InnoDB does not set an implicit save point on
> nested
> statements...
>
> - Kristian.
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=1
>
--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com
