The used storage engine can't index column ‘xxxx’ #10644

Mingdr · 2019-05-30T02:51:49Z

Bug Report

Please answer these questions before submitting your issue. Thanks!

What did you do?

I encountered this error while creating the table,

CREATE TABLE `MCH_STORE_LABEL` (
  `LABEL_MAP_ID` int(11) NOT NULL,
  `STORE_ID` int(11) NOT NULL ,
  `LABEL_ID` int(11) NOT NULL ,
  `CREATE_BY` varchar(20) NOT NULL ,
  `CREATE_DATE` datetime NOT NULL,
  `UPDATE_BY` varchar(20) DEFAULT NULL,
  `LAST_MODIFIED` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`LABEL_MAP_ID`)
) ENGINE=Innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ;

Then perform this alteration:

ALTER TABLE `MCH_STORE_LABEL`
ADD INDEX `INDEX_STORE_ID`(`STORE_ID`) USING BTREE;

error log of tidb.log:

2019/05/30 10:12:09.366 session.go:1638: [info] [CRUCIAL OPERATION] con:150591 schema_ver:868 cur_db                                                                                                             :ystpay ALTER TABLE `ystpay`.`MCH_STORE_LABEL`
ADD INDEX `INDEX_STORE_ID`(`STORE_ID`) USING BTREE (by [email protected]).
2019/05/30 10:12:09.366 tidb.go:157: [info] RollbackTxn for ddl/autocommit error.
2019/05/30 10:12:09.367 session.go:892: [warning] con:150591 schema_ver:868 session error:
[ddl:1167]The used storage engine can't index column 'STORE_ID'
github.com/pingcap/errors.AddStack
        /home/jenkins/workspace/build_tidb_master/go/pkg/mod/github.com/pingcap/[email protected]/error                                                                                                             s.go:174
github.com/pingcap/parser/terror.(*Error).GenWithStackByArgs
        /home/jenkins/workspace/build_tidb_master/go/pkg/mod/github.com/pingcap/[email protected]                                                                                                             28070002-74e8cffabf28/terror/terror.go:233
github.com/pingcap/tidb/ddl.buildIndexColumns
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/ddl/index.go:63
github.com/pingcap/tidb/ddl.(*ddl).CreateIndex
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:2632
github.com/pingcap/tidb/ddl.(*ddl).AlterTable
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:1547
github.com/pingcap/tidb/executor.(*DDLExec).executeAlterTable
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/ddl.go:291
github.com/pingcap/tidb/executor.(*DDLExec).Next
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/ddl.go:104
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/adapter.go                                                                                                             :298
github.com/pingcap/tidb/executor.(*ExecStmt).Exec
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/adapter.go                                                                                                             :244
github.com/pingcap/tidb/session.runStmt
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/tidb.go:206
github.com/pingcap/tidb/session.(*session).executeStatement
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/session.go:                                                                                                             889
github.com/pingcap/tidb/session.(*session).execute
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/session.go:                                                                                                             957
github.com/pingcap/tidb/session.(*session).Execute
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/session.go:                                                                                                             910
github.com/pingcap/tidb/server.(*TiDBContext).Execute
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/driver_tidb.                                                                                                             go:246
github.com/pingcap/tidb/server.(*clientConn).handleQuery
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/conn.go:1017
github.com/pingcap/tidb/server.(*clientConn).dispatch
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/conn.go:745
github.com/pingcap/tidb/server.(*clientConn).Run
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/conn.go:582
github.com/pingcap/tidb/server.(*Server).onConn
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/server.go:40                                                                                                             5
runtime.goexit
        /usr/local/go/src/runtime/asm_amd64.s:1333
{
  "currDBName": "ystpay",
  "id": 150591,
  "status": 2,
  "strictMode": true,
  "user": {
    "Username": "root",
    "Hostname": "192.168.0.58",
    "CurrentUser": false,
    "AuthUsername": "root",
    "AuthHostname": "%"
  }
}
2019/05/30 10:12:09.367 conn.go:600: [warning] con:150591 dispatch error:
id:150591, addr:192.168.0.58:29639 status:2, collation:utf8_general_ci, user:root
"ALTER TABLE `ystpay`.`MCH_STORE_LABEL` \r\nADD INDEX `INDEX_STORE_ID`(`STORE_ID`) USING BTREE"
[ddl:1167]The used storage engine can't index column 'STORE_ID'
github.com/pingcap/errors.AddStack
        /home/jenkins/workspace/build_tidb_master/go/pkg/mod/github.com/pingcap/[email protected]/error                                                                                                             s.go:174
github.com/pingcap/parser/terror.(*Error).GenWithStackByArgs
        /home/jenkins/workspace/build_tidb_master/go/pkg/mod/github.com/pingcap/[email protected]                                                                                                             28070002-74e8cffabf28/terror/terror.go:233
