Partitioning/InnoDB: EXCHANGE PARTITION accepts a table whose unique keys are declared in a different order, after which every open of the partitioned table fails with error 174

Summary

ALTER TABLE t EXCHANGE PARTITION p WITH TABLE x succeeds when x has the same unique keys as t but declares them in a different order. The exchanged-in partition keeps x's index order in the InnoDB dictionary. On the next open, InnoDB sets HA_DUPLICATE_KEY_NOT_IN_ORDER for that partition only. ha_partition::open() then finds that the partitions have different table flags and returns HA_ERR_INITIALIZATION (174). After that, every <schema> that opens the whole table fails, including SELECT, SHOW CREATE TABLE and CHECK TABLE. Nothing is written to the error log.

No REORGANIZE, stored procedure or DATA DIRECTORY is needed. A table created directly with its final partition list reproduces it.

Expected behaviour

One of these:

• the EXCHANGE is rejected with ER_TABLES_DIFFERENT_METADATA when the unique key order differs, or
• the partitioned table stays usable after the exchange.

At minimum, the table should not become unopenable through a <schema> the server accepted, and CHECK TABLE and the error log should say why.

Actual behaviour

• The EXCHANGE returns Query OK, 0 rows affected with no warning.
• The very next full-table <schema> fails with ERROR 1030 (HY000): Got error 174 "Fatal error during initialization of handler" from storage engine InnoDB. This includes the first <schema> after the EXCHANGE, before any FLUSH TABLES.
• CHECK TABLE: Error Got error 174 ... followed by error Corrupt.
• Reads restricted to partitions that kept the original order still work (SELECT ... PARTITION (p1, pDEF)).
• The error log stays empty.

How to reproduce

Plain server, no special configuration. Run with mariadb --force -vvv. The full scripts and outputs are in the attached reproduction package (bugB_exchange_keyorder.sql and the negative control bugB_negative_control.sql).

CREATE DATABASE IF NOT EXISTS mtest;

USE mtest;

DROP TABLE IF EXISTS t2, x2;

CREATE TABLE t2 (a int NOT NULL, b int NOT NULL, c int NULL, d int NULL,

  PRIMARY KEY (a,b), UNIQUE KEY u_b (a,c), UNIQUE KEY u_a (a,d)) ENGINE=InnoDB

PARTITION BY LIST <schema>(a) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2), PARTITION pDEF DEFAULT);

INSERT INTO t2 VALUES (1,1,1,1),(2,1,1,1),(2,2,2,2),(3,1,1,1);

-- same columns and keys, unique keys declared in the other order

CREATE TABLE x2 (a int NOT NULL, b int NOT NULL, c int NULL, d int NULL,

  PRIMARY KEY (a,b), UNIQUE KEY u_a (a,d), UNIQUE KEY u_b (a,c)) ENGINE=InnoDB;

INSERT INTO x2 SELECT * FROM t2 WHERE a = 2;

ALTER TABLE t2 TRUNCATE PARTITION p2;

ALTER TABLE t2 EXCHANGE PARTITION p2 WITH TABLE x2;   -- accepted

SELECT COUNT(*) FROM t2;                              -- error 174

FLUSH TABLES t2;

SELECT * FROM t2;                                     -- error 174

SHOW CREATE TABLE t2;                                 -- error 174

CHECK TABLE t2;                                       -- Error 174 / Corrupt

SELECT * FROM t2 PARTITION (p1, pDEF);                -- works

SELECT t.NAME, i.INDEX_ID, i.NAME FROM information_schema.INNODB_SYS_TABLES t

  JOIN information_schema.INNODB_SYS_INDEXES i USING(TABLE_ID)

  WHERE t.NAME LIKE 'mtest/t2#%' ORDER BY t.NAME, i.INDEX_ID;

Negative control: the same script with x declaring u_b before u_a, as in t. Result: no error, COUNT=4, CHECK TABLE OK, and PRIMARY,u_b,u_a in every partition. The only difference between the failing case and the control is the order in which the unique keys are declared.

Evidence

Output, identical on 11.8.9 and 13.0.2 (bugB_result_cs118.txt, bugB_result_cs130.txt):

ALTER TABLE t2 EXCHANGE PARTITION p2 WITH TABLE x2  -> Query OK, 0 rows affected

ERROR 1030 (HY000) at line 16: Got error 174 "Fatal error during initialization of handler" from storage engine InnoDB   <- SELECT COUNT(*)

