MySQL Bugs: #120206: EXCHANGE PARTITION bypasses CHECK CONSTRAINT validation

Bug #120206	EXCHANGE PARTITION bypasses CHECK CONSTRAINT validation
Submitted:	2 Apr 19:48	Modified:	18 Sep 10:01
Reporter:	Yakir Gibraltar (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	8.0.46, 8.4.9, 9.6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
ALTER TABLE ... EXCHANGE PARTITION does not validate that check constraints match between the partitioned table and the swap table. This allows data that violates the swap table's check constraints to be silently moved into it.

The exchange succeeds unconditionally. The swap table ends up with rows that violate its own check constraints. Constraints ARE still enforced for new DML on the swap table, creating an inconsistency between existing and newly inserted rows.

Impact:
- Data integrity bypass: rows violating CHECK constraints can exist in the table after EXCHANGE PARTITION
- Constraints still enforced for new DML, creating inconsistency between existing and new rows
- No crash, but violates the contract that CHECK constraints guarantee about table data

Root Cause:
sql/sql_partition_admin.cc — check_exchange_partition() validates engine, temp table status, FK constraints, and partitioning, but has zero CHECK constraint validation. sql/sql_table.cc — mysql_compare_tables() compares column types, keys, and NULL behavior, but does not compare check constraints either.

Related:
- MariaDB MDEV-33127 — same issue, open/unresolved
- TiDB #45922 — same issue, fixed via PR #46021
- No existing MySQL bug as of 2026-04-01

Thank you,
Yakir Gibraltar

How to repeat:
CREATE TABLE t_part (
  a INT,
  CHECK (a > 0)
) PARTITION BY RANGE (a) (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN MAXVALUE
);

CREATE TABLE t_swap (
  a INT,
  CHECK (a > 50)
);

INSERT INTO t_part PARTITION (p0) VALUES (10), (20);

-- Succeeds — no check constraint validation
ALTER TABLE t_part EXCHANGE PARTITION p0 WITH TABLE t_swap;

-- t_swap now contains rows violating its own CHECK (a > 50)
SELECT * FROM t_swap;
-- Returns: 10, 20

-- Constraint IS enforced for new inserts
INSERT INTO t_swap VALUES (5);
-- ERROR 3819 (HY000): Check constraint 't_swap_chk_1' is violated.

Expected: ALTER TABLE ... EXCHANGE PARTITION should reject the exchange when the partitioned table's data would violate the swap table's check constraints (or vice versa), unless WITHOUT VALIDATION is specified.

Actual: The exchange succeeds. t_swap ends up with rows (10, 20) that violate CHECK (a > 50), while new inserts are still blocked by the constraint.

Suggested fix:
In check_exchange_partition() (sql/sql_partition_admin.cc, lines 127-172) or in mysql_compare_tables() (sql/sql_table.cc, lines 13611-13682), add validation that iterates over check constraints on both tables and rejects the exchange if the swap table has constraints that the data from the partitioned table would violate.

Alternatively, extend the existing WITH VALIDATION row scan (sql/sql_partition_admin.cc) to also evaluate the swap table's CHECK constraints against each row being moved, similar to how DML enforces them.

No one want to verify the following bug ?

Thank you, verified as described!

Posted by developer: Bug status updated to 'Patch pending'

Posted by developer: Bug status updated to 'Patch approved'

Posted by developer: Bug status updated to 'Documenting'

Posted by developer:
 
Added the following note to the MySQL Server 8.4.13, 9.7.4, and 26.10.0 release notes:

ALTER TABLE ... EXCHANGE PARTITION could move rows that violated the
destination table's CHECK constraints or exchange tables whose generated
columns used different expressions. Rows are now checked in both
directions, including when WITHOUT VALIDATION skips partition-bound
checks, and mismatched generated-column definitions are rejected. Errors
were returned similar to the following:

ERROR 3819 (HY000): Check constraint 't_swap_chk_1' is violated.