ddl: support dropping a column with single-value indices

[bb7133]

Feature Request

Is your feature request related to a problem? Please describe:
Support dropping a column with single-value indices. For now, the ALTER TABLE ... DROP COLUMN statement returns an error when dropping a column that is covered with index:

tidb> create table t(a int, b int, index idx_b(b));
Query OK, 0 rows affected (0.01 sec)

tidb> alter table t drop column b;
ERROR 8200 (HY000): can't drop column b with index covered now

Describe the feature you'd like:
See #3364 for the full feature requests.

Describe alternatives you've considered:
Manually, the ALTER TABLE ... DROP COLUMN in TiDB can be archived by 2 separate steps:

1. Drop all indices that cover the column to be dropped.
2. Drop the column without any index.

However, for some third-party frameworks that relies on dropping columns directly, workarounds have to be done for this limitation. This can be disappointing.

Teachability, Documentation, Adoption, Migration Strategy:
Compared with #3364, which is the full support for ALTER TABLE ... DROP COLUMN, this task is relatively easy. We can achieve by combining the current codes of ALTER TABLE ... DROP COLUMN and ALTER TABLE ... DROP INDEX. More help is available by referring:

• The internal algorithm of TiDB online DDL, more specifically AddColumn action and AddIndex action.
• The key schema state change codes:
  • onDropColumns()
  • onDropIndex()
  • applyDiff(), which updates the information schema served as a schema cache in TiDB server.
• The related PR that supports atomical multiple schema objects change: ddl: Support the operation of adding multi-columns #15540

Here are some major code changes that may be helpful:

1. Update function isDroppableColumn by removing the restriction of dropping column/columns that are covered by single-value indicies

2. Update function onDropColumn and onDropColumns(maybe they can/should be combined together): check if they're droppable and retrieve all the single-value indices that cover the columns, and change the state of indicies with the columns(StatePublic -> StateWriteOnly -> StateDeleteOnly -> StateDeleteReorgnization -> StateNone). It would be much better if we can reuse the codes in onDropIndex by some code refactorings.

3. Update function insertJobIntoDeleteRangeTable to add the range information for dropped columns along with all the indicies.

4. May need to update ApplyDiff. It is possible that this function works fine without modification because the state of column/index has been updated together with the new TableInfo. However, it is worth checking this function still.

5. Add unit tests/integration tests. It should be aware that, for a specific column, the related index/indices can be changed when the 'DropTable' DDL job is waiting in the DDL queue and it's worth making some tests for it.

6. Update rollingbackDropColumn function to process rollback.

[lance6716]

https://github.com/pingcap/dm/blob/dc72ee8bf4a83c97b4962102649608899704c77c/syncer/syncer.go#L2081-L2093

https://github.com/pingcap/dm/blob/dc72ee8bf4a83c97b4962102649608899704c77c/pkg/schema/tracker.go#L146-L171

DM has patched this some days ago, FYR

[blacktear23]

@bb7133 there has 6 item, we should edit rollingback.go: rollingbackDropColumn function to process rollback

[bb7133]

@bb7133 there has 6 item, we should edit rollingback.go: rollingbackDropColumn function to process rollback

Thanks, updated.

I will close this feature since #18852 is merged.

[zz-jason]

can we backport it to release 4.0?

[bb7133]

can we backport it to release 4.0?

Unless it is critical for some users, it's better to keep it in V5.0 since this feature is just a part of the multiple schema changes.