Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-41

connection.getMetaData().getPrimaryKeys() returns wrong ordinal_colum when primary key is not in the first colum.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.1.1
    • 1.1.3
    • None
    • None

    Description

      Try the following

      create database SomeTest;
       
      use SomeTest;
       
      CREATE TABLE `ExampleTable` (
        `someId` smallint(6) NOT NULL DEFAULT '0',
        `somePK` decimal(19,0) NOT NULL DEFAULT '0',
        `someValue1` decimal(19,0) DEFAULT NULL,
        `someValue2` smallint(6) DEFAULT NULL,
        `someValue3` datetime DEFAULT NULL,
        `someValue4` datetime DEFAULT NULL,
        PRIMARY KEY (`somePK`),
        KEY `IN_SOME_ID` (`someId`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

      When you use the query like it is coded in MySQLDatabaseMetaData to query the primary keys with getPrimaryKeys()
      the wrong information_schema table is queried.

      Consider the follwing testcase:

      SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, NULL PK_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY='pri' AND (ISNULL(database()) OR (TABLE_SCHEMA = database())) AND (TABLE_NAME LIKE 'ExampleTable') ORDER BY column_name;
      +-----------+-------------+--------------+-------------+---------+---------+
      | TABLE_CAT | TABLE_SCHEM | TABLE_NAME   | COLUMN_NAME | KEY_SEQ | PK_NAME |
      +-----------+-------------+--------------+-------------+---------+---------+
      | SomeTest  |        NULL | ExampleTable | somePK      |       2 |    NULL |
      +-----------+-------------+--------------+-------------+---------+---------+

      As KEY_SEQ which is the ordinal position of the primary key should be returned 1 and not the column of the primary key. The Sun jdbc implementation uses a own resultset which is build against "show keys from table"

      show keys from ExampleTable;
      +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table        | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | ExampleTable |          0 | PRIMARY    |            1 | somePK      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
      | ExampleTable |          1 | IN_SOME_ID |            1 | someId      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
      +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      As Seq_in_index which is mapped as KEY_SEQ is returned 1, the right value.
      The issue can be solved by using a query against the table KEY_COLUMN_USAGE here as example:

      SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, NULL PK_NAME  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE (ISNULL(database()) OR (TABLE_SCHEMA = database())) AND (TABLE_NAME LIKE 'ExampleTable') ORDER BY column_name;

      Attachments

        Issue Links

          Activity

            People

              wlad Vladislav Vaintroub
              framas Frank S
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.