The ALTER TABLE statement failed because it tries to add or enforce a NOT NULL constraint while NULL data already exists and strict SQL mode forbids implicit conversion.
MySQL error 1861 (ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOT_NULL) occurs when ALTER TABLE attempts to add a NOT NULL column or make an existing column NOT NULL while rows still contain NULLs; clean or convert the data, then re-run the DDL to fix it.
ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_NOT_NULL
Error 1861 fires when ALTER TABLE introduces a NOT NULL requirement that would turn existing NULL data into NOT NULL without an explicit value. From MySQL 5.7.1 onward, strict SQL mode blocks this silent conversion and raises the error instead of quietly replacing NULL with default values.
The message protects data quality by forcing you to fix or replace NULLs before tightening the constraint. Addressing it promptly avoids unexpected NULL handling and guarantees that new schema rules stay consistent.
The error appears during DDL like ADD COLUMN ... NOT NULL, MODIFY COLUMN ... NOT NULL, or CHANGE COLUMN when the target table already stores at least one NULL in that column. It can also surface in online schema change tools or during replication if the replica enforces stricter sql_mode settings than the primary.
Leaving inconsistent NULL values prevents you from enforcing referential integrity, slows queries that rely on NOT NULL indexes, and blocks future schema migrations. Fixing the error guarantees clean datasets and predictable application behavior.
Rows already contain NULL, and the ALTER statement tries to add a NOT NULL constraint or make the column NOT NULL.
A column is added as NOT NULL but no default is provided, so MySQL cannot populate existing rows.
sql_mode includes STRICT_ALL_TABLES or STRICT_TRANS_TABLES, which disallows silent data changes that would otherwise convert NULL to default.
Replication lag surfaces the error on a replica that runs stricter sql_mode than the source, even though the master accepted the change.
Raised when an INSERT or UPDATE tries to put NULL into a NOT NULL column.
Occurs during INSERT when NO DEFAULT exists for a NOT NULL column and strict mode is on.
Appears if strict mode is off and MySQL silently inserts default instead of NULL, the opposite of 1861 behavior.
Yes, but this only masks the problem. Data may become inconsistent. Cleanup NULLs instead.
No. The condition was introduced in 5.7.1. Earlier versions silently converted NULLs.
Supplying a DEFAULT fixes new NOT NULL columns but will not solve NULLs already stored in existing columns.
Galaxy flags NULL counts in schema diffs, suggests data cleanup queries, and lets teams review ALTER statements collaboratively to catch the issue before deployment.
.avif)
.avif){kind=logo}
