[MDEV-33083] "AFTER UPDATE" trigger does not fire when duplicate inserted with "ON DUPLICATE KEY UPDATE" Created: 2023-12-20  Updated: 2024-01-03

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.6.16, 11.0.4
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2

Type: Bug Priority: Major
Reporter: Phil Sumner Assignee: Vladislav Vaintroub
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File inert on update mariadb confusion.txt     File on duplicate key triggers.sql     Text File on duplicate key triggers.txt    

 Description   

According to https://mariadb.com/kb/en/trigger-overview/ - the following triggers should fire when an ON DUPLICATE KEY insert is used and a duplicate is detected:

BEFORE INSERT;
BEFORE UPDATE;
AFTER UPDATE.

We're seeing that the AFTER UPDATE trigger never fires when a duplicate row is inserted. See attached test case and output.

Tested on 10.6.16 and 11.0.4

 Comments   
Comment by Alice Sherepa [ 2023-12-20 ]

This is because you are setting again the same value of Name = 'Test Unique Key' - so update was not actually performed.

MariaDB [test]> UPDATE testing.duplicatekey SET Name = 'Test Unique Key2' WHERE Name = 'Test Unique Key';
 
Query OK, 1 row affected (0,013 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
MariaDB [test]> SELECT * FROM testing.duplicatekey_log;
 
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| id | description                                                                                                                                                                                                                                           |
 
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
|  1 | Before Insert into duplicatekey : id: 0, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 13:06:59                                                                                                                                                 |
 
|  2 | After Insert into duplicatekey : id: 1, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 13:06:59                                                                                                                                                  |
 
|  3 | Before Insert into duplicatekey : id: 0, NEW.Name: Test Unique Key, NEW.Modified: 2023-12-20 13:06:59                                                                                                                                                 |
 
|  4 | Before Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 13:06:59, OLD.Modified : 2023-12-20 13:06:59, duplicatekey.Modified : 2023-12-20 13:06:59  |
 
|  5 | Before Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 13:06:59, OLD.Modified : 2023-12-20 13:06:59, duplicatekey.Modified : 2023-12-20 13:06:59  |
 
|  6 | After Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 13:06:59, OLD.Modified : 2023-12-20 13:06:59, duplicatekey.Modified : 2023-12-20 13:06:59   |
 
|  7 | Before Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key2, duplicatekey.Name : Test Unique Key, NEW.Modified: 2023-12-20 13:07:27, OLD.Modified : 2023-12-20 13:06:59, duplicatekey.Modified : 2023-12-20 13:06:59 |
 
|  8 | After Update on duplicatekey : id: 1, OLD.Name: Test Unique Key, NEW.Name: Test Unique Key2, duplicatekey.Name : Test Unique Key2, NEW.Modified: 2023-12-20 13:07:27, OLD.Modified : 2023-12-20 13:06:59, duplicatekey.Modified : 2023-12-20 13:07:27 |
 
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
8 rows in set (0,001 sec)

Comment by Phil Sumner [ 2024-01-03 ]

I haven't checked this work, but the company dev who provided the initial test case has sent the latest attachment (inert on update mariadb confusion.txt) and said the following:

```
you might expect that when the insert into on duplicate key doesn't actually update anything then it's reasonable that the after update trigger doesn't fire.

However a standard update that does the same, does fire the after update trigger.

Am I wrong in thinking that this behaviour is conflicting?
```

This behaviour also doesn't seem to be documented anywhere.

Comment by Sergei Golubchik [ 2024-01-03 ]

AFTER UPDATE trigger should fire, whether the new value is the same as the old or not. SQL standard describes precisely when a trigger should fire, and it doesn't even have the notion of "oh, the values are the same so it's kind of not an update".

So, it's a bug.

Generated at Thu Feb 08 10:36:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.