Bug #104970 EXCHANGE PARTITION fails for table altered with ALGORITHM=INSTANT
Submitted: 17 Sep 2021 11:17 Modified: 22 Sep 2021 18:54
Reporter: Amish Shah Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: Jon Stephens CPU Architecture:Any
Tags: add column, erro 1731, exchange partition, instant algorithm

[17 Sep 2021 11:17] Amish Shah
Description:
We have large partitioned tables and we need to add columns in this tables.In view of avoid downtime we are adding column using Instant Algorithm. We also add column to the archive table using Instant Algorithm. Now when we are going to exchange partition from live table to archive table its failing. 
Its giving error
 
"Error Code: 1731. Non matching attribute 'INSTANT COLUMN(s)' between partition and table"

We have to build both table again or add column using Inplace Algorithm. But it takes time from 1 to 1.5 hour because table has more than 25 M rows and more than 100 columns. This big amount of downtime is not acceptable. 

How to repeat:
/* creating partitioned table */

CREATE TABLE partition_test (
test_id int NOT NULL AUTO_INCREMENT,
user_id varchar(45) DEFAULT NULL,
stats_date date,
PRIMARY KEY (test_id,stats_date)
)
PARTITION BY RANGE COLUMNS(stats_date)
(PARTITION p1 VALUES LESS THAN ('2020-12-31'),
PARTITION p2 VALUES LESS THAN ('2021-12-31')
)

/*creating archive table */

CREATE TABLE archive_test (
test_id int NOT NULL AUTO_INCREMENT,
user_id varchar(45) DEFAULT NULL,
stats_date date,
PRIMARY KEY (test_id,stats_date)
)

/*Adding new column with instant algorithm in both tables */

alter table partition_test  add column instant_test int ,algorithm = instant
alter table archive_test add column instant_test int ,algorithm = instant

/* switching partitions */

alter table partition_test exchange partition p1 with table archive_test

Note that new column added in both tables with instant algorithm.so schema should match in both tables.

But it fails with this message

Error Code: 1731. Non matching attribute 'INSTANT COLUMN(s)' between partition and table	

Suggested fix:
Partitions should be exchanged when columns are added with Instant Algorithm
[17 Sep 2021 13:47] MySQL Verification Team
Hi Mr. Shah,

Thank you for your bug report.

We have discovered, by analysing our code, that you can not perform the EXCHANGE operation if you had instantly added columns in either table(s) or partition(s).

However, we have also discovered that this is not documented at all, in our Reference Manual. 

Furthermore, we discovered that there is a whole family of errors, related to the EXCHANGE operation, that are not documented at all in our Manual.

Hence, this report is verified as a Documentation bug.

Thank you for your report.
[17 Sep 2021 14:36] Amish Shah
Can you accept this as feature request? It will be very helpful for partitioned tables while adding new columns.
[20 Sep 2021 12:10] MySQL Verification Team
Hi Mr. Shah,

We are afraid that it is not possible. There are premises on which EXCHANGE is designed, so it would require a whole new redesign and reprogramming of the feature.

However, we shall pass your note to the team in charge.
[22 Sep 2021 18:54] Jon Stephens
Fixed in mysqldoc rev 70876.

The changes should appear in the online documentation within a day or two.

Thanks for bringing this to our attention.

Closed.
[23 Sep 2021 12:08] MySQL Verification Team
Thank you, Jon.