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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Not a Bug
    • 10.0.10
    • 10.0.11
    • None

    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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5341 ConnectSE: discovery for ODBC tables does not work if tables with the same names present in multiple schemas

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bertrandop Olivier Bertrand added a comment -

            I don't know whether you have access to the proper CONNECT documentation I update regularly in DOC, PDF, and HTML format or just use the on-line version of the MariaDB site. The problem is that this version is often outdated or incomplete.

            In the file version, this issue is addressed page 49 in the section called "ODBC Catalog Information" and says in particular:

            <<<<<<<<<<<<<
            Depending on the version of the used ODBC driver, some additional information on the tables are existing, such as table QUALIFIER or OWNER for old versions, now named CATALOG or SCHEMA since version 3.

            CATALOG is apparently rarely used by most data sources, but SCHEMA (formerly OWNER) is and corresponds to the DATABASE information of MySQL.

            The issue is that if no schema name is specified, some data sources return information for all schemas while some others only return the information of the “default” schema. In addition, the used “schema” or “database” is sometimes implied by the connection string and sometimes is not. Sometimes, it also can be included in a data source definition.

            CONNECT offers two ways to specify this information:

            1. When specified, the DBNAME create table option is regarded by ODBC tables as the SCHEMA name.
            2. Table names can be specified as “cat.sch.tab” allowing to set the catalog and schema info.

            When both are used, the qualified table name has precedence over DBNAME.

            When creating a standard ODBC table, you should make sure only one source table is specified. Specifying more than one source table must be done only for CONNECT catalog tables (with CATFUNC=tables or columns)
            >>>>>>>>>>>>>

            If this does not seem explicit enough for you, I am open to any suggestion concerning what should be said.

            About the inappropriate error message, it is difficult to address this for CONNECT because it is made by MariaDB when the constructed create statement is sent to the MariaDB "init_from_sql_statement_string" function. What can be done is simply to test for duplicate column names before sending it but there will be no mean to discover the cause of it.

            bertrandop Olivier Bertrand added a comment - I don't know whether you have access to the proper CONNECT documentation I update regularly in DOC, PDF, and HTML format or just use the on-line version of the MariaDB site. The problem is that this version is often outdated or incomplete. In the file version, this issue is addressed page 49 in the section called "ODBC Catalog Information" and says in particular: <<<<<<<<<<<<< Depending on the version of the used ODBC driver, some additional information on the tables are existing, such as table QUALIFIER or OWNER for old versions, now named CATALOG or SCHEMA since version 3. CATALOG is apparently rarely used by most data sources, but SCHEMA (formerly OWNER) is and corresponds to the DATABASE information of MySQL. The issue is that if no schema name is specified, some data sources return information for all schemas while some others only return the information of the “default” schema. In addition, the used “schema” or “database” is sometimes implied by the connection string and sometimes is not. Sometimes, it also can be included in a data source definition. CONNECT offers two ways to specify this information: 1. When specified, the DBNAME create table option is regarded by ODBC tables as the SCHEMA name. 2. Table names can be specified as “cat.sch.tab” allowing to set the catalog and schema info. When both are used, the qualified table name has precedence over DBNAME. When creating a standard ODBC table, you should make sure only one source table is specified. Specifying more than one source table must be done only for CONNECT catalog tables (with CATFUNC=tables or columns) >>>>>>>>>>>>> If this does not seem explicit enough for you, I am open to any suggestion concerning what should be said. About the inappropriate error message, it is difficult to address this for CONNECT because it is made by MariaDB when the constructed create statement is sent to the MariaDB "init_from_sql_statement_string" function. What can be done is simply to test for duplicate column names before sending it but there will be no mean to discover the cause of it.
            bar Alexander Barkov added a comment -

            Olivier, the bug was created in November 2013. We need to do something.
            If you think we should close it, please do so.

            bar Alexander Barkov added a comment - Olivier, the bug was created in November 2013. We need to do something. If you think we should close it, please do so.
            bertrandop Olivier Bertrand added a comment -

            I shall make the documentation more explicit about this issue.

            bertrandop Olivier Bertrand added a comment - I shall make the documentation more explicit about this issue.

            People

              bertrandop Olivier Bertrand
              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.