EC
1794
MySQLERRORNotableDDLHIGH confidence

INPLACE ALTER not supported — NOT NULL constraint added

Production Risk

Medium — DDL blocked; data update required first.

What this means

Adding a NOT NULL constraint to a column that currently contains NULL values is not supported with ALGORITHM=INPLACE.

Why it happens
  1. 1ALTER TABLE ... MODIFY COLUMN col INT NOT NULL on a column containing NULL values, using INPLACE.
How to reproduce
trigger — this will error
trigger — this will error
ALTER TABLE tbl ALGORITHM=INPLACE, MODIFY COLUMN col INT NOT NULL; -- col has NULLs

expected output

ERROR 1794 (0A000): ALGORITHM=INPLACE is not supported. Reason: Adding NOT NULL constraint is not supported.

Fix

Update NULLs to default values first, then alter with COPY

Update NULLs to default values first, then alter with COPY
UPDATE tbl SET col = 0 WHERE col IS NULL;
ALTER TABLE tbl ALGORITHM=COPY, MODIFY COLUMN col INT NOT NULL DEFAULT 0;

Why this works

Eliminating NULLs before the constraint change allows it to succeed.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 1794 ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOT_NULL

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors