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

ConnectSE: discovery for ODBC tables does not work if tables with the same names present in multiple schemas

Details

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

    Description

      Create tables with the same names in two different schemas in Oracle XE 11:

      [bar@home ~]$ sqlplus
      SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 12:37:51 2013
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      Enter user-name: system
      Enter password: 
      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
      SQL> create table hr.t1 (a char(10));
      Table created.
      SQL> insert into hr.t1 values ('test1');
      1 row created.
      SQL> create table system.t1 (a char(10));
      Table created.
      SQL> insert into system.t1 values ('test2');
      1 row created.
      SQL> commit;
      Commit complete.

      Now try to create a ConnectSE ODBC table in MariaDB.
      It fails:

      mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
      Query OK, 0 rows affected (0.01 sec)
       
      ERROR 1939 (HY000): Engine CONNECT failed to discover table `test`.`t1` with 'CREATE TABLE whatever (`A` CHAR(10),`A` CHAR(10)) TABLE_TYPE='odbc' TABNAME='T1' CONNECTION='DSN=oraodbc;UID=system;PWD=manager''
      ERROR 1146 (42S02): Table 'test.t1' doesn't exist

      Notice, the column "A" presents two times.
      The first "A" comes from the table "hr.t1".
      The second "A" comes from the table "system.t1".

      Now drop the table hr.t1 in Oracle:

      SQL> drop table hr.t1;
      Table dropped.

      and try to create the MariaDB table again:

      mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='T1'; select * from t1;
      Query OK, 0 rows affected, 1 warning (0.00 sec)
       
      Query OK, 0 rows affected (2.61 sec)
       
      +-------+
      | A     |
      +-------+
      | test2 |
      +-------+
      1 row in set (0.06 sec)

      It works as expected and returns data from the Oracle's table system.t1
      (Note, "system" is the default schema for the user "system" in Oracle).

      The discovery code should be fixed to collect column data only from a
      single remote table "t1".

      Also, discovery does not work for a table in a non-default schema.
      Create a table in a single non-default Oracle schema:

      [bar@home ~]$ sqlplus
      SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 26 13:11:27 2013
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      Enter user-name: system
      Enter password: 
      Connected to:
      Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
      SQL> drop table hr.t1;
      Table dropped.
      SQL> create table hr.t1 (a char(10));
      Table created.
      SQL> insert into hr.t1 values ('test3');
      1 row created.
      SQL> commit;
      Commit complete.

      Now try to create a ConnectSE table using discovery. It fails:

      mysql> drop table if exists t1; create table t1 engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='HR.T1'; select * from t1;
      Query OK, 0 rows affected (0.00 sec)
      ERROR 1105 (HY000): No data found for table HR.T1
      ERROR 1146 (42S02): Table 'test.t1' doesn't exist

      However, It works fine when the table structure is specified explicitly:

      mysql> drop table if exists t1; create table t1 (a char(10)) engine=connect table_type=odbc connection='DSN=oraodbc;UID=system;PWD=manager' tabname='HR.T1'; select * from t1;
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      Query OK, 0 rows affected (0.04 sec)
      +-------+
      | a     |
      +-------+
      | test3 |
      +-------+
      1 row in set (0.07 sec)

      The same problems are reproducible when connecting to Interystems Cache.

      The problem with multiple schemas is not repeatable with PostgreSQL.
      The problem with a non-default schema IS repeatable with PostgreSQL.

      The problem with multiple schemas is not repeatable with MS SQL Server.
      The problem with a non-default schema IS repeatable with MS SQL Server.

      Attachments

        Issue Links

          Activity

            It seems to be a duplicate of MDEV-5318, but since it has more elaborate test cases, I'll keep it open for now. Please feel free to close if you don't need it.

            elenst Elena Stepanova added a comment - It seems to be a duplicate of MDEV-5318 , but since it has more elaborate test cases, I'll keep it open for now. Please feel free to close if you don't need it.

            These issues are really difficult to address since data sources behave differently (not counting those not tested yet or to come)

            The main advice to users is "do specify the schema when creating ODBC tables via Discovery".

            This is already documented (see my comment in MDEV-5318) but probably not insisting enough on that point. I shall update the documentation to make it more visible.

            By the way, the same problems probably occur when the table is created not using Discovery (by specifying the columns) and when the table is used in a query.

            bertrandop Olivier Bertrand added a comment - These issues are really difficult to address since data sources behave differently (not counting those not tested yet or to come) The main advice to users is "do specify the schema when creating ODBC tables via Discovery". This is already documented (see my comment in MDEV-5318 ) but probably not insisting enough on that point. I shall update the documentation to make it more visible. By the way, the same problems probably occur when the table is created not using Discovery (by specifying the columns) and when the table is used in a query.

            Refer to MDEV-5318 for more information.

            bertrandop Olivier Bertrand added a comment - Refer to MDEV-5318 for more information.

            People

              bertrandop Olivier Bertrand
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.