Bug #27006 AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY UPDATE
Submitted: 9 Mar 2007 16:19 Modified: 20 Mar 2007 16:34
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.36 OS:Any (All)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: bfsm_2007_03_15, ON DUPLICATE KEY UPDATE, regression, triggers

[9 Mar 2007 16:19] Mark Leith
Description:
AFTER UPDATE triggers no longer fire on INSERT .. ON DUPLICATE KEY UPDATE statements - when the UPDATE portion of the statement is activated due to a duplicate key value. 

This is a regression. 

On 5.0.36:

mysql> INSERT INTO foo (id,testvalue) VALUES (1,'test1');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM foo;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test1     | 
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bar;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test1     | 
+----+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> INSERT INTO foo (id,testvalue) VALUES (1,'test2') ON DUPLICATE KEY UPDATE testvalue=VALUES(testvalue);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> SELECT * FROM foo;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test2     | 
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bar;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test1     |   <---- Note still only test1, should be a test2 row as well
+----+-----------+
1 row in set (0.00 sec)

On 5.0.34:

mysql> INSERT INTO foo (id,testvalue) VALUES (1,'test1');
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> SELECT * FROM foo;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test1     | 
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bar;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test1     | 
+----+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> INSERT INTO foo (id,testvalue) VALUES (1,'test2') ON DUPLICATE KEY UPDATE testvalue=VALUES(testvalue);
Query OK, 2 rows affected (0.00 sec)

mysql> 
mysql> SELECT * FROM foo;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test2     | 
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM bar;
+----+-----------+
| id | testvalue |
+----+-----------+
|  1 | test1     | 
|  2 | test2     | 
+----+-----------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.0.34-enterprise-gpl | 
+-----------------------+
1 row in set (0.00 sec)

How to repeat:
GRANT ALL ON *.* TO triggeruser@localhost;

use test;
CREATE TABLE `foo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `testvalue` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `bar` like `foo`;

DROP TRIGGER test.foo_a_insert;

DELIMITER |

CREATE DEFINER='triggeruser'@'localhost' TRIGGER foo_a_insert AFTER INSERT ON test.foo FOR EACH ROW
BEGIN
    insert into test.bar(id,testvalue) values (NULL,new.testvalue);
END
|

DELIMITER ;

DROP TRIGGER test.foo_a_update;

DELIMITER |

CREATE DEFINER='triggeruser'@'localhost' TRIGGER foo_a_update AFTER UPDATE ON test.foo FOR EACH ROW
BEGIN
    insert into test.bar(id,testvalue) values (NULL,new.testvalue);
END
|

DELIMITER ;

INSERT INTO foo (id,testvalue) VALUES (1,'test1');

SELECT * FROM foo;
SELECT * FROM bar;

INSERT INTO foo (id,testvalue) VALUES (1,'test2') ON DUPLICATE KEY UPDATE testvalue=VALUES(testvalue);

SELECT * FROM foo;
SELECT * FROM bar;
[16 Mar 2007 14:25] Bugs System
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/22138

ChangeSet@1.2485, 2007-03-16 17:23:26+03:00, evgen@moonbone.local +3 -0
  Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE KEY
  UPDATE if the row wasn't actually changed.
  
  This bug was caused by fix for bug#19978. It causes AFTER UPDATE triggers
  not firing if a row wasn't actually changed by the update part of the
  INSERT .. ON DUPLICATE KEY UPDATE.
  
  Now triggers are always fired if a row is touched by the INSERT ... ON
  DUPLICATE KEY UPDATE.
[19 Mar 2007 20:07] Paul Dubois
Noted in 5.0.40, 5.1.17 changelogs.

AFTER UPDATE triggers were not activated if a row was not actually
changed by the update part of an INSERT ... ON DUPLICATE KEY UPDATE
statement.

Also added notes about this to 5.0.36, 5.0.38, 5.1.16 changelogs.
[19 Mar 2007 21:05] Bugs System
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/22305

ChangeSet@1.2486, 2007-03-20 00:04:04+03:00, evgen@moonbone.local +3 -0
  sql_insert.cc:
    Removed wrong fix for the bug#27006.
  trigger.test, trigger.result:
    Corrected test case for the bug#27006.
[19 Mar 2007 21:12] Bugs System
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/22306

ChangeSet@1.2486, 2007-03-20 00:10:40+03:00, evgen@moonbone.local +3 -0
  sql_insert.cc:
    Removed wrong fix for the bug#27006.
    The bug was added by the fix for the bug#19978 and fixed by Monty on 2007/02/21.
  trigger.test, trigger.result:
    Corrected test case for the bug#27006.
[19 Mar 2007 21:25] Bugs System
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/22309

ChangeSet@1.2486, 2007-03-20 00:24:08+03:00, evgen@moonbone.local +4 -0
  sql_insert.cc, sql_class.h:
    Removed wrong fix for the bug#27006.
    The bug was added by the fix for the bug#19978 and fixed by Monty on 2007/02/21.
  trigger.test, trigger.result:
    Corrected test case for the bug#27006.
[19 Mar 2007 21:47] Bugs System
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/22312

ChangeSet@1.2486, 2007-03-20 00:46:19+03:00, evgen@moonbone.local +3 -0
  sql_insert.cc:
    Removed wrong fix for the bug#27006.
    The bug was added by the fix for the bug#19978 and fixed by Monty on 2007/02/21.
  trigger.test, trigger.result:
    Corrected test case for the bug#27006.
[20 Mar 2007 16:34] Paul Dubois
Correction to previous changelog note.

Noted in 5.0.38, 5.1.17 changelogs.

AFTER UPDATE triggers were not activated if a row was not actually
changed by the update part of an INSERT ... ON DUPLICATE KEY UPDATE
statement.

Also added notes about this to 5.0.36, 5.1.16 changelogs.
[21 Mar 2007 15:09] Paul Dubois
Updated changelog entry.

AFTER UPDATE triggers were not activated by the update part of INSERT
... ON DUPLICATE KEY UPDATE statements.
[20 Apr 2007 13:44] Paul Dubois
The bugfix for Bug#27006 also fixes Bug#27210.