Tracking: Online DDL partitioning changes improved support

[shlomi-noach]

Feature Description

Partitioning changes are quite nuanced. Some are perfectly applicable to Online DDL, some are expected to run directly, and some outright have to run directly and are incorrect to run through Online DDL.

We want Vitess Online DDL, likely through schemadiff, to be more aware of partitioning nuances. Examples:

• Setting a partition spec on a non-partitioned table is best accomplished via Online DDL.
• Re-hashing a hashed partition table is likewise best via Online DDL.
• Completely changing a partition schema, same.
• Coalescing a partition is likely better with Online DDL. Even though Online DDL will completely rewrite the entire table, it will do so non-blocking, where a direct DDL will perhaps only operate on two or a few partitions, but will hold write locks for the entire (long) operation.
• Adding a range partition (rotating forward) is best achieved via direct DDL. Online DDL will still be correct, but unneeded and wasteful, since adding the partition is an immediate operation.
• Dropping a range partition (discarding oldest partition) should be strictly done with direct DDL. Running Online DDL will be incorrect because with Online DDL, the next-oldest partition will "adopt" the rows of the dropped partition, and this is unexpected because the end result should have those rows removed.

There is furthermore the fact that MySQL does not support mixing and matching partitioning and non-partitioning changes in a single ALTER TABLE statement. e.g. one cannot ADD COLUMN and DROP PARTITION in the same statement. Also, one cannot add two partitions on a RANGE partitioned table, and likewise one cannot both ADD and DROP partitions in the same statement. Interestingly it's OK to DROP multiple RANGE partitions. schemadiff already recognizes some (most?) of these scenarios, but more work is needed.

Some prior work was done in:

• https://github.com/vitessio/vitess/pull/10315
• https://github.com/vitessio/vitess/pull/10402
• https://github.com/vitessio/vitess/pull/11591
• https://github.com/vitessio/vitess/pull/14878
• https://github.com/vitessio/vitess/pull/14883
• https://github.com/vitessio/vitess/pull/14900

Use Case(s)

Better schema management; vitess to auto-resolve best approach to schema changes.

[shlomi-noach]

There is furthermore the fact that MySQL does not support mixing and matching partitioning and non-partitioning changes in a single ALTER TABLE statement.

This is actually solved implicitly by:

• sqlparser, which will now parse such queries in the first place, and
• schemadiff itself, which will not generate such an ALTER TABLE statement (when schemadiff sees such a complex diff, it breaks it down into subsequent ALTER TABLE queries).

[shlomi-noach]

https://github.com/vitessio/vitess/pull/15698 fulfills all that we need right now on the vitess side, supporting RANGE partition rotation.