[AIRAVATA-2309] SHARING table: MySQL complains "Specified key was too long; max key length is 3072 bytes"

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Assignee: Supun Nakandala
• Reporter: Marcus Christie
• Created: 27/Jan/17 16:07
• Updated: 30/Jan/17 19:40
• Resolved: 30/Jan/17 18:28

Description

Error occurs while trying to create the SHARING table:

CREATE TABLE SHARING (
  PERMISSION_TYPE_ID VARCHAR(255) NOT NULL,
  ENTITY_ID VARCHAR(255) NOT NULL,
  GROUP_ID VARCHAR(255) NOT NULL,
  SHARING_TYPE VARCHAR(255) NOT NULL,
  INHERITED_PARENT_ID VARCHAR(255),
  CREATED_TIME BIGINT NOT NULL,
  UPDATED_TIME BIGINT NOT NULL,
  PRIMARY KEY (PERMISSION_TYPE_ID, ENTITY_ID, GROUP_ID, INHERITED_PARENT_ID),
  FOREIGN KEY (PERMISSION_TYPE_ID) REFERENCES PERMISSION_TYPE(PERMISSION_TYPE_ID) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (INHERITED_PARENT_ID) REFERENCES ENTITY(ENTITY_ID) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (GROUP_ID) REFERENCES USER_GROUP(GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE
);

This key is fine in the dev database on gw62. I think the reason it fails for me is that my databases have a default encoding of UTF-8. On gw62 the encoding is latin1_swedish_ci.