| 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: | |
| 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 | ||
[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.
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;