MySQL Bugs: #104970: EXCHANGE PARTITION fails for table altered with ALGORITHM=INSTANT

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

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

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.

Can you accept this as feature request? It will be very helpful for partitioned tables while adding new columns.

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.

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.

Thank you, Jon.

Hello MySql Team,

This adds restriction on adding new column in tables.

For big size tables it requires re-populating new table with INPLACE column, this is time consuming and heavy operation for production environment.

can you work on this feature actively as its been 3 years since its reported.