Support the operation of dropping multi-indexes in one statement

[zimulala]

Feature Request

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

create table t(a int, b int, key idx1(a), key idx2(b));
alter table t drop index idx1, drop index idx2;
ERROR 8200 (HY000): Unsupported multi schema change

Describe the feature you'd like:

mysql> alter table t drop index idx1, drop index idx2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Support adding multi-columns in one statement, the syntax as follows:

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option:
DROP {INDEX | KEY} index_name

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

We can refer to the implementation of #5092(Support the operation of adding multi-columns).

Score

1200

SIG Slack Channel

You can join #sig-ddl on slack in your spare time to discuss and get help with mentors or others.

Mentor(s)

• @AilinKid
• @zimulala

Recommended Skills

• DDL
• Golang

Learning Materials

MySQL's document for syntax reference.
TiDB DDL architecture

[zimulala]

@chanme picked up this issue.

[chanme]

@zimulala I'm trying to understand the following codes of the function onDropIndex and I have a few questions.

1. when would job.IsRollingback() happen? and if it does, what is the purpose of setting job.Args[0] = indexInfo.ID
2. for the other case, what is the purpose of setting getPartitionIDs into the args.
   

   tidb/ddl/index.go

   Lines 645 to 654 in 8a66104

   	// Finish this job.
   	if job.IsRollingback() {
   	job.FinishTableJob(model.JobStateRollbackDone, model.StateNone, ver, tblInfo)
   	job.Args[0] = indexInfo.ID
   	// the partition ids were append by convertAddIdxJob2RollbackJob, it is weird, but for the compatibility,
   	// we should keep appending the partitions in the convertAddIdxJob2RollbackJob.
   	} else {
   	job.FinishTableJob(model.JobStateDone, model.StateNone, ver, tblInfo)
   	job.Args = append(job.Args, indexInfo.ID, getPartitionIDs(tblInfo))
   	}

[zimulala]

@zimulala I'm trying to understand the following codes of the function onDropIndex and I have a few questions.

1. when would job.IsRollingback() happen? and if it does, what is the purpose of setting job.Args[0] = indexInfo.ID
2. for the other case, what is the purpose of setting getPartitionIDs into the args.
   

   tidb/ddl/index.go

   Lines 645 to 654 in 8a66104

   	// Finish this job.
   	if job.IsRollingback() {
   	job.FinishTableJob(model.JobStateRollbackDone, model.StateNone, ver, tblInfo)
   	job.Args[0] = indexInfo.ID
   	// the partition ids were append by convertAddIdxJob2RollbackJob, it is weird, but for the compatibility,
   	// we should keep appending the partitions in the convertAddIdxJob2RollbackJob.
   	} else {
   	job.FinishTableJob(model.JobStateDone, model.StateNone, ver, tblInfo)
   	job.Args = append(job.Args, indexInfo.ID, getPartitionIDs(tblInfo))
   	}

1. When adding index failed, we may need rollback(we can see the function of convertAddIdxJob2RollbackJob). After calling convertAddIdxJob2RollbackJob, it will do job.IsRollingback(). job.Args[0] = indexInfo.ID is use to remove index data in delete_range.go

2. getPartitionIDs is used for partition table, I think we need to add this function in line648. Could you file an issue?

If I don't reply in time, you can join #sig-ddl on slack in your spare time

[asiafrank]

/pick-up

[ti-challenge-bot]

Pick up success.

[ou-bing]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@ou-bing You did not submit PR within 7 days, so give up automatically.

[ou-bing]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@ou-bing You did not submit PR within 7 days, so give up automatically.

[ou-bing]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@ou-bing You did not submit PR within 7 days, so give up automatically.

[ou-bing]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@ou-bing You did not submit PR within 7 days, so give up automatically.

[ou-bing]

/pick-up

[ti-challenge-bot]

Pick up success.