Support multiple schema change

[tangenta]

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";

Describe alternatives you've considered:

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

Teachability, Documentation, Adoption, Migration Strategy:

TBD

Tasks

Code branch: pingcap#32899 (for CI test)

Design:

• Add a design document for multi-schema change #2

Framework & refactoring:

• Add a basic framework for multi-schema change #3
• Reimplement checkMultiSpecs func #5
• Reimplement onAddColumns, onDropColumns, onDropIndexes #7
• Reimplement onDropColumn #18
• Prevent notifying ddl/stats workers multiple times #14
• Reimplement the error handling for multi-schema change #28
• Put multi-schema change jobs to a proper DDL queue #40
• Allocate the element ID correctly for GC delete range #67
• Remove the system variable tidb_enable_change_multi_schema #109

Key features:

• Support multi-schema change for add columns #4
• Support multi-schema change for add indexes #38
• Support multi-schema change for drop columns #15
• Support multi-schema change for drop index #21
• Support multi-schema change for modify/change column #39
• Support multi-schema change for rename index #47
• Support multi-schema change for rename column and change column #60
• Support multi-schema change for alter column #69
• Support multi-schema change for alter index visible and alter index invisible #77
• Support multi-schema change for alter table options #88

Not support alter stmts:

• Support multi-schema change for add partition #59
• Support multi-schema change for truncate partition #61
• Support multi-schema change for drop partition #62

Admin & Monitor

• Support display multi-schema change job with admin show ddl jobs #8
• Support admin cancel ddl jobs
• add MultiSchemaChange to metrics/ddl.go #55
• Improve the log output by multi-schema change #83

Tests

• Enhance the tests for multi-schema change #41
• Add Multi-schema-change Jobs to schrddl #63

Bugs:

https://github.com/tangenta/tidb/issues?q=is%3Aissue++label%3Abug+

Docs:

• Update docs DDL的限制 and MySQL 兼容性

Optional:

• Support cascade DDLs like add column a, add index i(a)
• Optimize multi-schema change for add indexes #101
• Reorg with empty table too slow #100
• Support alter table x rename xx #92
• Cannot drop column after dropping covered index #93
• gorm: modify column with unique attribute return error pingcap/tidb#34535
• Support drop and add primary key in one statement #119
• Support rename columns interchangeably #126