{"id":3627,"date":"2026-09-14T13:36:10","date_gmt":"2026-09-14T13:36:10","guid":{"rendered":"https:\/\/kedar.nitty-witty.com\/blog\/?p=3627"},"modified":"2026-09-14T13:36:10","modified_gmt":"2026-09-14T13:36:10","slug":"mysql-instant-ddl-breaks-exchange-partition","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/mysql-instant-ddl-breaks-exchange-partition","title":{"rendered":"MySQL INSTANT DDL breaks EXCHANGE PARTITION"},"content":{"rendered":"\n

A MySQL partition maintenance script starts failing at the EXCHANGE PARTITION<\/code> step after a recent ALTER TABLE<\/code> operation, with the following error:<\/p>\n\n\n\n

ERROR 1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and table<\/code><\/pre>\n\n\n\n
This MySQL ERROR 1731 is caused by an earlier ALTER TABLE<\/code> run with ALGORITHM=INSTANT<\/code>. 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.<\/p>\n\n\n\n
Producing MySQL ERROR 1731<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
mysql> CREATE TABLE `api_request_log` (\n    ->   `id`             bigint unsigned NOT NULL AUTO_INCREMENT,\n    ->   `tenant_id`      int unsigned    NOT NULL,\n    ->   `endpoint`       varchar(191)    NOT NULL,\n    ->   `http_method`    varchar(10)     NOT NULL,\n    ->   `status_code`    smallint unsigned NOT NULL,\n    ->   `response_ms`    int unsigned    NOT NULL,\n    ->   `client_ip`      varchar(45)     NOT NULL,\n    ->   `user_agent`     varchar(255)    DEFAULT NULL,\n    ->   `request_bytes`  int unsigned    NOT NULL DEFAULT '0',\n    ->   `response_bytes` int unsigned    NOT NULL DEFAULT '0',\n    ->   `trace_id`       char(32)        NOT NULL,\n    ->   `logged_at`      datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,\n    ->   PRIMARY KEY (`id`,`logged_at`),\n    ->   KEY `idx_tenant_logged`   (`tenant_id`,`logged_at`),\n    ->   KEY `idx_endpoint_status` (`endpoint`,`status_code`,`logged_at`),\n    ->   KEY `idx_trace`           (`trace_id`)\n    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n    -> PARTITION BY RANGE COLUMNS(`logged_at`)\n    -> (PARTITION p202606 VALUES LESS THAN ('2026-07-01') ENGINE = InnoDB,\n    ->  PARTITION p202607 VALUES LESS THAN ('2026-08-01') ENGINE = InnoDB,\n    ->  PARTITION p202608 VALUES LESS THAN ('2026-09-01') ENGINE = InnoDB,\n    ->  PARTITION p202609 VALUES LESS THAN ('2026-10-01') ENGINE = InnoDB);\nQuery OK, 0 rows affected (0.71 sec)\n\nmysql> ALTER TABLE api_request_log ADD COLUMN error_code varchar(32) DEFAULT NULL, ALGORITHM=INSTANT;\nQuery OK, 0 rows affected (0.28 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql> CREATE TABLE api_request_log_p202607 LIKE api_request_log;\nQuery OK, 0 rows affected (1.28 sec)\n\nmysql> ALTER TABLE api_request_log_p202607 REMOVE PARTITIONING;\nQuery OK, 0 rows affected (0.87 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql> ALTER TABLE api_request_log EXCHANGE PARTITION p202607 \nWITH TABLE api_request_log_p202607 WITHOUT VALIDATION;\nERROR 1731 (HY000): Non matching attribute 'INSTANT COLUMN(s)' between partition and table<\/strong><\/code><\/pre>\n\n\n\n
What is MySQL ERROR 1731, “Non matching attribute ‘INSTANT COLUMN(s)'”?<\/h2>\n\n\n\n
Error 1731 is ER_PARTITION_EXCHANGE_DIFFERENT_OPTION<\/code>. MySQL raises it when ALTER TABLE ... EXCHANGE PARTITION<\/code> finds that the partition and the standalone table do not have identical structure.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Let’s meet the MySQL’s (not a) bug<\/h2>\n\n\n\n
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 \u2013 EXCHANGE PARTITION fails for table altered with ALGORITHM=INSTANT<\/a>.<\/p>\n\n\n\n
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.<\/em>“. 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.<\/p>\n\n\n\n
Identifying INSTANT columns in MySQL<\/h2>\n\n\n\n
Just by looking at CREATE TABLE statements we cannot deduce if the column is INSTANT addition. Luckily, we can query the information_schema tables.<\/p>\n\n\n\n
The HAS_DEFAULT<\/code> column in information_schema.INNODB_COLUMNS<\/code> indicates whether a column added using ALTER TABLE ... ADD COLUMN ... ALGORITHM=INSTANT<\/code> has a default value. Since columns added instantly always have a default value, HAS_DEFAULT<\/code> can be used as an indicator of whether a column was added using the INSTANT<\/code> algorithm.<\/p>\n\n\n\n
I use following query to identify such tables with partitioning having INSTANT columns present.<\/p>\n\n\n\n
SELECT DISTINCT\n  SUBSTRING_INDEX(t.NAME, '\/', 1) AS table_schema,\n  SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '\/', -1), '#p#', 1) AS table_name,\n  IF(p.TABLE_NAME IS NULL, 'NO', 'YES') AS has_partitioning,\n  c.NAME AS column_name, c.POS, c.HAS_DEFAULT\nFROM information_schema.INNODB_COLUMNS c\nJOIN information_schema.INNODB_TABLES  t ON c.TABLE_ID = t.TABLE_ID\nLEFT JOIN (\n    SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME\n    FROM information_schema.PARTITIONS\n    WHERE PARTITION_NAME IS NOT NULL\n) p ON p.TABLE_SCHEMA = SUBSTRING_INDEX(t.NAME, '\/', 1)\n   AND p.TABLE_NAME   = SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '\/', -1), '#p#', 1)\nWHERE c.HAS_DEFAULT = 1\nORDER BY table_schema, table_name, c.POS;\n+--------------+------------------------+------------------+--------------+-----+-------------+\n| table_schema | table_name             | has_partitioning | column_name  | POS | HAS_DEFAULT |\n+--------------+------------------------+------------------+--------------+-----+-------------+\n| test         | api_request_log        | YES              | error_code   |  12 |           1 |\n+--------------+------------------------+------------------+--------------+-----+-------------+<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
How to fix MySQL Error 1731<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
mysql> ALTER TABLE api_request_log ENGINE=InnoDB;\nQuery OK, 0 rows affected (0.99 sec)\nRecords: 0  Duplicates: 0  Warnings: 0\n\nmysql> ALTER TABLE api_request_log EXCHANGE PARTITION p202607   WITH TABLE api_request_log_p202607 WITHOUT VALIDATION;\nQuery OK, 0 rows affected (0.04 sec)<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
\n
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.<\/p>\n<\/blockquote>\n\n\n\n
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. <\/p>\n\n\n\n
Where is the problem?<\/h2>\n\n\n\n
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<\/a> task and it clearly state this<\/p>\n\n\n\n
\n
FR14: For EXCHANGE PARTITION<\/strong>, to simplify the logic, if either the partition or the table to be swapped is instant<\/strong>, then the operation would be rejected<\/strong> with error ER_PARTITION_EXCHANGE_DIFFERENT_OPTION<\/strong>.<\/p>\n<\/blockquote>\n\n\n\n
This is also something you can read through the MySQL documentation<\/a> that clearly states this limitation with ALGORITHM=INSTANT and partitioning operations:<\/p>\n\n\n\n
\n
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.<\/p>\n<\/blockquote>\n\n\n\n
Why EXCHANGE PARTITION fails for INSTANT column<\/h2>\n\n\n\n
Let’s do a shallow dive into the MySQL codebase.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The INSTANT COLUMN(s)<\/code> check is actually handled at the storage-engine level in storage\/innobase\/handler\/handler0alter.cc<\/code>, in the exchange_partition_low<\/a>()<\/code> function – relevant code section is below.<\/p>\n\n\n\n
int ha_innopart::exchange_partition_low(uint part_id, dd::Table *part_table,dd::Table *swap_table) {\n...\n  if (dd_table_has_instant_cols(*part_table) ||\n      dd_table_has_instant_cols(*swap_table)) {\n    my_error(ER_PARTITION_EXCHANGE_DIFFERENT_OPTION, MYF(0),\n             \"INSTANT COLUMN(s)\");\n    return true;\n  }\n...<\/code><\/pre>\n\n\n\n
Notice the ||<\/code> in the condition. MySQL does not compare the instant-column metadata of the two tables. It simply checks whether either side<\/strong> has instant-column metadata. If either does, the EXCHANGE operation is rejected.<\/p>\n\n\n\n
But how does MySQL decide if a table has instant columns? That’s handled by dd_table_has_instant_cols<\/a>()<\/code>, defined in storage\/innobase\/include\/dict0dd.h<\/code>. Relevant code blow is as follows:<\/p>\n\n\n\n
inline bool dd_table_has_instant_cols(const dd::Table &table) {\n  if (table.is_temporary()) {\n    return false;\n  }\n\n  bool instant_v1 = dd_table_is_upgraded_instant(table);\n  bool instant_v2 = dd_table_has_row_versions(table);\n\n  bool instant = instant_v1 || instant_v2;\n\n  \/* If table has instant columns, make sure they are consistent with DD *\/\n  ut_ad(!instant || dd_instant_columns_consistent(table));\n\n  return (instant);\n}<\/code><\/pre>\n\n\n\n
So, either dd_table_is_upgraded_instant()<\/code> or dd_table_has_row_versions()<\/code> returning true<\/code> is enough for dd_table_has_instant_cols()<\/code> to return true<\/code> (have INSTANT column).<\/p>\n\n\n\n
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<\/code> with ALGORITHM=INSTANT<\/code>, InnoDB updates the MySQL Data Dictionary (DD) metadata. At a high level, this is something like<\/p>\n\n\n\n
ALTER TABLE\n      |\n      v\nInnoDB ALTER implementation\n      |\n      v\nUpdate dd::Table \/ dd::Column\n      |\n      +--> table.se_private_data()\n      |       |\n      |       +--> instant_col       [V1]\n      |\n      +--> column.se_private_data()\n              |\n              +--> version_added\n              +--> physical_pos [V2]<\/code><\/pre>\n\n\n\n
This metadata is part of the server’s dd::Table<\/code> \/ dd::Column<\/code> objects. That’s what the functions above inspect when the dd::Table<\/code> is passed as the table<\/code> argument. You might have noted V1 and V2 here. An easy explanation to that is:<\/p>\n\n\n\n
V1 = older table-level instant metadata.
V2 = newer row-version\/column-level metadata.<\/p>\n\n\n\n
So full picture of our ALTER TABLE … EXCHANGE PARTITION failure is as follows:<\/p>\n\n\n\n
ALTER TABLE ... ALGORITHM=INSTANT\n                 |\n                 v\n        InnoDB updates DD\n                 |\n        +--------+---------+\n        |                  |\n        v                  v\n   dd::Table           dd::Column\n        |                  |\n se_private_data()   se_private_data()\n        |                  |\n   \"instant_col\"      \"physical_pos\"\n        |                  |\n        +--------+---------+\n                 |\n                 v\n        Persistent MySQL DD\n                 |\n                 v\n        EXCHANGE PARTITION Command executed\n                 |\n                 v\n    dd_table_has_instant_cols()\n                 |\n                 +--> V1: instant_col exists?\n                 |\n                 +--> V2: physical_pos exists?\n                 |\n                 v\n              TRUE\n                 |\n                 v\n       EXCHANGE rejected<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
\n
I 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<\/strong>” 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<\/strong>.”<\/p>\n<\/blockquote>\n\n\n\n
I wish MySQL can fix this, like others did<\/strong>.<\/em> I’m going to try asking my AI friend to compare the code bases and see what I can understand, when time permits.<\/p>\n\n\n\n
TL;DR and Your Action<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Run the query<\/strong> to identify Partitioned table having INSTANT columns. At-least you will know in advance.<\/p>\n\n\n\n
SELECT DISTINCT\n  SUBSTRING_INDEX(t.NAME, '\/', 1) AS table_schema,\n  SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '\/', -1), '#p#', 1) AS table_name,\n  IF(p.TABLE_NAME IS NULL, 'NO', 'YES') AS has_partitioning,\n  c.NAME AS column_name, c.POS, c.HAS_DEFAULT\nFROM information_schema.INNODB_COLUMNS c\nJOIN information_schema.INNODB_TABLES  t ON c.TABLE_ID = t.TABLE_ID\nLEFT JOIN (\n    SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME\n    FROM information_schema.PARTITIONS\n    WHERE PARTITION_NAME IS NOT NULL\n) p ON p.TABLE_SCHEMA = SUBSTRING_INDEX(t.NAME, '\/', 1)\n   AND p.TABLE_NAME   = SUBSTRING_INDEX(SUBSTRING_INDEX(t.NAME, '\/', -1), '#p#', 1)\nWHERE c.HAS_DEFAULT = 1\nORDER BY table_schema, table_name, c.POS;<\/code><\/pre>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
ALGORITHM=INSTANT<\/code> is a great feature, especially when dealing with large tables where a traditional table rebuild is something we really want to avoid.<\/p>\n\n\n\n
But this experience shows that an INSTANT ADD COLUMN<\/code> has consequences for partitioned tables. If you are using EXCHANGE PARTITION<\/strong><\/code>, adding a column with ALGORITHM=INSTANT<\/code> can break that workflow later.<\/p>\n\n\n\n
INSTANT DDL can save you a table rebuild today but prevent an EXCHANGE PARTITION<\/code> tomorrow.<\/p>\n","protected":false},"excerpt":{"rendered":"MySQL ERROR 1731 ‘Non matching attribute INSTANT COLUMN(s)’ breaks EXCHANGE PARTITION after ALGORITHM=INSTANT. This blog offers reasoning and fixtures.\n","protected":false},"author":1,"featured_media":3632,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[8,377],"tags":[1245,1246,1242,427,1243,1244],"class_list":["post-3627","post","type-post","status-publish","format-standard","has-post-thumbnail","category-mysql","category-mysql-articles","tag-algorithminstant","tag-er_partition_exchange_different_option","tag-exchange-partition","tag-mysql","tag-mysql-error-1731","tag-non-matching-attribute-instant-column"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t