List:Internals« Previous MessageNext Message »
From:Kristian Nielsen Date:February 1 2010 11:54am
Subject:Re: Finding the start of a statement
View as plain text  
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.
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