[TAJO-1305] With metadata storage of MySQL, columns with the same character but difference case are not allowed

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 0.10.0
• Assignee: Jihun Kang
• Reporter: Keuntae Park
• Created: 14/Jan/15 07:15
• Updated: 23/Jan/15 00:24
• Resolved: 20/Jan/15 14:04

Description

With MySQL as catalog storage, I experienced following create table statement did not work.

create table test ("name" text, "Name" text);

Error log is as followings:

2015-01-14 15:25:27,386 ERROR org.apache.tajo.catalog.CatalogServer: java.sql.BatchUpdateException: Duplicate entry '1221-Name' for key 'PRIMARY'
org.apache.tajo.catalog.exception.CatalogException: java.sql.BatchUpdateException: Duplicate entry '1221-Name' for key 'PRIMARY'
        at org.apache.tajo.catalog.store.AbstractDBStore.createTable(AbstractDBStore.java:819)
        at org.apache.tajo.catalog.CatalogServer$CatalogProtocolHandler.createTable(CatalogServer.java:523)
        at org.apache.tajo.catalog.AbstractCatalogClient$16.call(AbstractCatalogClient.java:354)
        at org.apache.tajo.catalog.AbstractCatalogClient$16.call(AbstractCatalogClient.java:351)

With Google's assistance, I found that
MySQL primary key column does not distinguish the case of characters.
I made above statement work with following COLUMS schema (according to Google's advice):

CREATE TABLE COLUMNS (
  TID INT NOT NULL,
  COLUMN_NAME VARCHAR(255) BINARY NOT NULL,
  ORDINAL_POSITION INT NOT NULL,
  DATA_TYPE CHAR(16),
  TYPE_LENGTH INTEGER,
  PRIMARY KEY (TID, COLUMN_NAME),
  FOREIGN KEY (TID) REFERENCES TABLES (TID) ON DELETE CASCADE
)

I'm not sure this is proper approach to solve the problem
because I'm not familiar with Catalog
Please, check the situation.