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:

• [x] #34037

Refactoring:

• [x] #32820
• [x] #33631
• [x] #33679

Framework:

• [x] #35231
• [x] #35358
• [x] #35429
• [x] #36167

Key features:

• [x] #35429
• [x] #35737
• [x] #35883
• [x] #35989
• [x] #36020
• [x] #36040
• [x] #36148
• [x] #36205
• [x] #36236

Others:

• [x] #35862
• [x] #35882
• [x] #35960
• [x] https://github.com/pingcap/tiflow/issues/6025
• [x] #36262
• [x] #36282
• [x] #36421

Admin & Monitor

• [x] #36168
• [x] #35977

User docs:

• [x] https://github.com/pingcap/docs-cn/pull/10457

Unsolved issues:

• [ ] Support cascade DDLs like add column a, add index i(a)
• [ ] #34535
• [x] https://github.com/pingcap/tidb/issues/41602
• [x] https://github.com/tangenta/tidb/issues/100
• [ ] https://github.com/tangenta/tidb/issues/92
• [ ] https://github.com/tangenta/tidb/issues/93
• [ ] https://github.com/tangenta/tidb/issues/119
• [ ] https://github.com/tangenta/tidb/issues/126
• [ ] #3364
• [x] #28421
• [ ] #38568
• [ ] #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.

[lance6716]

seems the TODO list item "Dropping column with index covered isn't fully compatible with mysql" is marked as done, but its issue is still open

[donbowman]

mautic requires:

ALTER TABLE leads ADD fax VARCHAR(255) DEFAULT NULL, ADD preferred_locale VARCHAR(255) DEFAULT NULL, ADD attribution_date DATETIME DEFAULT NULL, ADD attribution DOUBLE PRECISION DEFAULT NULL, ADD website LONGTEXT DEFAULT NULL, ADD facebook VARCHAR(255) DEFAULT NULL, ADD foursquare VARCHAR(255) DEFAULT NULL, ADD instagram VARCHAR(255) DEFAULT NULL, ADD linkedin VARCHAR(255) DEFAULT NULL, ADD skype VARCHAR(255) DEFAULT NULL, ADD twitter VARCHAR(255) DEFAULT NULL;

as a statement to install, which also gives: ERROR 8200 (HY000): Unsupported multi schema change

[AndrewWang-at]

One pattern we use with MySQL is a single ALTER TABLE that drops an index and adds a new index with the same name. This is important since we have queries that use FORCE INDEX and expect a specific index name.

Will this use case be addressed as part of this issue?

[yahonda]

I would like this feature is supported because Ruby on Rails migration is making use of this feature by setting bulk: true in the migration files. https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-change_table

[dveeden]

I think a good example of where one wants to use this is to change the primary key.

If executing it like this is separate steps the problem is that for a bit of time there isn't a PK that could cause logical issues with the data (e.g. new rows with duplicate PKs). Also this would rebuild the table twice and taking more time than if actions were combined.

CREATE TABLE t1 (id int primary key, c1 int not null);
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY (id, c1);

A better way to do this would be something like:

CREATE TABLE t1 (id int primary key, c1 int not null);
ALTER TABLE t1 ADD UNIQUE KEY u1 (id, c1);
ALTER TABLE t1 DROP PRIMARY KEY;
ALTER TABLE t1 ADD PRIMARY KEY (id, c1);
ALTER TABLE t1 DROP KEY u1;

And with things combined:

CREATE TABLE t1 (id int primary key, c1 int not null);
ALTER TABLE t1
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (id, c1);

Working with PKs like this may not work in the initial version, but it would be good to consider this for later revisions.

(the CREATE TABLE... here is only to show that the t1 table is like, in a realistic senario there is a lot of time and modifications in the table between the creation of the table and the modification of the PK)

[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.