dropping column with index covered isn't fully compatible with mysql

[qinix]

1. What did you do?

Try to drop a column using ALTER TABLE xxx DROP xx_id.

2. What did you expect to see?

According to MySQL's manual

"If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well."

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

3. What did you see instead?

it says

"can't drop column ride_id with index covered now"

4. What version of TiDB are you using (tidb-server -V)?

Git Commit Hash: 64830c93b74356f2a6ab37a95a9c9f4113d1db7d
UTC Build Time:  2017-05-27 08:43:18

Mentor(s)

• @zimulala

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

Recommended Skills

• DDL
• Golang

Learning Materials

MySQL's document for syntax reference.
TiDB DDL architecture.

According to the way index should be handled with, this feature can be sub-divided into 2 tasks:

• support dropping a column with single-value indices ddl: support dropping a column with single-value indices #18812
• support dropping columns with composite indices ddl: support dropping columns with composite indices  #19196

[holys]

@qinix For above 4, please show specific version information by executing tidb-server -V like:

Git Commit Hash: 2198d0e7521a5c6c493a4c7af58509b6b277fd6e
UTC Build Time:  2017-05-23 03:07:15

[qinix]

@holys

Git Commit Hash: 64830c93b74356f2a6ab37a95a9c9f4113d1db7d
UTC Build Time:  2017-05-27 08:43:18

[holys]

@qinix https://pingcap.com/doc-mysql-compatibility-zh

We don't support dropping column with index covered now. You may drop the index first, and then drop the column.

See https://github.com/pingcap/tidb/blob/master/ddl/ddl_api.go#L888

[kolbe]

Here's a test case that illustrates this behavior:

create table t1 (id int unsigned not null auto_increment primary key, col1 int, index(col1));
alter table t1 drop column col1;

Output:

tidb> create table t1 (id int unsigned not null auto_increment primary key, col1 int, index(col1));
Query OK, 0 rows affected (0.32 sec)

tidb> alter table t1 drop column col1;
ERROR 1105 (HY000): can't drop column col1 with index covered now

[zz-jason]

confirmed it still unsupported:

TiDB(root@127.0.0.1:test) > create table t(a bigint, b bigint, index idx(a, b));
Query OK, 0 rows affected (0.09 sec)

TiDB(root@127.0.0.1:test) > alter table t drop column a;
ERROR 8200 (HY000): can't drop column a with index covered now

tidb version:

TiDB(root@127.0.0.1:test) > select * from information_schema.cluster_info;
+------+----------------------+----------------------+--------------+------------------------------------------+---------------------------+---------------------+
| TYPE | INSTANCE             | STATUS_ADDRESS       | VERSION      | GIT_HASH                                 | START_TIME                | UPTIME              |
+------+----------------------+----------------------+--------------+------------------------------------------+---------------------------+---------------------+
| tidb | 192.168.56.103:4000  | 192.168.56.103:10080 | 4.0.0-beta.2 | b2922a70987e0b3a9b5e1392375af156162de346 | 2020-07-20T20:43:39+08:00 | 23h11m36.347805s    |
| pd   | 192.168.56.103:2379  | 192.168.56.103:2379  | 4.1.0-alpha  | d9bd722fd81413c46d1d63b6d03e7b9e4e3543ec | 2020-07-20T20:38:08+08:00 | 23h17m7.347809528s  |
| tikv | 192.168.56.103:20160 | 192.168.56.103:20180 | 4.1.0-alpha  | 22a94c81211d805c5938b0812482fa63e6696558 | 2020-07-20T20:43:28+08:00 | 23h11m47.347811286s |
+------+----------------------+----------------------+--------------+------------------------------------------+---------------------------+---------------------+
3 rows in set (0.01 sec)