Support changing multiple schema in ALTER TABLE

[zimulala]

Feature Request

Is your feature request related to a problem? Please describe:

Currently TiDB only supports one schema change per SQL statement and, in a few cases, multi-schema changes.

Therefore, users often encounter incompatible DDLs when migrating from other databases. The only way is to rewrite a multi-schema DDL into several separated DDLs. For the users that rely on frameworks like "flyway", the rewrite is not even possible.

Describe the feature you'd like:

Support multiple schema changes in a single SQL statement, including ADD , ALTER , DROP and CHANGE for columns and indexes, and changes to table options. For example:

CREATE TABLE t (a INT, c INT);
ALTER TABLE t ADD COLUMN b INT, 
 MODIFY COLUMN c CHAR(5),
 ADD INDEX idx(a),
 ADD PRIMARY KEY (c),
 COMMENT = "comments for t";

Refer to https://dev.mysql.com/doc/refman/5.7/en/alter-table.html.

Describe alternatives you've considered:

Rewriting the SQL at application level works, but it is not suitable in all the cases.

Tasks

Design:

• docs: add a proposal for multi-schema change #34037

Refactoring:

• ddl: refactor adjustColumnInfoInAddColumn for multi-schema change #32820
• ddl: refactor modify column for multi-schema change #33631
• ddl: extract data reorg code to separate functions #33679

Framework:

• ddl: add data structure SubJob for multi-schema change #35231
• ddl: add front-end checks for multi-schema change #35358
• ddl: implement the core for multi-schema change #35429
• *: remove system var tidb_enable_change_multi_schema #36167

Key features:

• ddl: implement the core for multi-schema change #35429
• ddl: support drop columns for multi-schema change #35737
• ddl: support drop indexes for multi-schema change #35883
• ddl: support add indexes for multi-schema change #35989
• ddl: support adding constraint with column in one spec #36020
• ddl: support modify column for multi-schema change #36040
• ddl: support rename index and columns for multi-schema change #36148
• ddl: support alter index for multi-schema change #36205
• ddl: support alter table options for multi-schema change #36236

Others:

• ddl: remove onDropColumns and onAddColumns #35862
• ddl: make ToProxyJob return value to avoid heap allocation #35882
• ddl: remove onDropIndexes #35960
• Support multi-schema change DDL action for TiCDC tiflow#6025
• ddl: only use one schema version for the non-revertible step #36262
• ddl: handle rolling back modify columns correctly #36282
• ddl: fix update panic in the middle of multi-schema change #36421

Admin & Monitor

• *: support show ddl jobs for sub-jobs #36168
• *: add telemetry support for multi-schema change #35977

User docs:

• Update the multi-schema change DDL limitation docs-cn#10457

Unsolved issues:

• Support cascade DDLs like add column a, add index i(a)
• gorm: modify column with unique attribute return error #34535
• Optimize multi-schema change for add indexes #41602
• Reorg with empty table too slow tangenta/tidb#100
• Support alter table x rename xx tangenta/tidb#92
• Cannot drop column after dropping covered index tangenta/tidb#93
• Support drop and add primary key in one statement tangenta/tidb#119
• Support rename columns interchangeably tangenta/tidb#126
• dropping column with index covered isn't fully compatible with mysql #3364
• Support the operation of dropping multi-foreign-keys in one statement... #28421
• Support drop and add column with same name in one statement #38568
• issue of multi-schema change on same column #38897

[IANTHEREAL]

Cool! @july2993 @csuzhangxc please pay attention to this change.

We should consider the compatibility issues in tidb binlog replication, like replicating binlog from a newer version tidb cluster to old version tidb cluster.

[zimulala]

Related to #5166.

[eikodev]

Looking forward to this one so I can use TiDB with Prisma properly.

[shtse8]

Looking forward to this one so I can use TiDB with Prisma properly.

Me too. Although Tidb is 100% mysql compatible, without mutiple schema support, many Orms cannot function properly.

[dveeden]

Maybe #39143 and #38453 should be added to the list of items?

[bb7133]

Close it since most changes have been supported already.