ERROR: "ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes" XCOM Linux

Products

• XCOM Data Transport - Windows
• XCOM Data Transport - Linux PC
• XCOM - SUPPORT

Issue/Introduction

The following error occurred when running the MySQL database resource creation script to create the XCOMTRST database

mysql> create table XCOM_TRUSTED_SYS (

    ->         SYSTEM_NAME             VARCHAR(255)    NOT NULL,

    ->         IS_SYSNAME_ENTERED      CHAR(1),

    ->         NOTES                   VARCHAR(255),

    ->         SYSNAME                 CHAR(8)         NOT NULL,

    ->         SYSID                   CHAR(4)         NOT NULL,

    ->         GROUP_NAME              VARCHAR(255)    NOT NULL,

    ->         USER_NAME               VARCHAR(255)    NOT NULL,

    ->         USER_PASSWORD           VARCHAR(126),

    ->         USER_DOMAIN             VARCHAR(15),

    ->         primary key (SYSNAME,SYSID,SYSTEM_NAME,GROUP_NAME,USER_NAME));

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

Environment

XCOM™ Data Transport® for Linux PC

Cause

The problem is caused by the default character set on the MySQL server. It is possible that the MySQL server is using some Unicode character set, where each character is considered 4 bytes causing the length to exceed the 3072 bytes supported by the database.

Resolution

In order to resolve the problem, modify the following statement in the xcomtrstDB.sql file:

primary key (SYSNAME,SYSID,SYSTEM_NAME,GROUP_NAME,USER_NAME))

to

primary key (SYSNAME,SYSID,SYSTEM_NAME,GROUP_NAME,USER_NAME)) DEFAULT CHARSET=latin1 DATA DIRECTORY='path';