Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Not a Bug
-
10.0.10
-
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
- duplicates
-
-
- Closed
-