Bug #68726 Update trigger invoked when update ignore means that no update is performed
Submitted: 20 Mar 2013 11:22 Modified: 14 Jul 2014 15:28
Reporter: Neil Hampton Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.30 and 5.6.10, 5.1.70, 5.5.32, 5.6.12 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: IGNORE, primary key, trigger, UPDATE
Triage: Needs Triage: D2 (Serious)

[20 Mar 2013 11:22] Neil Hampton
Where a trigger exists for an update on a table, this trigger seems to be invoked for "update ignore" statements where a unique index prevents the update.

The NEW.xxx values in the trigger contain what the update would change the data to if it were allowed to happen.

How to repeat:
drop table cnt;
drop table dat;

create table cnt (cat varchar(50) primary key, rows int);

create table dat (
    ref        varchar(50),
    cat        varchar(50) references cnt (category),
    primary key (cat,ref));

delimiter |
create trigger post_dat after update on dat
for each row begin
   update cnt set rows=rows+1 where cnt.cat = NEW.cat;
   update cnt set rows=rows-1 where cnt.cat = OLD.cat;
delimiter ;

insert into cnt values ('a',1);
insert into cnt values ('b',2);
insert into dat values ('z','a');
insert into dat values ('z','b');
insert into dat values ('y','b');

 * The trigger seems to get invoked regardless of if the update
 * takes place or not 
update ignore dat set cat='a' where cat='b';

 * Expecting the same result from these two queries
select * from cnt;

select cat,count(*) from dat group by cat;

Suggested fix:
Don't invoke the update trigger when an update is not actually done because of key constraints.
[20 Mar 2013 17:41] Sveta Smirnova
Thank you for the report.

Verified as described.
[14 Jul 2014 15:28] Paul Dubois
Noted in 5.7.5 changelog.

UPDATE triggers for a table were invoked even for UPDATE IGNORE
statements for which a unique index caused the update to be ignored.