github.com/pingcap/tidb/ddl.buildIndexColumns
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/ddl/index.go:63
github.com/pingcap/tidb/ddl.(*ddl).CreateIndex
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:2632
github.com/pingcap/tidb/ddl.(*ddl).AlterTable
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:1547
github.com/pingcap/tidb/executor.(*DDLExec).executeAlterTable
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/ddl.go:291
github.com/pingcap/tidb/executor.(*DDLExec).Next
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/ddl.go:104
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/adapter.go                                                                                                             :298
github.com/pingcap/tidb/executor.(*ExecStmt).Exec
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/executor/adapter.go                                                                                                             :244
github.com/pingcap/tidb/session.runStmt
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/tidb.go:206
github.com/pingcap/tidb/session.(*session).executeStatement
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/session.go:                                                                                                             889
github.com/pingcap/tidb/session.(*session).execute
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/session.go:                                                                                                             957
github.com/pingcap/tidb/session.(*session).Execute
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/session.go:                                                                                                             910
github.com/pingcap/tidb/server.(*TiDBContext).Execute
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/driver_tidb.                                                                                                             go:246
github.com/pingcap/tidb/server.(*clientConn).handleQuery
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/conn.go:1017
github.com/pingcap/tidb/server.(*clientConn).dispatch
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/conn.go:745
github.com/pingcap/tidb/server.(*clientConn).Run
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/conn.go:582
github.com/pingcap/tidb/server.(*Server).onConn
        /home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/server/server.go:40                                                                                                             5
runtime.goexit
        /usr/local/go/src/runtime/asm_amd64.s:1333

What did you expect to see?

Why can't I add the index?
But here's the weird thing,I can add the index when I rebuild this table.So this bug may not fire every time.

What did you see instead?

I see the notice on navicat ：
The used storage engine can't index column 'STORE_ID'

What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
Release Version: v3.0.0-beta-153-g63b49c9e4
Git Commit Hash: 63b49c9
Git Branch: master
UTC Build Time: 2019-03-05 06:08:25
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

The text was updated successfully, but these errors were encountered:

tiancaiamao · 2019-05-30T17:58:43Z

PTAL @winkyao

bb7133 · 2019-05-31T02:54:28Z

hi @Mingdr , thanks for reporting! Unfortunately, I cannot reproduce your issue at the given version. Do you have the full log files so we can dig deeper?

Mingdr · 2019-05-31T06:11:13Z

hi @Mingdr , thanks for reporting! Unfortunately, I cannot reproduce your issue at the given version. Do you have the full log files so we can dig deeper?

Where can I get more log files？
pd.log or tidb.log ?

bb7133 · 2019-06-04T09:34:20Z

tidb.log, please.

Sorry for replying late.

Mingdr · 2019-06-04T09:57:18Z

tidb.log, please.

Sorry for replying late.

I may find the reason：
2019/05/30 10:10:17.150 session.go:1638: [info] [CRUCIAL OPERATION] con:150591 schema_ver:866 cur_db:ystpay CREATE TABLE ystpay.MCH_STORE_LABEL (LABEL_MAP_IDint(0) NOT NULL,STORE_IDint(0) NOT NULL ,LABEL_IDint(0) NOT NULL ,CREATE_BYvarchar(20) NOT ,CREATE_DATEdatetime(0) NOT NULL,UPDATE_BYvarchar(20) NULL,LAST_MODIFIED datetime(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0), PRIMARY KEY (LABEL_MAP_ID) ) (by [email protected]).
I can create the table,
then I show create table :
CREATE TABLE MCH_STORE_LABEL(LABEL_MAP_IDint(11) NOT NULL,STORE_IDint(11) NOT NULL ,LABEL_IDint(11) NOT NULL ,CREATE_BYvarchar(20) NOT NULL ,CREATE_DATEdatetime NOT NULL,UPDATE_BYvarchar(20) DEFAULT NULL,LAST_MODIFIED datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (LABEL_MAP_ID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
I guess it replaced int(0) with int(11) ,But I can not create index.It is my problem or bug?
thanks for your answer !

zimulala · 2019-07-01T08:18:29Z

@Mingdr Thanks for your feedback!
It's a bug for the case as follows:

tidb> create table tt(a int(0) not null);
Query OK, 0 rows affected (0.01 sec)
tidb> alter table tt add index idx(a);
ERROR 1167 (42000): The used storage engine can't index column 'a'

Mingdr · 2019-07-01T08:48:09Z

@Mingdr Thanks for your feedback!
It's a bug for the case as follows:

tidb> create table tt(a int(0) not null);
Query OK, 0 rows affected (0.01 sec)
tidb> alter table tt add index idx(a);
ERROR 1167 (42000): The used storage engine can't index column 'a'

that's all,When can it be resolved.

alivxxx added the type/bug The issue is confirmed as a bug. label Jun 4, 2019

zimulala self-assigned this Jul 2, 2019

zimulala mentioned this issue Jul 3, 2019

ddl: fix the issue that the length of the index is 0. #11045

Merged

zimulala closed this as completed in #11045 Jul 8, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

The used storage engine can't index column ‘xxxx’ #10644

The used storage engine can't index column ‘xxxx’ #10644

Mingdr commented May 30, 2019 •

edited

Loading

tiancaiamao commented May 30, 2019

bb7133 commented May 31, 2019

Mingdr commented May 31, 2019

bb7133 commented Jun 4, 2019

Mingdr commented Jun 4, 2019 •

edited

Loading

zimulala commented Jul 1, 2019

Mingdr commented Jul 1, 2019

The used storage engine can't index column ‘xxxx’ #10644

The used storage engine can't index column ‘xxxx’ #10644

Comments

Mingdr commented May 30, 2019 • edited Loading

Bug Report

tiancaiamao commented May 30, 2019

bb7133 commented May 31, 2019

Mingdr commented May 31, 2019

bb7133 commented Jun 4, 2019

Mingdr commented Jun 4, 2019 • edited Loading

zimulala commented Jul 1, 2019

Mingdr commented Jul 1, 2019

Mingdr commented May 30, 2019 •

edited

Loading

Mingdr commented Jun 4, 2019 •

edited

Loading