Description:
An existing enforced CHECK constraint that references a DATETIME column
incorrectly prevents an unrelated ENUM column from being extended using
ALGORITHM=INSTANT or ALGORITHM=INPLACE.
Appending an ENUM member is documented as supporting ALGORITHM=INSTANT when
the storage size does not change. In the reproduction below, the ENUM remains
within the one-byte representation and the CHECK constraint does not reference
the ENUM column.
Nevertheless, both INSTANT and INPLACE are rejected and MySQL requires COPY.
The issue was originally observed on an Amazon Aurora MySQL instance compatible
with MySQL 8.0.42. It is reproducible on stock Oracle MySQL 8.0.42, 8.0.45,
and the current MySQL 8.0.46 official Docker image.
This is distinct from Bug #117450, which concerns adding a new CHECK constraint.
Here, the CHECK constraint already exists, references an unchanged column, and
should not require reevaluation for the unrelated ENUM extension.
How to repeat:
SELECT VERSION();
-- 8.0.46
DROP TABLE IF EXISTS t_datetime;
CREATE TABLE t_datetime (
id BIGINT UNSIGNED NOT NULL,
view_type ENUM('A','B') NOT NULL,
checked_at DATETIME(6) DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT chk_checked_at CHECK (checked_at IS NULL)
) ENGINE=InnoDB;
ALTER TABLE t_datetime
MODIFY COLUMN view_type ENUM('A','B','C') NOT NULL,
ALGORITHM=INSTANT;
Actual result:
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this
operation. Try ALGORITHM=COPY.
ALGORITHM=INPLACE is also rejected:
ALTER TABLE t_datetime
MODIFY COLUMN view_type ENUM('A','B','C') NOT NULL,
ALGORITHM=INPLACE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this
operation. Try ALGORITHM=COPY.
Control case 1:
===============
Dropping the unrelated CHECK in the same ALTER allows the ENUM extension to
complete using ALGORITHM=INSTANT:
ALTER TABLE t_datetime
DROP CHECK chk_checked_at,
MODIFY COLUMN view_type ENUM('A','B','C') NOT NULL,
ALGORITHM=INSTANT;
Result:
Query OK, 0 rows affected
Control case 2:
===============
An equivalent CHECK referencing a BIGINT column does not prevent the instant
ENUM extension:
DROP TABLE IF EXISTS t_bigint;
CREATE TABLE t_bigint (
id BIGINT UNSIGNED NOT NULL,
view_type ENUM('A','B') NOT NULL,
checked_value BIGINT DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT chk_checked_value CHECK (checked_value IS NULL)
) ENGINE=InnoDB;
ALTER TABLE t_bigint
MODIFY COLUMN view_type ENUM('A','B','C') NOT NULL,
ALGORITHM=INSTANT;
Result:
Query OK, 0 rows affected
Expected result:
================
The ENUM extension should complete using ALGORITHM=INSTANT.
The new ENUM value is appended, the ENUM storage size remains one byte, and
the existing CHECK constraint references only an unchanged column.
Suggested fix:
Source-code analysis:
=====================
Source-code analysis indicates that
is_any_check_constraints_evaluation_required() incorrectly concludes that the
unchanged DATETIME column has changed.
When ALTER_CHANGE_COLUMN is set, the function iterates over every Create_field
in the resulting table definition, including unchanged columns referenced by
existing CHECK constraints:
https://github.com/mysql/mysql-server/blob/mysql-8.0.46/sql/sql_table.cc#L19735-L19747
The comparison uses:
itm_fld->data_type() != fld.sql_type
These values are populated through different Field APIs:
1. Item_field::set_field() uses field->type():
https://github.com/mysql/mysql-server/blob/mysql-8.0.42/sql/item.cc#L2897-L2927
2. Create_field(Field *) uses field->real_type():
https://github.com/mysql/mysql-server/blob/mysql-8.0.42/sql/create_field.cc#L59-L65
3. Field_datetimef deliberately returns different values:
type() -> MYSQL_TYPE_DATETIME
real_type() -> MYSQL_TYPE_DATETIME2
https://github.com/mysql/mysql-server/blob/mysql-8.0.42/sql/field.h#L3384-L3429
Therefore, the unchanged checked_at column is incorrectly treated as having a
changed datatype merely because an unrelated MODIFY COLUMN operation set
ALTER_CHANGE_COLUMN.
The same comparison remains present in MySQL 8.0.46.
Suggested fix:
==============
Only compare the datatype of a CHECK-referenced column when the corresponding
Create_field represents a column that is actually being changed, or compare
types using consistent/normalized Field representations.
For example, the datatype-change test appears to require an fld.change guard,
similar to the default-change test immediately below it:
if (fld.change &&
!my_strcasecmp(system_charset_info, itm_fld->field_name,
fld.field_name) &&
itm_fld->data_type() != fld.sql_type)
return true;
The exact fix may require additional handling for renamed columns and datatype
attributes.
Please also add a regression test covering an unrelated instant MODIFY COLUMN
on a table whose existing enforced CHECK references DATETIME(N).
Impact:
=======
This forces a full table copy for an otherwise metadata-only ENUM extension on
large production tables. A COPY operation can require substantial additional
storage, replication work, and operational downtime.
There is no SQL-level spelling that avoids the internal DATETIME/DATETIME2
representation difference while retaining the same enforced CHECK constraint.
© 2026, Oracle Corporation and/or its affiliates