Description | "MySQL trigger gets executed even if the row hasn't changed. For some reason this has been changed in Mysql version 5.1.24 and 6.0.5 . {quote} Documented in the 5.1.24 and 6.0.5 changelogs as follows: An AFTER UPDATE trigger was not invoked when the UPDATE did not make any changes in the table. Now AFTER UPDATE triggers behave the same in this regard as do BEFORE UPDATE triggers, which are invoked whether the UPDATE makes any changes in the table or not. Also tagged as an important change in behaviour. {quote} quote from http://bugs.mysql.com/bug.php?id=23771 In my environment, some applications enter mass update sql statements and those generate updates for each row even if the row hasn't really changed. For example this sql statement would execute triggers for each row even if all rows has originally ""somestatus=0"": update sometable set somestatus=0; In my case, in 24hrs: select count(*) from SymmetricDS.sym_data where row_data=old_data = 4705848 select count(*) from SymmetricDS.sym_data = 5270157 About 90% of the rows are unnecessary. The problem is in mysql since this change was made http://lists.mysql.com/commits/42102 . There is no way to prevent a update trigger to execute if the row really wasn't modified. SymmetricDS should add an IF statement to compare all fields (IF OLD.field1 <> NEW.field1 OR OLD.field2 <> NEW.field2 ...) before adding the change row to sym_data." |
---|