MySQL Forums
Forum List  »  InnoDB

MySQL 8.0.29 Algorithm=Instant new versioned DDL + broken partitioning functionality
Posted by: Jan Zmeskal
Date: June 09, 2022 03:48AM

Hello,

8.0.29 introduces Algorithm=INSTANT changes. In previous versions only counter of added columns was present, you could add only to end of table, couldn't drop. It was imo sufficient. Now DDL versioning was introduced (limited to 64 versions - hidden in Data dictionary) enabling other instant operations.


Previous reply in mysql bugs regarding broken partitioning functionality with algorithm=instant is "we'll document it won't work", done.

https://bugs.mysql.com/bug.php?id=104970
https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html
"Once one or more columns have been added to a partitioned table using ALGORITHM=INSTANT, it is no longer possible to exchange partitions with that table."

Nice "FIX" ...


This algorithm is default, so first time you use this Instant algorithm and you're done. You can't exchange partitions, migrate them as imported tables, etc. They introduced "flush tables tablename for export" what generates .cfg file from hidden (facepalm) Data dictionary, on unlock tables it's immediately deleted. Than it can be used in "alter table tablename2 import tablespace" and fixes mysql.columns dd table for imported table, but not mysql.tables dd table, version remains =0.


When you try to migrate .ibd file only (like you could in mysql5.7), you end with crash in 8.0.29 Response on bug report "Unable to reproduce, you're dropping nonexistent table" (who cares about "if exists")
https://bugs.mysql.com/bug.php?id=107517


The instant algorithm is imo highly unstable feature, should be explicitly enabled by admins and tested in their environment, disabled by default.


Is there an option to disable it, other than explicitly state "algorithm=inplace" or "algorithm=copy" in every alter and when omitted, you're done ?



Regards,
Jan

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL 8.0.29 Algorithm=Instant new versioned DDL + broken partitioning functionality
103
June 09, 2022 03:48AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.