ERROR 1030 (HY000) at line 18: Got error 174 ...   <- SELECT * after FLUSH TABLES

ERROR 1030 (HY000) at line 19: Got error 174 ...   <- SHOW CREATE TABLE

| mtest.t2 | check | Error    | Got error 174 "Fatal error during initialization of handler" from storage engine InnoDB |

| mtest.t2 | check | error    | Corrupt                                                                                 |

SELECT * FROM t2 PARTITION (p1, pDEF) -> (1,1,1,1),(3,1,1,1)

| mtest/t2#P#p1   | PRIMARY | u_b | u_a |

| mtest/t2#P#p2   | PRIMARY | u_a | u_b |   <- order of x2

| mtest/t2#P#pDEF | PRIMARY | u_b | u_a |

error log lines written during the run: 0

In the production case, a maintenance procedure built the exchange table from information_schema.STATISTICS with the keys sorted by name. Every table whose unique keys were not declared in name order became unopenable with 1030/174 after the first nightly EXCHANGE. Nothing was logged, and innodb_force_recovery did not help. Tables whose keys happened to be in name order were not affected.

Analysis

Source read at mariadb-13.0.2:

• sql/sql_partition.cc:7730: EXCHANGE calls compare_table_with_partition(), which uses mysql_compare_tables().
• sql/sql_table.cc:7747-7756 (mysql_compare_tables()): for each key of the first table it searches the other table for a key with the same name (cmp(&table_key->name, &new_key->name)). It then compares algorithm, flags and key parts. Key position is never compared, so a table whose unique keys are in another order counts as metadata-equal.
• storage/innobase/handler/ha_innodb.cc:5654-5662: when a unique index in the InnoDB dictionary is in a different position than in the .frm, m_int_table_flags |= HA_DUPLICATE_KEY_NOT_IN_ORDER.
• sql/ha_partition.cc:3902-3923: ha_partition::open() requires every opened partition to have the same ha_table_flags() after masking PARTITION_DISABLED_TABLE_FLAGS / PARTITION_ENABLED_TABLE_FLAGS. Otherwise it returns HA_ERR_INITIALIZATION without <schema> anything. PARTITION_DISABLED_TABLE_FLAGS (sql/ha_partition.cc:79-82) does not include HA_DUPLICATE_KEY_NOT_IN_ORDER.
• sql/ha_partition.cc:3907: partitions not in m_opened_partitions are skipped. This explains why reads limited to the partitions with the original order succeed.
  The same code path was reported at 11.8.6 in the Enterprise issue linked below.

Possible fixes

• For EXCHANGE, make compare_table_with_partition() / mysql_compare_tables() also require the same key order (at least for unique keys) and reject a mismatch with ER_TABLES_DIFFERENT_METADATA.
• Or tolerate the difference: handle HA_DUPLICATE_KEY_NOT_IN_ORDER in the ha_partition::open() flag check, and have the partition handler report the union of that flag across partitions.
• In either case, log a message when ha_partition::open() fails on mismatched table flags, and make CHECK TABLE report the cause instead of only "Corrupt".

Related issues

• MENT-3193: the same defect, filed against Enterprise 11.8.6-4.
• MDEV-41577: EXCHANGE PARTITION accepts a table with an extra VECTOR INDEX (same class of problem: the EXCHANGE metadata comparison is too lax).
• MDEV-21042: REORGANIZE of a LIST DEFAULT partition misplaces rows. It was hit in the same production procedure, but it is a separate defect.
• MDEV-14618: REORGANIZE PARTITION ignores DATA DIRECTORY. This is why users fall back to copy + EXCHANGE PARTITION, the pattern that exposes this bug.

Workarounds

• Create the exchange table with its unique keys in the same order as the partitioned table, for example ordered by INDEX_ID from information_schema.INNODB_SYS_INDEXES or as in SHOW CREATE TABLE, not by key name. Before the EXCHANGE, compare the index order of the target partition with that of the exchange table. On Enterprise 11.8.6-4 and 11.8.9-6 this was validated in the lab, including after a restart, and in production. Not re-run on the Community builds.
• Reading an affected table (lab, Enterprise 11.8.6-4, not re-run on Community): open it first with an explicit PARTITION (<partition with the original key order>). In the same session and without FLUSH, CREATE TABLE ... AS SELECT * FROM t or ALTER TABLE t REMOVE PARTITIONING then succeeded. Run on their own, REMOVE PARTITIONING and an EXCHANGE back both fail with 174.