Error when creating a new index: Specified key was too long; max key length is 12288 bytes

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 新建索引的时候报错：Specified key was too long; max key length is 12288 bytes

| username: 胡杨树旁

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Encountered Issues]
[Reproduction Path] What operations were performed to encounter the issue
[Issue Phenomenon and Impact]

[Attachments]

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

image
image978×218 10.4 KB

This error occurred when creating an index.

2
| username: 啦啦啦啦啦 | Original post link

Check out this parameter max-index-length

3
| username: 胡杨树旁 | Original post link

The setting is already at the maximum value.

4
| username: 啦啦啦啦啦 | Original post link

Please provide the table structure.

5
| username: 胡杨树旁 | Original post link

CREATE TABLE rbac_datascope (
datascopeid decimal(19,0) NOT NULL COMMENT ‘Serial Number’,
createdate datetime(6) DEFAULT NULL COMMENT ‘Creation Time’,
createuserid varchar(1020) DEFAULT NULL COMMENT ‘Creator ID’,
owneruserid varchar(1020) DEFAULT NULL COMMENT ‘Owner User ID (Reserved Field)’,
partitionid varchar(256) DEFAULT NULL COMMENT ‘Partition ID (Reserved Field)’,
schemaname varchar(64) DEFAULT NULL COMMENT ‘Schema Name (Reserved Field)’,
setid varchar(1020) DEFAULT NULL COMMENT ‘Set ID (Reserved Field)’,
structureid varchar(128) DEFAULT NULL COMMENT ‘All Organization IDs (Reserved Field)’,
tenantid varchar(128) DEFAULT NULL COMMENT ‘Tenant ID (Reserved Field)’,
updatedate datetime(6) DEFAULT NULL COMMENT ‘Update Time’,
updateuserid varchar(1020) DEFAULT NULL COMMENT ‘Update User ID’,
description varchar(4000) DEFAULT NULL COMMENT ‘(Unused)’,
kind varchar(256) DEFAULT NULL COMMENT ‘(Unused)’,
name varchar(1024) DEFAULT NULL COMMENT ‘(Unused)’,
typeid varchar(1024) DEFAULT NULL COMMENT ‘(Unused)’,
accessmode varchar(1020) DEFAULT NULL COMMENT ‘Access Permission Type’,
caseid decimal(19,0) DEFAULT NULL COMMENT ‘Scope Object ID’,
casetype varchar(1020) DEFAULT NULL COMMENT ‘Scope Type’,
dimensionid varchar(1020) DEFAULT NULL COMMENT ‘Data Scope (Organization, Channel, Product Line, Product Category, Product, Function Group) ID’,
dimensionkind varchar(1020) DEFAULT NULL COMMENT ‘Data Scope (Organization, Channel, Product Line, Product Category, Product, Function Group) Identifier’,
dimensionname varchar(1020) DEFAULT NULL COMMENT ‘Data Scope (Organization, Channel, Product Line, Product Category, Product, Function Group) Name’,
dimensiontype varchar(1020) DEFAULT NULL COMMENT ‘Data Scope Dimension Type’,
dirtyflag varchar(1020) DEFAULT NULL COMMENT ‘Dirty Flag’,
approvestatus varchar(1020) DEFAULT NULL COMMENT ‘Approval Status’,
batchid varchar(1020) DEFAULT NULL COMMENT ‘Approval Batch ID’,
dimensionpath varchar(1020) DEFAULT NULL COMMENT ‘Data Permission Dimension Path’,
PRIMARY KEY (datascopeid) /*T![clustered_index] NONCLUSTERED */,
KEY idx_rbac_datascope_dimensionkind (dimensionkind),
KEY idx_rbac_datascope_dimensionname (dimensionname),
KEY idx_rbac_datascope_casetype (casetype),
KEY idx_rbac_datascope_dimensiontype (dimensiontype),
KEY idx_rbac_datascope_caseid (caseid),
KEY idx_rbac_datascope_dimensionid (dimensionid),
KEY idx_rbac_datascope_dimensionpath (dimensionpath)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T!

6
| username: 啦啦啦啦啦 | Original post link

This field type is unreasonable… Why is a status value varchar(1020)?

7
| username: 胡杨树旁 | Original post link

It’s probably based on the maximum value. This might require modifying the field type, right?

8
| username: 啦啦啦啦啦 | Original post link

Well, not only does it waste disk space, but it definitely exceeds the limit.

9
| username: 胡杨树旁 | Original post link

Yes, I have never paid attention to it.

10
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.