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
• Status: Closed
• 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: Windows
• CPU Architecture: Any
• Tags: IGNORE, primary key, trigger, UPDATE

[20 Mar 2013 11:22] Neil Hampton

Description:
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;
end|
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.