MySQL Linux ERROR 1071 (42000): Specified key was too long XCOM 11.6

book

Article ID: 195083

calendar_today

Updated On:

Products

CA XCOM Data Transport CA XCOM Data Transport - Windows CA 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

 

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.

Environment

Release : 11.6

Component : CA XCOM Data Transport for Linux PC

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';