Bug #109445 Handler table flag HA_DUPLICATE_KEY_NOT_IN_ORDER is unused

Submitted: 21 Dec 2022 5:14 Modified: 21 Dec 2022 7:44
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engine API Severity:S3 (Non-critical)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any

[21 Dec 2022 5:14] Laurynas Biveinis
Description:
No storage engine ever sets HA_DUPLICATE_KEY_NOT_IN_ORDER, and this includes 3rd party engines I was able to check. Git history suggests this was never used in Oracle MySQL, it might have been used in other related databases.

How to repeat:
Read source code

Suggested fix:
Remove it
[21 Dec 2022 7:44] MySQL Verification Team
Hello Laurynas,

Thank you for the report and feedback!

regards,
Umesh
[20 Oct 8:29] Knut Anders Hatlen
Bug #115830 and bug #121507 provide examples where the ordering assumption protected by this flag does not hold.

After in-place index creation, InnoDB can check unique-key conflicts in a different order from the SQL layer's index order. REPLACE may then incorrectly conclude that there can be no further conflicts and attempt an UPDATE instead of deleting the conflicting row and retrying the insertion. That UPDATE can fail with ERROR 1062.

In both reproducers, adding a DELETE trigger makes REPLACE succeed. The presence of a DELETE trigger disables this optimization, and the delete-and-retry path correctly removes both conflicting rows.

The observation that no engine sets the flag is still valid. However, these reports suggest that the protection is needed and that having InnoDB advertise this capability should be considered before removing the flag.

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

Page generated in 0.052 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.