A MySQL partition maintenance script starts failing at the EXCHANGE PARTITION step after a recent ALTER TABLE operation, with the following error:
ERROR 1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and tableThis MySQL ERROR 1731 is caused by an earlier ALTER TABLE run with ALGORITHM=INSTANT. A friend hit this on a job and this issue. I confirmed the same behaviour on MySQL 8.4.11 and as I learnt more about it. This was new for me, what I learnt through this was interesting and I had to blog this.
Producing MySQL ERROR 1731
The suspecting nature made me not believe what I just heard and hence I decided to spin-up my test MySQL 8.4.11 instance and see what’s going on.
mysql> CREATE TABLE `api_request_log` (
-> `id` bigint unsigned NOT NULL AUTO_INCREMENT,
-> `tenant_id` int unsigned NOT NULL,
-> `endpoint` varchar(191) NOT NULL,
-> `http_method` varchar(10) NOT NULL,
-> `status_code` smallint unsigned NOT NULL,
-> `response_ms` int unsigned NOT NULL,
-> `client_ip` varchar(45) NOT NULL,
-> `user_agent` varchar(255) DEFAULT NULL,
-> `request_bytes` int unsigned NOT NULL DEFAULT '0',
-> `response_bytes` int unsigned NOT NULL DEFAULT '0',
-> `trace_id` char(32) NOT NULL,
-> `logged_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`,`logged_at`),
-> KEY `idx_tenant_logged` (`tenant_id`,`logged_at`),
-> KEY `idx_endpoint_status` (`endpoint`,`status_code`,`logged_at`),
-> KEY `idx_trace` (`trace_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
-> PARTITION BY RANGE COLUMNS(`logged_at`)
-> (PARTITION p202606 VALUES LESS THAN ('2026-07-01') ENGINE = InnoDB,
-> PARTITION p202607 VALUES LESS THAN ('2026-08-01') ENGINE = InnoDB,
-> PARTITION p202608 VALUES LESS THAN ('2026-09-01') ENGINE = InnoDB,
-> PARTITION p202609 VALUES LESS THAN ('2026-10-01') ENGINE = InnoDB);
Query OK, 0 rows affected (0.71 sec)
mysql> ALTER TABLE api_request_log ADD COLUMN error_code varchar(32) DEFAULT NULL, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE api_request_log_p202607 LIKE api_request_log;
Query OK, 0 rows affected (1.28 sec)
mysql> ALTER TABLE api_request_log_p202607 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE api_request_log EXCHANGE PARTITION p202607
WITH TABLE api_request_log_p202607 WITHOUT VALIDATION;
ERROR 1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and tableWhat is MySQL ERROR 1731, “Non matching attribute ‘INSTANT COLUMN(s)'”?
Error 1731 is ER_PARTITION_EXCHANGE_DIFFERENT_OPTION. MySQL raises it when ALTER TABLE ... EXCHANGE PARTITION finds that the partition and the standalone table do not have identical structure.
That said, if you perform SHOW CREATE TABLE for both api_request_log and api_request_log_p202607, the table definition will appear identical. The real issue or difference lies in InnoDB’s internal data dictionary.
Let’s meet the MySQL’s (not a) bug
It is not a bug because I reject it. Ahem! Well, so this “issue” is already filed in MySQL bug repository as: MySQL Bug #104970 – EXCHANGE PARTITION fails for table altered with ALGORITHM=INSTANT.
The verification team’s comment conveys that EXCHANGE has design premises that INSTANT columns violate, and fixing it “would require a whole new redesign and reprogramming of the feature.“. What I understand from that report is that we’re not going to see EXCHANGE PARTITION for INSTANT columns. Only learning here is don’t add INSTANT columns to a partitioned table.
Identifying INSTANT columns in MySQL
Just by looking at CREATE TABLE statements we cannot deduce if the column is INSTANT addition. Luckily, we can query the information_schema tables.
The HAS_DEFAULT column in information_schema.INNODB_COLUMNS indicates whether a column added using ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT has a default value. Since columns added instantly always have a default value, HAS_DEFAULT can be used as an indicator of whether a column was added using the INSTANT algorithm.
I use following query to identify such tables with partitioning having INSTANT columns present.
SELECT DISTINCT
SUBSTRING_INDEX(t.NAME, '/', 1) AS table_schema,
SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '/', -1), '#p#', 1) AS table_name,
IF(p.TABLE_NAME IS NULL, 'NO', 'YES') AS has_partitioning,
c.NAME AS column_name, c.POS, c.HAS_DEFAULT
FROM information_schema.INNODB_COLUMNS c
JOIN information_schema.INNODB_TABLES t ON c.TABLE_ID = t.TABLE_ID
LEFT JOIN (
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.PARTITIONS
WHERE PARTITION_NAME IS NOT NULL
) p ON p.TABLE_SCHEMA = SUBSTRING_INDEX(t.NAME, '/', 1)
AND p.TABLE_NAME = SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '/', -1), '#p#', 1)
WHERE c.HAS_DEFAULT = 1
ORDER BY table_schema, table_name, c.POS;
+--------------+------------------------+------------------+--------------+-----+-------------+
| table_schema | table_name | has_partitioning | column_name | POS | HAS_DEFAULT |
+--------------+------------------------+------------------+--------------+-----+-------------+
| test | api_request_log | YES | error_code | 12 | 1 |
+--------------+------------------------+------------------+--------------+-----+-------------+How to fix MySQL Error 1731
The simple fixture here is to rebuild the table and cleanup the metadata mess created by ALGORITHM=INSTANT. After rebuild, the EXCHANGE PARTITION works flawlessly. OPTIMIZE table will do the same thing.
mysql> ALTER TABLE api_request_log ENGINE=InnoDB;
Query OK, 0 rows affected (0.99 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE api_request_log EXCHANGE PARTITION p202607 WITH TABLE api_request_log_p202607 WITHOUT VALIDATION;
Query OK, 0 rows affected (0.04 sec)It is worth calling this out because it is easy to assume that WITHOUT VALIDATION will bypass the problem. It won’t. WITHOUT VALIDATION is about validating whether the rows in the table being exchanged satisfy the partitioning expression. It does not disable the structural and metadata compatibility checks required for the exchange.
When WITHOUT VALIDATION is specified, the ALTER TABLE … EXCHANGE PARTITION operation does not perform any row-by-row validation when exchanging a partition a nonpartitioned table, allowing database administrators to assume responsibility for ensuring that rows are within the boundaries of the partition definition.
Okay, now NULL ALTER or TABLE REBUILD is good for smaller tables but what about the large ones? That’s why we used INSTANT right (and repenting now?). Well, Percona Toolkit to the rescue. Use pt-online-schema-change. This is a go-to utility for every open source DBA for any big table ops.
Where is the problem?
Design. Or that’s what I understand. As you read earlier the bug report already says this is not going to be implemented. I read the Work Log for Support Instant Add Column task and it clearly state this
FR14: For EXCHANGE PARTITION, to simplify the logic, if either the partition or the table to be swapped is instant, then the operation would be rejected with error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION.
This is also something you can read through the MySQL documentation that clearly states this limitation with ALGORITHM=INSTANT and partitioning operations:
After adding a column to a partitioned table using ALGORITHM=INSTANT, it is no longer possible to perform ALTER TABLE … EXCHANGE PARTITION on the table.
Why EXCHANGE PARTITION fails for INSTANT column
Let’s do a shallow dive into the MySQL codebase.
I was looking to see why it is a problem to fix this issue or accept this as a feature by exploring the MySQL git repository. This is what I’ve understood from the expedition.
The INSTANT COLUMN(s) check is actually handled at the storage-engine level in storage/innobase/handler/handler0alter.cc, in the exchange_partition_low() function – relevant code section is below.
int ha_innopart::exchange_partition_low(uint part_id, dd::Table *part_table,dd::Table *swap_table) {
...
if (dd_table_has_instant_cols(*part_table) ||
dd_table_has_instant_cols(*swap_table)) {
my_error(ER_PARTITION_EXCHANGE_DIFFERENT_OPTION, MYF(0),
"INSTANT COLUMN(s)");
return true;
}
...Notice the || in the condition. MySQL does not compare the instant-column metadata of the two tables. It simply checks whether either side has instant-column metadata. If either does, the EXCHANGE operation is rejected.
But how does MySQL decide if a table has instant columns? That’s handled by dd_table_has_instant_cols(), defined in storage/innobase/include/dict0dd.h. Relevant code blow is as follows:
inline bool dd_table_has_instant_cols(const dd::Table &table) {
if (table.is_temporary()) {
return false;
}
bool instant_v1 = dd_table_is_upgraded_instant(table);
bool instant_v2 = dd_table_has_row_versions(table);
bool instant = instant_v1 || instant_v2;
/* If table has instant columns, make sure they are consistent with DD */
ut_ad(!instant || dd_instant_columns_consistent(table));
return (instant);
}So, either dd_table_is_upgraded_instant() or dd_table_has_row_versions() returning true is enough for dd_table_has_instant_cols() to return true (have INSTANT column).
But how does the functions find that the table has instant columns? It seems like this is something coming from MySQL’s data dictionary meta data. What I understood is, we execute an ALTER TABLE with ALGORITHM=INSTANT, InnoDB updates the MySQL Data Dictionary (DD) metadata. At a high level, this is something like
ALTER TABLE
|
v
InnoDB ALTER implementation
|
v
Update dd::Table / dd::Column
|
+--> table.se_private_data()
| |
| +--> instant_col [V1]
|
+--> column.se_private_data()
|
+--> version_added
+--> physical_pos [V2]This metadata is part of the server’s dd::Table / dd::Column objects. That’s what the functions above inspect when the dd::Table is passed as the table argument. You might have noted V1 and V2 here. An easy explanation to that is:
V1 = older table-level instant metadata.
V2 = newer row-version/column-level metadata.
So full picture of our ALTER TABLE … EXCHANGE PARTITION failure is as follows:
ALTER TABLE ... ALGORITHM=INSTANT
|
v
InnoDB updates DD
|
+--------+---------+
| |
v v
dd::Table dd::Column
| |
se_private_data() se_private_data()
| |
"instant_col" "physical_pos"
| |
+--------+---------+
|
v
Persistent MySQL DD
|
v
EXCHANGE PARTITION Command executed
|
v
dd_table_has_instant_cols()
|
+--> V1: instant_col exists?
|
+--> V2: physical_pos exists?
|
v
TRUE
|
v
EXCHANGE rejectedI have tried to keep this simple for my own understandings and I hope it make sense for most part. If you see any error, do not hesitate to correct me. Long ago I said “To err is human, To restore is DBA” but I think it is time to extend that with “To error is AI, To acknowledge it confidently, hallucinate again, for more tokens – is still AI.”
I wish MySQL can fix this, like others did. I’m going to try asking my AI friend to compare the code bases and see what I can understand, when time permits.
TL;DR and Your Action
Do not run ALTER TABLE with INSTANT algorithm on a partitioned table. EXCHANGE PARTITION will refuse the operation with that metadata. The only fix there is to perform a table rebuild.
Run the query to identify Partitioned table having INSTANT columns. At-least you will know in advance.
SELECT DISTINCT
SUBSTRING_INDEX(t.NAME, '/', 1) AS table_schema,
SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '/', -1), '#p#', 1) AS table_name,
IF(p.TABLE_NAME IS NULL, 'NO', 'YES') AS has_partitioning,
c.NAME AS column_name, c.POS, c.HAS_DEFAULT
FROM information_schema.INNODB_COLUMNS c
JOIN information_schema.INNODB_TABLES t ON c.TABLE_ID = t.TABLE_ID
LEFT JOIN (
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.PARTITIONS
WHERE PARTITION_NAME IS NOT NULL
) p ON p.TABLE_SCHEMA = SUBSTRING_INDEX(t.NAME, '/', 1)
AND p.TABLE_NAME = SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '/', -1), '#p#', 1)
WHERE c.HAS_DEFAULT = 1
ORDER BY table_schema, table_name, c.POS;Conclusion
ALGORITHM=INSTANT is a great feature, especially when dealing with large tables where a traditional table rebuild is something we really want to avoid.
But this experience shows that an INSTANT ADD COLUMN has consequences for partitioned tables. If you are using EXCHANGE PARTITION, adding a column with ALGORITHM=INSTANT can break that workflow later.
INSTANT DDL can save you a table rebuild today but prevent an EXCHANGE PARTITION tomorrow.