MySQL Bugs: #25981: editing 1 row via QB causes update trigger to fire multiple times

Bug #25981	editing 1 row via QB causes update trigger to fire multiple times
Submitted:	31 Jan 2007 15:25	Modified:	5 Feb 2007 8:41
Reporter:	Hartmut Grosser	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.2.8/1.2.9	OS:	Windows (Win XP)
Assigned to:		CPU Architecture:	Any

Description:
If you edit several columns of the same row in a result set via the Edit-Button and after that press "Apply changes", an existing AFTER-UPDATE-Trigger for this table is fired multiple times (one time for each affected column).

It seems that QB invokes separate UPDATE statements (one for each changed column).

This will be often not that, what people who create update triggers will expect and leads to a couple of problems for users needing update triggers.

How to repeat:
 - create any table with a handfull of columns
 - define an AFTER-UPDATE-Trigger for that table
 - edit several columns in the same row of that table via QB and when ready press "Apply changes"
 - check how often the update trigger was fired

Suggested fix:
Please decide whether you classify this as an error or a "design problem" in QB.
For each edited row, only 1 UPDATE statement should be executed, causing an UPDATE-Trigger to be fired only 1 time per changed row.

Thanks a lot.

Thank you for the bug report. Below the general query log when QB updates
3 columns in the same row:

070131 14:04:40	      1 Query       UPDATE `xtest`.`xtab` SET `col1`='aa' WHERE `id`='1'
		      1 Query       UPDATE `xtest`.`xtab` SET `col2`='bb' WHERE `id`='1'
		      1 Query       UPDATE `xtest`.`xtab` SET `col3`='cc' WHERE `id`='1'

I'm sorry but currently we cannot fix it. We don't have client side cursors so it is complicated to correctly update column values from a list of changes. Hence we have this split, with the result you see now. Btw, at the time this code was designed there were no triggers available, so we would need a major overhaul of that part and needed client side cursors. As long as we don't have, I'm afraid, we cannot fix this problem.

Hallo Mike,
I understand that implementing client side cursors would make a lot of work for this, but what about collecting the changed columns in an array and then assembling 1 SQL-statement for each changed row like this:
update mytable
set changed_column_1 = new_value_1,
    changed_column_2 = new_value_2,
    ...
    changed_column_n = new_value_n
where primary_key = ...;

This would result in one simple statement for each changed row without needing client side cursors.