Skip to main content

MY.L3.EXCHANGE_PARTITION_CLAUSE_IGNORED — An ALGORITHM or LOCK clause MySQL parses and discards

  • Category: safety
  • Level: 3
  • Confidence: deterministic
  • Downtime class: derived per statement from the exchange_partition matrix entry, and never restated here — which is why no single value is named on this line
  • Stability: stable
  • Suites: lint
  • Applies to: MySQL 8.4 and later. Not transferable to MariaDB — SQLens refuses that engine outright rather than reasoning about semantics it does not share

The measurement, because the claim is the whole rule​

ALGORITHM=COPY together with LOCK=NONE is a contradiction: copying a table requires a lock, and MySQL says so. Measured on 8.4.10, one statement at a time against a fresh partitioned table:

StatementServer
ALGORITHM=COPY, LOCK=NONE, EXCHANGE PARTITION p0 WITH TABLE taccepted
ALGORITHM=COPY, LOCK=NONE, DROP PARTITION p1refused — 1846
ALGORITHM=COPY, LOCK=NONE, ADD COLUMN c INT NULLrefused — 1846
ALGORITHM=COPY, LOCK=NONE, ADD INDEX iv (v)refused — 1846
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

Every neighboring statement validates the clause and rejects the impossible pair. Only this one takes it. That is the proof that the clause is not checked here — it is parsed and thrown away.

Why a rule and not a footnote​

Somebody writes LOCK=NONE because they want availability. They get no error and no guarantee, and on any other statement the server would have argued back. This one silence is indistinguishable from agreement — a promise shaped like a promise that is not one.

The cost the clause never addressed​

By default MySQL validates every row of the incoming table against the partition definition, and the table does not accept writes while that scan runs. Somebody who believes LOCK=NONE protects them from that is wrong twice: the clause does nothing, and the thing they were worried about is real.

Flagged​

DB::statement('ALTER TABLE orders ALGORITHM=INPLACE, LOCK=NONE, EXCHANGE PARTITION p2025 WITH TABLE orders_2025');

Preferred​

DB::statement('ALTER TABLE orders EXCHANGE PARTITION p2025 WITH TABLE orders_2025');

Removing the clause changes nothing about what the statement does — it only stops the migration from claiming something the server never agreed to. Plan the exchange for the validation scan instead.

WITHOUT VALIDATION is not the reassurance it looks like​

It removes the scan and the guarantee together. A row that does not belong in the partition lives there afterwards anyway, and the table's own definition then lies about its contents — which is worse than slow, and harder to find. It is a trade to make deliberately, never a way to make this finding go away.

What is not reported​

The same clause on ADD, DROP, TRUNCATE, REORGANIZE or COALESCE PARTITION is validated by the server and honored, so writing it there is correct and this rule stays silent. An EXCHANGE PARTITION without the clause is not reported either: it made no promise to break. And a string literal that happens to spell LOCK= never decides the verdict — in either direction.

Sources​

  • MySQL 8.4: online DDL operations, partitioning — the partitioning section records that EXCHANGE PARTITION is not affected by the ALGORITHM and LOCK clauses the way other partitioning operations are. What that means in practice is the measurement above.

The fix material this rule carries​

A finding from this rule carries a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.