Bug #115830 `Replace into` may be invalid when functional key is unique
| Submitted: | 14 Aug 2024 2:24 | Modified: | 14 Aug 2024 6:04 |
|---|---|---|---|
| Reporter: | huahua xu | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.39, 8.4.2, 9.0.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[14 Aug 2024 2:24]
huahua xu
[14 Aug 2024 6:04]
MySQL Verification Team
Hello huahua xu, Thank you for the report and test case. regards, Umesh
[20 Oct 8:37]
Knut Anders Hatlen
This appears to have the same cause as bug#121507. The issue is not specific to functional indexes: the index creation sequence can leave InnoDB checking unique-key conflicts in a different order from the SQL layer's index order. The REPLACE implementation uses last_uniq_key() to decide whether it can replace a delete-and-insert operation with an UPDATE. That decision assumes that unique-key conflicts are reported in SQL index order. There is already a handler flag, HA_DUPLICATE_KEY_NOT_IN_ORDER, that disables this optimization for engines that cannot guarantee that ordering. Bug#109445 notes that no engine currently sets it. Having InnoDB return this flag is therefore a candidate fix. It would direct REPLACE through the delete-and-retry path. I have not tested a patch setting the flag. However, I tested both this report's functional-index reproducer and the reduced prefix-index reproducer in bug#121507 with a DELETE trigger present. This also disables the optimization, and both statements then succeed, deleting both conflicting rows.
[20 Oct 8:41]
Knut Anders Hatlen
Bug#121507 has been marked as a duplicate of this report. It includes a reduced reproducer using a prefix unique index rather than a functional index, along with discussion of compatibility considerations.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.070 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.