[MDEV-5318] ConnectSE: assisted discovery fails if the same table name presents in more than one schemas Created: 2013-11-20  Updated: 2014-04-09  Resolved: 2014-04-09

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.10
Fix Version/s: 10.0.11

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Olivier Bertrand
Resolution: Not a Bug Votes: 0
Labels: connect-engine

Issue Links:
Duplicate
is duplicated by MDEV-5341 ConnectSE: discovery for ODBC tables ... Closed

 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.



 Comments   
Comment by Olivier Bertrand [ 2014-04-08 ]

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.

Comment by Alexander Barkov [ 2014-04-09 ]

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

Comment by Olivier Bertrand [ 2014-04-09 ]

I shall make the documentation more explicit about this issue.

Generated at Thu Feb 08 07:03:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.