<p>MySQL throws error 1847 when an ALTER TABLE statement uses ALGORITHM=COPY without taking an exclusive lock.</p>
<p>MySQL Error 1847: ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_COPY appears when ALTER TABLE runs with ALGORITHM=COPY but MySQL cannot acquire the required exclusive lock. Remove the LOCK=NONE clause, switch to ALGORITHM=INPLACE, or run the change during a maintenance window to resolve the issue.</p>
COPY algorithm requires a lock
Error 1847 fires when ALTER TABLE is executed with ALGORITHM=COPY but MySQL is unable or not allowed to take the table-level lock that the COPY algorithm requires.
The COPY algorithm creates a new table, copies all rows, swaps the tables, and drops the old one. Because the data physically moves, MySQL needs an exclusive lock to keep consistency.
The error appears if you explicitly set LOCK=NONE or LOCK=SHARED with ALGORITHM=COPY. MySQL also raises it when a metadata lock is already held by another session, preventing the required exclusive lock.
The problem is common in high-traffic production systems where long-running reads hold shared metadata locks on heavily used tables.
Failed schema changes leave necessary modifications unfinished, block deployment pipelines, and may hold metadata locks that stall other sessions. Addressing the error allows structural changes to proceed safely and predictably.
Specifying LOCK=NONE or LOCK=SHARED while also requesting ALGORITHM=COPY forces MySQL into an impossible state, generating error 1847.
Other sessions holding shared metadata locks block the exclusive lock needed by COPY, causing the operation to abort with the error.
Complex topologies with replication filters or cascading foreign keys sometimes trigger MySQL to fall back to COPY, surfacing the lock requirement.
Raised when a requested alter operation is not supported at all, not just because of locking.
Appears when the ALTER statement would not change the table, often due to a redundant clause.
Indicates the statement tried to rename a column or index in a way MySQL cannot execute.
Yes. In many cases ALGORITHM=INPLACE combined with suitable options lets MySQL avoid copying. Verify with SHOW WARNINGS after the ALTER.
The error occurs on the server where the ALTER is issued. Slaves receiving the statement may repeat the error if they cannot obtain the lock.
Galaxy highlights incompatible LOCK and ALGORITHM clauses as you type, and its AI copilot suggests INPLACE alternatives, reducing the chance of error 1847.
.avif)
.avif){kind=logo}
