SQL issue-[Script issue]: Specified key was too long; max key length is 3072 bytes

[huan11]

Describe the bug

Env: MySQL version: Server version: 8.0.37 MySQL Community Server - GPL

Scripts: https://github.com/sofastack/sofa-registry/blob/master/create_table.sql

CREATE TABLE IF NOT EXISTS multi_cluster_sync_info
(
    id                   bigint(20)    NOT NULL AUTO_INCREMENT COMMENT '主键',
    data_center          varchar(512)  NOT NULL COMMENT '集群名称',
    remote_data_center   varchar(512)  NOT NULL COMMENT '同步的集群名称',
    remote_meta_address  varchar(1024) NOT NULL COMMENT '同步的集群地址',
    enable_sync_datum    varchar(16)   NOT NULL COMMENT 'datum同步的开关是否开启',
    enable_push          varchar(16)   NOT NULL COMMENT '同步的数据是否允许推送',
    sync_datainfoids     MEDIUMTEXT    NOT NULL COMMENT '同步的dataInfoId名单',
    syn_publisher_groups varchar(4096) NOT NULL COMMENT '同步的group名单',
    ignore_datainfoids   MEDIUMTEXT    NOT NULL COMMENT '忽略同步的dataInfoId',
    data_version         bigint(20)    NOT NULL DEFAULT '0' COMMENT '版本号',
    gmt_create           timestamp     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    gmt_modified         timestamp     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_table_key (data_center, remote_data_center),
    KEY idx_data_center (data_center)
);

[2024-07-09 13:07:10] [42000][1071] Specified key was too long; max key length is 3072 bytes

Expected behavior

Script shld be work well.

Actual behavior

When follow the guide (https://www.sofastack.tech/projects/sofa-registry/server-quick-start/ ), it failed at '2.启动 registry-integration' Step.

how to fix

reduce the width of column data_center and column remote_data_center. (change 512 to 255)

CREATE TABLE IF NOT EXISTS multi_cluster_sync_info
(
    id                   bigint(20)    NOT NULL AUTO_INCREMENT COMMENT '主键',
    data_center          varchar(255)  NOT NULL COMMENT '集群名称',
    remote_data_center   varchar(255)  NOT NULL COMMENT '同步的集群名称',
    remote_meta_address  varchar(1024) NOT NULL COMMENT '同步的集群地址',
    enable_sync_datum    varchar(16)   NOT NULL COMMENT 'datum同步的开关是否开启',
    enable_push          varchar(16)   NOT NULL COMMENT '同步的数据是否允许推送',
    sync_datainfoids     MEDIUMTEXT    NOT NULL COMMENT '同步的dataInfoId名单',
    syn_publisher_groups varchar(4096) NOT NULL COMMENT '同步的group名单',
    ignore_datainfoids   MEDIUMTEXT    NOT NULL COMMENT '忽略同步的dataInfoId',
    data_version         bigint(20)    NOT NULL DEFAULT '0' COMMENT '版本号',
    gmt_create           timestamp     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    gmt_modified         timestamp     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (id),
    UNIQUE KEY uk_table_key (data_center, remote_data_center),
    KEY idx_data_center (data_center)
);

Environment

Env: MySQL version: Server version: 8.0.37 MySQL Community Server - GPL

[NickNYU]

Thanks for contribute @huan11 . Just to confirm that why we prefer to change remote_data_center, data_center, while keeping syn_publisher_groups as the same, if the issue is varchar length related?

[huan11]

@NickNYU yes, UNIQUE KEY is too long which caused the issue.

UNIQUE KEY uk_table_key (data_center, remote_data_center),

[NickNYU]

@NickNYU yes, UNIQUE KEY is too long which caused the issue.

UNIQUE KEY uk_table_key (data_center, remote_data_center),

Awesome!! that's reasonable

Please send a Pull Request according to this question, thanks

The basic idea of the varchar length is a draft thought when I was defining the table, not a big deal. It is just in Ant Group we leverage OceanBase as default DB, and seems like OB has a high toleration than traditional DBs.

[huan11]

@NickNYU PR have been raised : )

[NickNYU]

PR has been merged, thx for contribution @huan11 I'll close this issue