<p>MySQL throws error 1731 when an ALTER TABLE ... EXCHANGE PARTITION tries to swap in a table whose storage options do not exactly match the partitioned table.</p>
<p>MySQL Error 1731 ER_PARTITION_EXCHANGE_DIFFERENT_OPTION occurs when ALTER TABLE ... EXCHANGE PARTITION finds mismatched options between the standalone table and the partition. Align ENGINE, ROW_FORMAT, CHARSET, and other options, then rerun the exchange to resolve the issue.</p>
Non matching attribute '%s' between partition and table
Error 1731 fires during ALTER TABLE ... EXCHANGE PARTITION when MySQL detects at least one table option mismatch between the partitioned table and the standalone table you want to swap in. The server stops the operation to prevent metadata corruption.
The error message reads: Non matching attribute '%s' between partition and table. The placeholder shows the first option MySQL found to be different, such as ROW_FORMAT or DEFAULT CHARSET.
MySQL requires the source table to be logically identical to the target partition. Option differences in storage engine, compression, encryption, row format, key block size, or default collation break that rule and trigger error 1731.
The check is strict because partition metadata is shared by all partitions. Swapping in a table with divergent options could make the overall table definition inconsistent.
Compare SHOW CREATE TABLE outputs of the partitioned table and the standalone table. Alter the standalone table so every option matches exactly. Once aligned, rerun ALTER TABLE ... EXCHANGE PARTITION and the statement will succeed.
Always start with ENGINE, ROW_FORMAT, DEFAULT CHARSET, and COLLATE, then confirm secondary settings like COMPRESSION, ENCRYPTION, and KEY_BLOCK_SIZE.
Create the standalone table with LIKE partitioned_table syntax or a CREATE TABLE ... SELECT that copies structure. Both methods inherit all options, preventing mismatches later.
Automate verification in CI pipelines or use a schema-aware editor like Galaxy that shows table options inline and warns about differences before execution.
Error 1732 ER_PARTITION_EXCHANGE_PART_TABLE signals the target table is itself partitioned. Error 1735 ER_PARTITION_EXCHANGE_FOREIGN_KEY warns of foreign keys. Error 1747 ER_PARTITION_EXCHANGE_TEMP_TABLE occurs when the table is temporary. Their fixes follow the same inspect-and-align pattern.
The standalone table uses a different storage engine (e.g., MyISAM) while the partitioned table is InnoDB.
Compact vs Dynamic row format discrepancies trigger the error.
DEFAULT CHARSET utf8mb4 with COLLATE utf8mb4_0900_ai_ci must match exactly.
One table is compressed or encrypted and the other is not.
Different key block sizes on MyISAM tables cause attribute mismatch.
Raised when the source table is itself partitioned. Drop partitioning or create a plain table before exchange.
Occurs if the source table has foreign keys. Remove constraints or add identical keys to the partitioned table.
Shown when attempting to exchange a temporary table. Use a permanent table instead.
Indicates partition operations on temporary tables are not allowed.
Yes. Column order, data types, and generated column properties must be identical in addition to table options.
No. MySQL validates every option and stops at the first mismatch. All options must align.
WITHOUT VALIDATION skips data checks but not metadata checks. Option mismatches are still enforced.
Galaxy displays option metadata in its sidebar, highlights differences in compare mode, and lets you run ALTER statements with AI-generated fixes, reducing runtime errors.
.avif)
.avif){kind=logo}
