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
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);
CREATE FUNCTION f1(x int) RETURNS int
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 (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