List:Internals« Previous MessageNext Message »
From:Paul McCullagh Date:February 1 2010 3:51pm
Subject:Re: Finding the start of a statement
View as plain text  
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



Thread
Finding the start of a statementPaul McCullagh27 Jan
  • re: Finding the start of a statementMichael Widenius29 Jan
    • Re: Finding the start of a statementMARK CALLAGHAN29 Jan
      • Re: Finding the start of a statementPaul McCullagh1 Feb
        • Re: Finding the start of a statementKristian Nielsen1 Feb
          • Re: Finding the start of a statementPaul McCullagh1 Feb
          • Re: Finding the start of a statementKristian Nielsen11 Feb
        • Re: Finding the start of a statementSergei Golubchik1 Feb
          • Re: Finding the start of a statementPaul McCullagh2 Feb
      • Re: Finding the start of a statementMichael Widenius3 Feb
        • Re: Finding the start of a statementMARK CALLAGHAN18 Feb
          • Re: Finding the start of a statementMARK CALLAGHAN18 Feb
  • Re: Finding the start of a statementSergei Golubchik1 Feb
    • Re: Finding the start of a statementPaul McCullagh1 Feb