0

I have a program that regularly exchanges a partition with a table.

MyTable is a partitioned table, and MyTableTemplate is an empty copy of this table, with the same structure but no partitioning.

The process is the following:

CREATE TABLE tmp LIKE MyTableTemplate;

INSERT INTO tmp VALUES (...);
// more inserts...

ALTER TABLE MyTable EXCHANGE PARTITION p1 WITH TABLE tmp;
DROP TABLE tmp;

It worked fine so far, until I needed to add a column to the table:

ALTER TABLE MyTable         ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE MyTableTemplate ADD COLUMN someColumn TINYINT(1) UNSIGNED NULL DEFAULT NULL;

Now when I run EXCHANGE PARTITION WITH TABLE, I get the following message:

General error: 1731 Non matching attribute 'INSTANT COLUMN(s)' between partition and table

Oddly, if I do not add the column at the last position:

ALTER TABLE ... ADD COLUMN ... AFTER someField;

...then the partition exchange works fine!

What's this "INSTANT COLUMN(s) attribute" and what did I do wrong?

Is this a bug? I'm using MySQL 8.0.12.

| improve this question | | | | |
  • 1
    Specify ALGORITHM=INPLACE (or ALGORITHM=COPY if in-place is not supported for your table's engine) in the ALTER TABLE statement explicitly. – Akina Aug 24 '18 at 6:28
  • @Akina Thanks a lot, this works perfectly! Is this expected behaviour? If you can move this to an answer, I'll accept it right away. – Benjamin Aug 24 '18 at 9:31
  • If you can move this to an answer To move the comment to the answer, it is necessary to tell more details. But I'm too lazy to study INSTANT COLUMN so deep, because do not need in this knowledge now. It helps? that's enough. – Akina Aug 24 '18 at 9:40
  • 1
    It sometimes just helps to have a solution, even if there's no full explanation. Other people might stumble upon this question with the same problem, and for this reason, it's always nice to have an accepted answer! I'll write a short one if you don't. – Benjamin Aug 24 '18 at 9:46
1

Specify ALGORITHM=INPLACE (or ALGORITHM=COPY if in-place is not supported for your table's engine) in the ALTER TABLE statement explicitly.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.