MySQL Bugs: #13479: REPLACE activates UPDATE trigger, not the DELETE and INSERT triggers

Bug #13479	REPLACE activates UPDATE trigger, not the DELETE and INSERT triggers
Submitted:	26 Sep 2005 11:16	Modified:	22 Jun 2006 14:39
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.12	OS:	Windows (Windows / *)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
this issue was mentioned in bug #13473, however this bug is now a documentation issue, and not directly related.
So I took the liberty, and open another bug

replace statem,ents trigger "before/after update"

the documentation states that replace, does an insert.
Inserts should not trigger "update".

the behaviour happens with MyIsam and InnoDb

How to repeat:
CREATE TABLE logi (a varchar(200)) ENGINE=InnoDB;
CREATE TABLE ti (a int(11) NOT NULL default 0, b int(11),  PRIMARY KEY  (a))
ENGINE=InnoDB;
create trigger ti1 before insert on ti
for each row insert into logi select concat("before insert ",new.a,new.b);
create trigger ti2 after insert on ti
for each row insert into logi select concat("after insert ",new.a,new.b);
create trigger ti3 before update on ti
for each row insert into logi select concat("before update ",new.a,new.b);
create trigger ti4 after update on ti
for each row insert into logi select concat("after update ",new.a,new.b);

CREATE TABLE logm (a varchar(200)) ENGINE=MyIsam;
CREATE TABLE tm (a int(11) NOT NULL default 0, b int(11),  PRIMARY KEY  (a))
ENGINE=MyIsam;
create trigger tm1 before insert on tm
for each row insert into logm select concat("before insert ",new.a,new.b);
create trigger tm2 after insert on tm
for each row insert into logm select concat("after insert ",new.a,new.b);
create trigger tm3 before update on tm
for each row insert into logm select concat("before update ",new.a,new.b);
create trigger tm4 after update on tm
for each row insert into logm select concat("after update ",new.a,new.b);

insert into ti  select 1,1; delete from logi;
replace into ti values(1,2);
select * from logi;
+------------------+
| a                |
+------------------+
| before insert 12 |
| before update 12 |
| after update 12  |
+------------------+

insert into tm  select 1,1; delete from logm;
replace into tm values(1,2);
select * from logm;
+------------------+
| a                |
+------------------+
| before insert 12 |
| before update 12 |
| after update 12  |
+------------------+

drop table ti; drop table tm; drop table logi;drop table logm;

Suggested fix:
I would expect
"before insert" and "after insert"

Everything works as you described, and I believe, as inteded (but not as described in http://dev.mysql.com/doc/mysql/en/replace.html):

"While the insertion fails because a duplicate-key error occurs for a primary or unique key: 

- Delete from the table the conflicting row that has the duplicate key value 
- Try again to insert the new row into the table"

If this phrase is correct, than you are right, and your example verifies that there is a bug in trigger implementation. But I think, the behaviour in 5.0 simply changed to be more effective (update instead of delete + insert new) and reasonable. For example, there is a similar MERGE statement in Oracle, that is refered to as "upsert" (insert or update, if already exists).

If I am right, the following documentation pages should be updated accordingly:

http://dev.mysql.com/doc/mysql/en/replace.html
http://dev.mysql.com/doc/mysql/en/create-trigger.html
http://dev.mysql.com/doc/mysql/en/using-triggers.html

Let the documentation team do the rest of the verification.

I think this is a server bug

Valeriy Kravchuk said:
====
Everything works as you described, and I believe, as inteded (but not as
described in http://dev.mysql.com/doc/mysql/en/replace.html):

"While the insertion fails because a duplicate-key error occurs for a primary or
unique key: 

- Delete from the table the conflicting row that has the duplicate key value 
- Try again to insert the new row into the table"

If this phrase is correct, than you are right, and your example verifies that
there is a bug in trigger implementation. But I think, the behaviour in 5.0
simply changed to be more effective (update instead of delete + insert new) and
reasonable.
====

That does sound more reasonable, but with my MySQL (v. 5.0.18), a REPLACE that updates an existing row due to a duplicate key shows 2 affected rows (one deleted, one added) whereas one that inserts a new row shows only 1.  The documentation lists this behavior as expected, and as the way to check to see if your REPLACE matched an existing row or not (check for mysql_affected_rows() > 1 or =1).

So even though it would make writing a trigger more complex, it does seem that a REPLACE that matches an existing key should cause an INSERT trigger event on the appropriate table.

If REPLACE's trigger behavior does indeed change in future versions, a way around would be to use ON DUPLICATE KEY UPDATE syntax, which will cause an UPDATE in the case of a key collision.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7619

minor comments sent to Dmitri.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7756

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7770

Fixed in 5.0.23 and 5.1.12

Noted in 5.0.23, 5.1.12 changelogs.

REPLACE statements caused activation of UPDATE triggers, not
DELETE and INSERT triggers.

> Noted in 5.0.23, 5.1.12 changelogs.

> REPLACE statements caused activation of UPDATE triggers, not
> DELETE and INSERT triggers.

I opened another report http://bugs.mysql.com/bug.php?id=22813 related to REPLACE focused on implementation delete+insert vs. update, but I comment here:

5.0.24a -- doc says it is delete+insert, not update and server fires triggers:
delete+insert not an update.

Just checked it. Maybe it was reversed in 24 -- pity.

I would love to see well set replace as update once for good.