| Bug #87371 | Bug in "INSERT... ON DUPLICATE KEY UPDATE" query | ||
|---|---|---|---|
| Submitted: | 10 Aug 2017 11:42 | Modified: | 5 Oct 2017 18:16 |
| Reporter: | Akash Panchal | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 5.7.17 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[10 Aug 2017 11:43]
Akash Panchal
Test schema file used to reproduce the bug
Attachment: test_schema.sql (application/octet-stream, text), 1.99 KiB.
[11 Aug 2017 13:54]
MySQL Verification Team
Hi! Can you tell me if trigger works if you set first trigger to work BEFORE INSERT ...... Thanks in advance .....
[14 Aug 2017 5:17]
Akash Panchal
Hi Sinisa, We have an issue with the trigger for AFTER UPDATE... Though I tried putting BEFORE instead of AFTER in both the trigger, it worked for all four cases. But we want "AFTER UPDATE" trigger to work while using "Insert into ...on Duplicate key" (updating same value - case 2.b) Best Regards, Akash
[14 Aug 2017 13:13]
MySQL Verification Team
Hi! I have tested your case and I have repeated it. This is a strange situation, so I am filing this as both a code bug and documentation bug. This is due to the fact that it will take some time to fix the behaviour. There is inconsistency in UPDATE triggers which are executed by UPDATE are executed even if the update row to the same value and i case of UPDATE triggers which are executed as result of INSERT ON DUPLICATE KEY UPDATE are executed only if value is changed. However, INSERT ON DUPLICATE KEY is a bit special in any case, because it also invokes BEFORE INSERT triggers first so at least this is valid documentation request,. It must be considered as a valid code bug due to the inconsistency involved. Thank you.
[16 Aug 2017 8:07]
Akash Panchal
Thanks for the confirmation. Best Regards, Akash
[5 Oct 2017 18:16]
Paul DuBois
Posted by developer: Fixed in 5.7.21, 8.0.4. AFTER UPDATE triggers were not invoked for INSERT ... ON DUPLICATE KEY UPDATE when the value to be updated and the new value were the same.
Description: Given: There is a trigger on update statement, that updates the primary table if there are changes in the secondary table. While using "INSERT... ON DUPLICATE KEY UPDATE" query if the query updates the same value in a column the trigger does not execute. But if "UPDATE `table_name'" updates the same value in a column the trigger executes. How to repeat: (The schema file is attached with this.) Please consider the following cases that describe the problem statement. Case 1. "UPDATE table" query case a. Update the value of a column to something else. > UPDATE `exam` set score = 40 where `studentid` = 1100; event: Trigger executes. (As expected) case b. Update the value of a column to the same value. > UPDATE `exam` set score = 40 where `studentid` = 1100; event: Trigger executes. (As expected) Case 2. "INSERT INTO...ON DUPLICATE KEY UPDATE" query case a. Update the value of a column to something else. > INSERT INTO `exam` (`studentid`) values (1100) ON DUPLICATE KEY UPDATE `score` = 50; event: Trigger executes. (As expected) case b. Update the value of a column to the same value. > INSERT INTO `exam` (`studentid`) values (1100) ON DUPLICATE KEY UPDATE `score` = 50; ################################ event: Trigger does not execute. ################################