Description:
ALTER TABLE ... EXCHANGE PARTITION compares the definitions of the two tables and
refuses mismatches with ER_TABLES_DIFFERENT_METADATA (1736). That comparison
covers column types, nullability, column order, collation, ENUM/SET member lists,
the set of indexes, and index prefix lengths.
It does not cover the *expressions* attached to the table, nor its CHECK
constraints. An exchange between two tables that differ only in
- a STORED generated column's expression
- a VIRTUAL generated column's expression, where that column is indexed
- a functional key part's expression
- the presence or definition of a CHECK constraint
is accepted with no error and no warning. Because the exchange swaps the
tablespaces, the partitioned table then owns data and index pages that were
produced under a *different* definition from the one it declares.
Consequences, one per omitted item:
1. STORED generated column: the partitioned table stores values that contradict
its own generated column definition. CHECK TABLE reports OK. mysqldump omits
generated columns, so the restore recomputes them and produces *different
data* — the restore succeeds, silently, with values that do not match the
source.
2. VIRTUAL generated column with an index: the index pages hold values computed
from the other table's expression while the column is computed on read from
this table's expression. Queries return wrong rows depending on whether the
index is used — not merely missing rows, but different rows.
3. Functional key part: same as 2.
4. CHECK constraint: rows the server refuses on every ordinary write path are
stored. The constraint remains enforced against new rows, CHECK TABLE reports
OK, and the table cannot be restored from its own mysqldump backup.
For CHECK constraints the same gap exists in ALTER TABLE ... IMPORT TABLESPACE,
and so does the generated column expression gap. (Mismatched *column* definitions
on IMPORT — ENUM member order, DECIMAL scale — are a separate, previously known
issue and are not the subject of this report.)
For CHECK constraints, every ordinary write path validates correctly; only these
two physical paths do not:
INSERT ERROR 3819, refused
INSERT IGNORE row not written
INSERT ... SELECT ERROR 3819, refused
REPLACE ERROR 3819, refused
LOAD DATA LOCAL INFILE ERROR 3819, refused
------------------------------------------------------------------
EXCHANGE PARTITION accepted
EXCHANGE PARTITION ... WITHOUT VALIDATION accepted
IMPORT TABLESPACE accepted
IMPORT PARTITION <p> TABLESPACE accepted
For EXCHANGE PARTITION the CHECK gap holds in both directions: moving a violating
row into a constrained partitioned table, and moving one out of an unconstrained
partitioned table into a constrained standalone table.
How to repeat:
--------------------------------------------------------------------------------
How to repeat, part 1: STORED generated column expression
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS b5; CREATE DATABASE b5; USE b5;
CREATE TABLE pt(a INT NOT NULL, g INT AS (a*2) STORED) ENGINE=InnoDB
PARTITION BY RANGE(a)(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
CREATE TABLE src(a INT NOT NULL, g INT AS (a*3) STORED) ENGINE=InnoDB;
INSERT INTO src(a) VALUES (1),(2),(3);
ALTER TABLE pt EXCHANGE PARTITION p0 WITH TABLE src;
Query OK, 0 rows affected
SELECT a, g, a*2 AS should_be FROM pt ORDER BY a;
+---+------+-----------+
| a | g | should_be |
+---+------+-----------+
| 1 | 3 | 2 |
| 2 | 6 | 4 |
| 3 | 9 | 6 |
+---+------+-----------+
CHECK TABLE pt;
+-------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------+-------+----------+----------+
| b5.pt | check | status | OK |
+-------+-------+----------+----------+
The stored values contradict the column's own definition. A dump and restore
succeeds but returns different data, because the restore recomputes the column:
$ mysqldump --set-gtid-purged=OFF --skip-comments b5 pt | mysql ... b5r
mysql> SELECT a, g FROM b5r.pt ORDER BY a;
+---+------+
| a | g |
+---+------+
| 1 | 2 |
| 2 | 4 |
| 3 | 6 |
+---+------+
Restoring a backup of this table therefore silently changes every value in the
column, with no error at any point.
--------------------------------------------------------------------------------
How to repeat, part 2: VIRTUAL generated column with an index
--------------------------------------------------------------------------------
CREATE TABLE pt2(a INT NOT NULL, g INT AS (a*2) VIRTUAL, KEY kg(g)) ENGINE=InnoDB
PARTITION BY RANGE(a)(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
CREATE TABLE src2(a INT NOT NULL, g INT AS (a*3) VIRTUAL, KEY kg(g)) ENGINE=InnoDB;
INSERT INTO src2(a) VALUES (1),(2),(3);
ALTER TABLE pt2 EXCHANGE PARTITION p0 WITH TABLE src2;
SELECT a FROM pt2 WHERE g=6; -- uses the index
+---+
| a |
+---+
| 2 |
+---+
SELECT a FROM pt2 IGNORE INDEX(kg) WHERE g=6; -- computes g from pt2's definition
+---+
| a |
+---+
| 3 |
+---+
The two plans return *different rows* for the same predicate. Under pt2's own
definition g = a*2, so the correct answer is a=3; the index, built from a*3,
answers a=2.
--------------------------------------------------------------------------------
How to repeat, part 3: functional key part
--------------------------------------------------------------------------------
CREATE TABLE pt3(a INT NOT NULL, INDEX f((a*2))) ENGINE=InnoDB
PARTITION BY RANGE(a)(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
CREATE TABLE src3(a INT NOT NULL, INDEX f((a*3))) ENGINE=InnoDB;
INSERT INTO src3 VALUES (1),(2),(3);
ALTER TABLE pt3 EXCHANGE PARTITION p0 WITH TABLE src3;
SELECT a FROM pt3 WHERE (a*2)=4; -- Empty set (wrong)
SELECT a FROM pt3 IGNORE INDEX(f) WHERE (a*2)=4; -- 2 (correct)
--------------------------------------------------------------------------------
How to repeat, part 4: CHECK constraint
--------------------------------------------------------------------------------
CREATE TABLE acct (
id INT NOT NULL,
role VARCHAR(16),
CONSTRAINT no_admin CHECK (role <> 'admin')
) ENGINE=InnoDB
PARTITION BY RANGE(id) (PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
CREATE TABLE staging(id INT NOT NULL, role VARCHAR(16)) ENGINE=InnoDB;
INSERT INTO staging VALUES (1,'admin'),(2,'user');
INSERT INTO acct VALUES (3,'admin');
ERROR 3819 (HY000): Check constraint 'no_admin' is violated.
ALTER TABLE acct EXCHANGE PARTITION p0 WITH TABLE staging;
Query OK, 0 rows affected
SELECT id, role FROM acct WHERE NOT (role <> 'admin');
+----+-------+
| id | role |
+----+-------+
| 1 | admin |
+----+-------+
CHECK TABLE acct; -- status OK
INSERT INTO acct VALUES (4,'admin');
ERROR 3819 (HY000): Check constraint 'no_admin' is violated.
$ mysqldump --set-gtid-purged=OFF --skip-comments b4 acct | mysql ... b4r
ERROR 3819 (HY000) at line 27: Check constraint 'no_admin' is violated.
mysql> SELECT COUNT(*) FROM b4r.acct;
0
WITHOUT VALIDATION behaves identically. The reverse direction also reproduces:
CREATE TABLE pt7(id INT NOT NULL, role VARCHAR(16)) ENGINE=InnoDB
PARTITION BY RANGE(id)(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO pt7 VALUES (1,'admin');
CREATE TABLE s7(id INT NOT NULL, role VARCHAR(16),
CONSTRAINT ck_d7 CHECK (role <> 'admin')) ENGINE=InnoDB;
ALTER TABLE pt7 EXCHANGE PARTITION p0 WITH TABLE s7;
SELECT id, role FROM s7 WHERE NOT (role <> 'admin'); -- 1, 'admin'
IMPORT TABLESPACE reproduces parts 1 and 4: exporting from a table whose
generated column expression or CHECK constraints differ from the destination's is
accepted, with the same end states.
The partition-level form is affected as well. Exporting the partitions of an
unconstrained partitioned table and importing one of them into a partitioned
table that carries a CHECK constraint is accepted:
ALTER TABLE pd DISCARD PARTITION p0 TABLESPACE;
-- copy pe#p#p0.ibd / pe#p#p0.cfg into place as pd#p#p0.*
ALTER TABLE pd IMPORT PARTITION p0 TABLESPACE;
Query OK
SELECT id, role FROM pd WHERE NOT (role <> 'admin'); -- 1, 'admin'
CHECK TABLE pd; -- status OK
$ mysqldump ... pd | mysql ... target
ERROR 3819 (HY000): Check constraint 'ck_pd' is violated.
--------------------------------------------------------------------------------
Control: what the comparison does catch
--------------------------------------------------------------------------------
The same exchange with any of these differences is correctly refused with
ERROR 1736 "Tables have different definitions":
column collation ENUM member order column type
nullability column order set of indexes
index prefix length
so the comparison exists and is applied; it is the expression-valued and
constraint-valued parts of the definition that are omitted from it.
Suggested fix:
Include in the EXCHANGE PARTITION definition comparison (and in the equivalent
IMPORT TABLESPACE compatibility check):
- generated column expressions, for both STORED and VIRTUAL columns
- functional key part expressions
- CHECK constraints
refusing the operation with ER_TABLES_DIFFERENT_METADATA as it already does for
collation and ENUM differences.
For CHECK constraints, an alternative is to evaluate them during the
WITH VALIDATION scan and refuse with ER_CHECK_CONSTRAINT_VIOLATED, as an ordinary
INSERT does. The validation routine already exists and reports correctly on the
resulting table; it is simply not invoked on these paths:
ALTER TABLE acct ALTER CHECK no_admin NOT ENFORCED;
ALTER TABLE acct ALTER CHECK no_admin ENFORCED;
ERROR 3819 (HY000): Check constraint 'no_admin' is violated.
Description: ALTER TABLE ... EXCHANGE PARTITION compares the definitions of the two tables and refuses mismatches with ER_TABLES_DIFFERENT_METADATA (1736). That comparison covers column types, nullability, column order, collation, ENUM/SET member lists, the set of indexes, and index prefix lengths. It does not cover the *expressions* attached to the table, nor its CHECK constraints. An exchange between two tables that differ only in - a STORED generated column's expression - a VIRTUAL generated column's expression, where that column is indexed - a functional key part's expression - the presence or definition of a CHECK constraint is accepted with no error and no warning. Because the exchange swaps the tablespaces, the partitioned table then owns data and index pages that were produced under a *different* definition from the one it declares. Consequences, one per omitted item: 1. STORED generated column: the partitioned table stores values that contradict its own generated column definition. CHECK TABLE reports OK. mysqldump omits generated columns, so the restore recomputes them and produces *different data* — the restore succeeds, silently, with values that do not match the source. 2. VIRTUAL generated column with an index: the index pages hold values computed from the other table's expression while the column is computed on read from this table's expression. Queries return wrong rows depending on whether the index is used — not merely missing rows, but different rows. 3. Functional key part: same as 2. 4. CHECK constraint: rows the server refuses on every ordinary write path are stored. The constraint remains enforced against new rows, CHECK TABLE reports OK, and the table cannot be restored from its own mysqldump backup. For CHECK constraints the same gap exists in ALTER TABLE ... IMPORT TABLESPACE, and so does the generated column expression gap. (Mismatched *column* definitions on IMPORT — ENUM member order, DECIMAL scale — are a separate, previously known issue and are not the subject of this report.) For CHECK constraints, every ordinary write path validates correctly; only these two physical paths do not: INSERT ERROR 3819, refused INSERT IGNORE row not written INSERT ... SELECT ERROR 3819, refused REPLACE ERROR 3819, refused LOAD DATA LOCAL INFILE ERROR 3819, refused ------------------------------------------------------------------ EXCHANGE PARTITION accepted EXCHANGE PARTITION ... WITHOUT VALIDATION accepted IMPORT TABLESPACE accepted IMPORT PARTITION <p> TABLESPACE accepted For EXCHANGE PARTITION the CHECK gap holds in both directions: moving a violating row into a constrained partitioned table, and moving one out of an unconstrained partitioned table into a constrained standalone table. How to repeat: -------------------------------------------------------------------------------- How to repeat, part 1: STORED generated column expression -------------------------------------------------------------------------------- DROP DATABASE IF EXISTS b5; CREATE DATABASE b5; USE b5; CREATE TABLE pt(a INT NOT NULL, g INT AS (a*2) STORED) ENGINE=InnoDB PARTITION BY RANGE(a)(PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN MAXVALUE); CREATE TABLE src(a INT NOT NULL, g INT AS (a*3) STORED) ENGINE=InnoDB; INSERT INTO src(a) VALUES (1),(2),(3); ALTER TABLE pt EXCHANGE PARTITION p0 WITH TABLE src; Query OK, 0 rows affected SELECT a, g, a*2 AS should_be FROM pt ORDER BY a; +---+------+-----------+ | a | g | should_be | +---+------+-----------+ | 1 | 3 | 2 | | 2 | 6 | 4 | | 3 | 9 | 6 | +---+------+-----------+ CHECK TABLE pt; +-------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+-------+----------+----------+ | b5.pt | check | status | OK | +-------+-------+----------+----------+ The stored values contradict the column's own definition. A dump and restore succeeds but returns different data, because the restore recomputes the column: $ mysqldump --set-gtid-purged=OFF --skip-comments b5 pt | mysql ... b5r mysql> SELECT a, g FROM b5r.pt ORDER BY a; +---+------+ | a | g | +---+------+ | 1 | 2 | | 2 | 4 | | 3 | 6 | +---+------+ Restoring a backup of this table therefore silently changes every value in the column, with no error at any point. -------------------------------------------------------------------------------- How to repeat, part 2: VIRTUAL generated column with an index -------------------------------------------------------------------------------- CREATE TABLE pt2(a INT NOT NULL, g INT AS (a*2) VIRTUAL, KEY kg(g)) ENGINE=InnoDB PARTITION BY RANGE(a)(PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN MAXVALUE); CREATE TABLE src2(a INT NOT NULL, g INT AS (a*3) VIRTUAL, KEY kg(g)) ENGINE=InnoDB; INSERT INTO src2(a) VALUES (1),(2),(3); ALTER TABLE pt2 EXCHANGE PARTITION p0 WITH TABLE src2; SELECT a FROM pt2 WHERE g=6; -- uses the index +---+ | a | +---+ | 2 | +---+ SELECT a FROM pt2 IGNORE INDEX(kg) WHERE g=6; -- computes g from pt2's definition +---+ | a | +---+ | 3 | +---+ The two plans return *different rows* for the same predicate. Under pt2's own definition g = a*2, so the correct answer is a=3; the index, built from a*3, answers a=2. -------------------------------------------------------------------------------- How to repeat, part 3: functional key part -------------------------------------------------------------------------------- CREATE TABLE pt3(a INT NOT NULL, INDEX f((a*2))) ENGINE=InnoDB PARTITION BY RANGE(a)(PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN MAXVALUE); CREATE TABLE src3(a INT NOT NULL, INDEX f((a*3))) ENGINE=InnoDB; INSERT INTO src3 VALUES (1),(2),(3); ALTER TABLE pt3 EXCHANGE PARTITION p0 WITH TABLE src3; SELECT a FROM pt3 WHERE (a*2)=4; -- Empty set (wrong) SELECT a FROM pt3 IGNORE INDEX(f) WHERE (a*2)=4; -- 2 (correct) -------------------------------------------------------------------------------- How to repeat, part 4: CHECK constraint -------------------------------------------------------------------------------- CREATE TABLE acct ( id INT NOT NULL, role VARCHAR(16), CONSTRAINT no_admin CHECK (role <> 'admin') ) ENGINE=InnoDB PARTITION BY RANGE(id) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN MAXVALUE); CREATE TABLE staging(id INT NOT NULL, role VARCHAR(16)) ENGINE=InnoDB; INSERT INTO staging VALUES (1,'admin'),(2,'user'); INSERT INTO acct VALUES (3,'admin'); ERROR 3819 (HY000): Check constraint 'no_admin' is violated. ALTER TABLE acct EXCHANGE PARTITION p0 WITH TABLE staging; Query OK, 0 rows affected SELECT id, role FROM acct WHERE NOT (role <> 'admin'); +----+-------+ | id | role | +----+-------+ | 1 | admin | +----+-------+ CHECK TABLE acct; -- status OK INSERT INTO acct VALUES (4,'admin'); ERROR 3819 (HY000): Check constraint 'no_admin' is violated. $ mysqldump --set-gtid-purged=OFF --skip-comments b4 acct | mysql ... b4r ERROR 3819 (HY000) at line 27: Check constraint 'no_admin' is violated. mysql> SELECT COUNT(*) FROM b4r.acct; 0 WITHOUT VALIDATION behaves identically. The reverse direction also reproduces: CREATE TABLE pt7(id INT NOT NULL, role VARCHAR(16)) ENGINE=InnoDB PARTITION BY RANGE(id)(PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN MAXVALUE); INSERT INTO pt7 VALUES (1,'admin'); CREATE TABLE s7(id INT NOT NULL, role VARCHAR(16), CONSTRAINT ck_d7 CHECK (role <> 'admin')) ENGINE=InnoDB; ALTER TABLE pt7 EXCHANGE PARTITION p0 WITH TABLE s7; SELECT id, role FROM s7 WHERE NOT (role <> 'admin'); -- 1, 'admin' IMPORT TABLESPACE reproduces parts 1 and 4: exporting from a table whose generated column expression or CHECK constraints differ from the destination's is accepted, with the same end states. The partition-level form is affected as well. Exporting the partitions of an unconstrained partitioned table and importing one of them into a partitioned table that carries a CHECK constraint is accepted: ALTER TABLE pd DISCARD PARTITION p0 TABLESPACE; -- copy pe#p#p0.ibd / pe#p#p0.cfg into place as pd#p#p0.* ALTER TABLE pd IMPORT PARTITION p0 TABLESPACE; Query OK SELECT id, role FROM pd WHERE NOT (role <> 'admin'); -- 1, 'admin' CHECK TABLE pd; -- status OK $ mysqldump ... pd | mysql ... target ERROR 3819 (HY000): Check constraint 'ck_pd' is violated. -------------------------------------------------------------------------------- Control: what the comparison does catch -------------------------------------------------------------------------------- The same exchange with any of these differences is correctly refused with ERROR 1736 "Tables have different definitions": column collation ENUM member order column type nullability column order set of indexes index prefix length so the comparison exists and is applied; it is the expression-valued and constraint-valued parts of the definition that are omitted from it. Suggested fix: Include in the EXCHANGE PARTITION definition comparison (and in the equivalent IMPORT TABLESPACE compatibility check): - generated column expressions, for both STORED and VIRTUAL columns - functional key part expressions - CHECK constraints refusing the operation with ER_TABLES_DIFFERENT_METADATA as it already does for collation and ENUM differences. For CHECK constraints, an alternative is to evaluate them during the WITH VALIDATION scan and refuse with ER_CHECK_CONSTRAINT_VIOLATED, as an ordinary INSERT does. The validation routine already exists and reports correctly on the resulting table; it is simply not invoked on these paths: ALTER TABLE acct ALTER CHECK no_admin NOT ENFORCED; ALTER TABLE acct ALTER CHECK no_admin ENFORCED; ERROR 3819 (HY000): Check constraint 'no_admin' is violated.