bulkCreate with updateOnDuplicate/ignoreDuplicates assigns wrong primary keys to returned instances on MySQL/MariaDB

Issue

On MySQL and MariaDB, Model.bulkCreate() synthesises the primary keys of returned instances from insertId and affectedRows (packages/mysql/src/query.js:116-127, and the MariaDB equivalent). That arithmetic assumes one inserted row per submitted record.

updateOnDuplicate and ignoreDuplicates break that assumption: MySQL reports affectedRows as 2 per updated row and 0 per ignored row, so the synthesised id range desynchronises from the instance array. Returned instances are then handed primary keys belonging to other rows — including rows outside the batch entirely.

Because the instances come back with isNewRecord === false, every downstream instance method trusts that key.

Reproduction

Demonstrated on MySQL 8.0.19 and MariaDB 10.11 against @sequelize/core@7.0.0-alpha.48. No fields option and no unusual input are required — this is reachable through ordinary application code:

• instances[0].destroy() deleted a different record's row.
• In an all-collide batch, an innocent instance's update() overwrote a pre-existing row outside the batch.

Why this is being filed separately

This was found while investigating a related fields-allowlist defect in bulkCreate (tracked privately). That one has a one-line fix — passing attributes: options.fields to build, mirroring create() — but that fix does not address this. It only prevents a caller-supplied primary key from surviving; here the wrong key is synthesised by Sequelize itself, so the fix merely changes attacker-chosen misidentification into unchosen misidentification.

It needs no untrusted input to trigger, so this reads as a data-integrity bug rather than a security issue — but silent wrong-row destroy() and update() are severe enough to want their own tracking.

Related

• The dialect carve-out at packages/core/src/model.js:2517-2521 skips replacing a truthy primary key on ['mysql','mariadb'], and dates to #11307 (Aug 2019). It compensates for the same underlying limitation but is written as a dialect allowlist rather than a capability check — see below.
• #17983 (fix(core): ensure bulkInsert returns array of inserted IDs) touches adjacent code but addresses the return shape, not this desync.

Two things worth fixing alongside

1. The carve-out should key off capability, not dialect name. supports.returnValues defaults to false (packages/core/src/abstract-dialect/dialect.ts:353), so db2, ibmi and snowflake inherit it and oracle sets it explicitly — yet the carve-out lists only ['mysql','mariadb']. Four dialects with the same inability to return real generated keys are treated as though they can. Deriving the predicate from supports.returnValues fixes both directions at once.

2. A configuration footgun in the same area. Listing the primary key in updateOnDuplicate but not in fields emits `id`=VALUES(`id`) and silently reassigns a matched row's primary key.

Caveat on scope

The multi-row alignment in the plain path (no updateOnDuplicate/ignoreDuplicates) was tested and is correct — excluding the PK from fields forces every row through AUTO_INCREMENT, so the range stays contiguous and index-aligned. However, that was only verified at auto_increment_increment = 1. The comment at packages/mariadb/src/query.js:111-112 implies the assumption fails under Galera or a non-1 increment, which was not tested. Worth confirming as part of any fix.

Created by Opus 5 with Claude Code, supervised by @WikiRik.

Start with the MySQL and MariaDB query implementations at packages/mysql/src/query.js:116-127 and the corresponding MariaDB code, then inspect packages/core/src/model.js:2517-2521 and packages/core/src/abstract-dialect/dialect.ts:353. Reproduce bulkCreate with updateOnDuplicate and ignoreDuplicates, including returned-instance updates or deletes. Done means generated keys stay aligned with returned instances and the capability-based carve-out and related configuration behavior are covered without regressing the plain path.