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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.095 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.