Uploaded image for project: 'Tajo'
  1. Tajo
  2. TAJO-1305

With metadata storage of MySQL, columns with the same character but difference case are not allowed

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.10.0
    • Component/s: None
    • Labels:
      None

      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.

        Activity

        Hide
        ykrips Jihun Kang added a comment -

        Yes, by default MySQL uses a case-insensitive collation in general. In this situation, we can choose two options. One is to use a binary string as a column data type, otherwise we can specify a case-sensitive collation in a column definition.

        Show
        ykrips Jihun Kang added a comment - Yes, by default MySQL uses a case-insensitive collation in general. In this situation, we can choose two options. One is to use a binary string as a column data type, otherwise we can specify a case-sensitive collation in a column definition.
        Hide
        hudson Hudson added a comment -

        FAILURE: Integrated in Tajo-master-CODEGEN-build #199 (See https://builds.apache.org/job/Tajo-master-CODEGEN-build/199/)
        TAJO-1305: With metadata storage of MySQL, columns with the same character but difference case are not allowed. (jihun: rev e22c1c57bc81e65af6a041e9f5afc9973ca70dae)

        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tables.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tablespaces.sql
        • tajo-catalog/tajo-catalog-server/src/test/java/org/apache/tajo/catalog/TestCatalog.java
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tables.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/table_properties.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/partitions.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/indexes.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/databases.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/partitions.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/indexes.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/databases.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/columns.sql
        • CHANGES
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tablespaces.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/columns.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/table_properties.sql
        Show
        hudson Hudson added a comment - FAILURE: Integrated in Tajo-master-CODEGEN-build #199 (See https://builds.apache.org/job/Tajo-master-CODEGEN-build/199/ ) TAJO-1305 : With metadata storage of MySQL, columns with the same character but difference case are not allowed. (jihun: rev e22c1c57bc81e65af6a041e9f5afc9973ca70dae) tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tables.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tablespaces.sql tajo-catalog/tajo-catalog-server/src/test/java/org/apache/tajo/catalog/TestCatalog.java tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tables.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/table_properties.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/partitions.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/indexes.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/databases.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/partitions.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/indexes.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/databases.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/columns.sql CHANGES tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tablespaces.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/columns.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/table_properties.sql
        Hide
        hudson Hudson added a comment -

        SUCCESS: Integrated in Tajo-master-build #560 (See https://builds.apache.org/job/Tajo-master-build/560/)
        TAJO-1305: With metadata storage of MySQL, columns with the same character but difference case are not allowed. (jihun: rev e22c1c57bc81e65af6a041e9f5afc9973ca70dae)

        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/partitions.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/columns.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tables.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tablespaces.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/partitions.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tablespaces.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/indexes.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/indexes.sql
        • tajo-catalog/tajo-catalog-server/src/test/java/org/apache/tajo/catalog/TestCatalog.java
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/table_properties.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/databases.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/table_properties.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/columns.sql
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tables.sql
        • CHANGES
        • tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/databases.sql
        Show
        hudson Hudson added a comment - SUCCESS: Integrated in Tajo-master-build #560 (See https://builds.apache.org/job/Tajo-master-build/560/ ) TAJO-1305 : With metadata storage of MySQL, columns with the same character but difference case are not allowed. (jihun: rev e22c1c57bc81e65af6a041e9f5afc9973ca70dae) tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/partitions.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/columns.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tables.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tablespaces.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/partitions.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/tablespaces.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/indexes.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/indexes.sql tajo-catalog/tajo-catalog-server/src/test/java/org/apache/tajo/catalog/TestCatalog.java tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/table_properties.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/databases.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/table_properties.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/columns.sql tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mariadb/tables.sql CHANGES tajo-catalog/tajo-catalog-server/src/main/resources/schemas/mysql/databases.sql

          People

          • Assignee:
            ykrips Jihun Kang
            Reporter:
            sirpkt Keuntae Park
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development