MySQL 8.0.29 Algorithm=Instant new versioned DDL + broken partitioning functionality
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