Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5318

ConnectSE: assisted discovery fails if the same table name presents in more than one schemas

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Not a Bug
    • Affects Version/s: 10.0.10
    • Fix Version/s: 10.0.11
    • Component/s: None
    • Labels:

      Description

      If two tables having the same name present in more than one
      different schemas, then discovery does not work.

      For example, Cache in its default installation has the following
      tables with the name "Person" in multiple demo schemas:

      +-----------------+-------------+------------+------------+-----------+
      | Table_Qualifier | Table_Owner | Table_Name | Table_Type | Remark    |
      +-----------------+-------------+------------+------------+-----------+
      |                 | BasTutorial | Person     | TABLE      |           |
      |                 | CosTutorial | Person     | TABLE      |           |
      |                 | SQLUser     | Person     | TABLE      |           |
      |                 | Sample      | Person     | TABLE      | This s... |
      |                 | Wasabi_Data | Person     | TABLE      |           |
      |                 | ZENMVC      | Person     | TABLE      | Simple .. |
      +-----------------+-------------+------------+------------+-----------+

      So this query:

      create table t1 tabname='person' engine=CONNECT table_type=ODBC connection='DSN=Samplesunixodbc';

      returns an error:

      ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`t1` with 'CREATE TABLE whatever (`ID` INT(10) NOT NULL COMMENT 'ID',`DOB` DATE COMMENT 'DOB',`FirstName` VARCHAR(50) COMMENT 'FirstName',`LastName` VARCHAR(50) COMMENT 'LastName',`Name` VARCHAR(50) COMMENT 'Name',`Phone` VARCHAR(12) COMMENT 'Phone',`ID` INT(10) NOT NULL COMMENT 'ID',`DOB` DATE COMMENT 'DOB',`FirstName` VARCHAR(50) COMMENT 'FirstName',`LastName` VARCHAR(50) COMMENT 'LastName',`Name` VARCHAR(50) COMMENT 'Name',`Phone` VARCHAR(12) COMMENT 'Phone

      because the ODBC call for SQLColumns() returns columns for all tables
      "Person" from all these six schemas, and some of them have duplicate names.

      The same problem happens with Oracle DSN:

      create table t1 tabname='COUNTRIES' engine=CONNECT table_type=ODBC connection='DSN=oraodbc;UID=system;PWD=manager';

      ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`t1` with 'CREATE TABLE whatever (`COUNTRY_ID` CHAR(2) NOT NULL,`COUNTRY_NAME` VARCHAR(40),`REGION_ID` DOUBLE(40,0),`COUNTRY_ID` VARCHAR(10)) TABLE_TYPE='ODBC' TABNAME='COUNTRIES' CONNECTION='DSN=oraodbc;UID=system;PWD=manager''

      if table "COUNTRIES" present in two schemas. Notice REGION_ID specified twice in the above error message.

      The above error message is confusing. We need to solve this somehow.

      Possible ways:

      1. Detect the default schema name.

      If schema name is not specified by the user,
      then we can try to get to know the default schema
      name before doing SQLColumn().

      Unfortunately, it seems there are no ways to fetch current schema in the ODBC API.

      Neither are portable SQL ways to get current schema.
      It's very different between databases:

      • MySQL:

        SELECT DATABASE();

      • PostgreSQL supports the SQL standard way:

        SELECT CURRENT_SCHEMA;

      • Oracle:

          SELECT SYS_CONTEXT('userenv','current_schema') x FROM dual;

      • Cache assumes "SQLUser" schema by default when schema is not
        specified explicitly.

      We can try to check the database type and run one
      of the above SQL queries to know the default schema name.
      But this, probably, sounds too complex.

      2. Or just print a better error message.

      The discovery code could make sure that schema
      name in the result of SQLColumns() is the same for all columns.
      In case if schema name is not the same, then print an error like this:

      ERROR 1939 (HY0000): Table name "Person" presents in more than one schemas ('SAMPLE' and 'SQLUSer'). Use a qualified table name (e.g. 'Sample.Person' to disambiguate.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bertrandop Olivier Bertrand
              Reporter:
              bar Alexander Barkov
              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.