Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004
    Posts
    35

    Unanswered: help - error creating index

    hi,
    Im migratin an oracle db to mysql and when i try to create index i get an error , please if some one know a way to increase the size of the index size??
    10x
    Ronen

    My script is
    CREATE TABLE MSM_ALL_EVENTS
    (
    EVENTID VARCHAR(255) NOT NULL,
    SRC_OID VARCHAR(255),
    DEST_OID VARCHAR(255),
    GENERATION_DATE DATE,
    TYPE VARCHAR(50),
    DESCR TEXT,
    PARENT_EVENTID VARCHAR(255),
    GENERATION_NUM DECIMAL(15,5),
    ALARM_TYPE VARCHAR(255),
    ALARM_DESCR VARCHAR(255),
    SEVERITY VARCHAR(50),
    IS_RAISED TINYINT,
    IS_CONFIRMED TINYINT,
    SRC_INTERNAL_OID VARCHAR(255),
    CONFIRMATION_NOTE TEXT
    ) ENGINE = INNODB;

    CREATE INDEX IDX_EVENT_CLEAR ON MSM_ALL_EVENTS
    (SRC_OID ASC,
    TYPE ASC,
    ALARM_TYPE ASC,
    IS_RAISED ASC,
    SRC_INTERNAL_OID ASC);

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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I read the error message saying the combined length of the columns comprising the index is to big ie the sum of the columns size exceeds 1024 characters which MySQL / INNODb can't handle
    The resolution is to look at the table defintion and work out if you really need to have columns that size

    Firt off I'd check the size and types of the columns in the Oracle and ensure you have got the closest match in MySQL, where there are discrepancies size up.

    I'm surprised you need so many varchar columns, especially those defined as ID or OID - suspect these should in reality be type Integer
    The other odd thing is there is no primary key defined - unusual in an SQL table definition, but not unkown

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •