[MDEV-24493] CONNECT sometimes returns wrong column's data for the ODBC table created based on Oracle view Created: 2020-12-27  Updated: 2022-01-25  Resolved: 2021-10-15

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.3.23, 10.3.27
Fix Version/s: 10.2.41, 10.3.32, 10.4.22, 10.5.13, 10.6.5, 10.7.1

Type: Bug Priority: Critical
Reporter: Valerii Kravchuk Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7


 Description   

This is a bit tricky. We need Oracle server, let's say 18 XE, and MariaDB 10.3.x with CONNECT engine. It is easy to get these in Docker:

openxs@ao756:~$ sudo docker ps
 
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                PORTS                                              NAMES
 
809121087b77        centos:centos7              "bash"                   4 hours ago         Up 4 hours                                                               stupefied_pike
 
0da3342f829b        oracle/database:18.4.0-xe   "/bin/sh -c 'exec $OБ─і"   3 days ago          Up 3 days (healthy)   0.0.0.0:51521->1521/tcp, 0.0.0.0:55500->5500/tcp   oracle18xe

I've used official Docker image for centos7 and image created based on this Oracle's GitHub project:

1994     cd git
 
1995     git clone https://github.com/oracle/docker-images.git
 
1996     cd docker-images/OracleDatabase/SingleInstance/dockerfiles
 
1997     ./buildDockerImage.sh -v 18.4.0 -x
 
1998     sudo ./buildDockerImage.sh -v 18.4.0 -x

After that create database as follows:

sudo docker run -p 51521:1521 -p 55500:5500 -e ORACLE_PWD=oracle -e ORACLE_CHARCTERSET=WE8ISO8859P9 oracle/database:18.4.0-xe

Now install these packages (I skip the details of setting up MariaDB repo etc) in clean centos7 image and update it:

[root@809121087b77 ~]# rpm -q -a | grep -e ODBC -e MariaDB -e oracle
 
MariaDB-server-10.3.27-1.el7.centos.x86_64
 
MariaDB-backup-10.3.27-1.el7.centos.x86_64
 
oracle-instantclient19.9-basic-19.9.0.0.0-1.x86_64
 
oracle-instantclient19.9-sqlplus-19.9.0.0.0-1.x86_64
 
MariaDB-compat-10.3.27-1.el7.centos.x86_64
 
MariaDB-connect-engine-10.3.27-1.el7.centos.x86_64
 
MariaDB-shared-10.3.27-1.el7.centos.x86_64
 
oracle-instantclient19.9-odbc-19.9.0.0.0-1.x86_64
 
unixODBC-2.3.1-14.el7.x86_64
 
MariaDB-common-10.3.27-1.el7.centos.x86_64
 
MariaDB-client-10.3.27-1.el7.centos.x86_64
 
[root@809121087b77 ~]#

The files to add/configure are these:

[root@809121087b77 ~]# cat /etc/oracle/tnsnames.ora
 
XE =
 
 ( DESCRIPTION =
 
  (ADDRESS_LIST =
 
  (ADDRESS =
 
  (PROTOCOL = TCP)
 
  (Host = 172.17.0.2)
 
  (Port = 1521)
 
  )
 
 )
 
 (CONNECT_DATA = (SID = XE)
 
 )
 
)
 
[root@809121087b77 ~]# cat /etc/odbc.ini
 
[oracle]
 
Driver = OracleODBC
 
DSN = OracleODBC
 
ServerName = XE
 
UserID = system
 
Password = oracle
 
[root@809121087b77 ~]# cat /etc/odbcinst.ini
 
# Example driver definitions
 
 
 
[OracleODBC]
 
Description = Oracle ODBC driver for Oracle 11g
 
Driver64 = /usr/lib/oracle/19.9/client64/lib/libsqora.so.19.1
 
FileUsage = 1
 
Driver Logging = 7
 
...

assuming that 172.17.0.2 is your Oracle 18 XE container IP. Now from centos7 we can connect with SQL*Plus and create these:

[root@809121087b77 ~]# sqlplus system/oracle@XE
 
 
 
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 27 15:22:08 2020
 
Version 19.9.0.0.0
 
 
 
Copyright (c) 1982, 2020, Oracle. All rights reserved.
 
 
 
Last Successful login time: Sun Dec 27 2020 15:20:42 +00:00
 
 
 
Connected to:
 
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
 
Version 18.4.0.0.0
 
 
 
SQL> drop view tv;
 
 
 
View dropped.
 
 
 
SQL> select * from t;
 
 
 
ID C1 C2
 
---------- ---------- ---------
 
1 1 24-DEC-20
 
2 3 20-DEC-20
 
 
 
SQL> create view tv as select id, c1, TO_CHAR(c2, 'YYYY-MM_DD HH24:MI:SS') c2 from t;
 
 
 
View created.
 
 
 
SQL> select * from tv;
 
 
 
ID C1 C2
 
---------- ---------- -------------------
 
1 1 2020-12_24 14:52:18
 
2 3 2020-12_20 00:00:00
 
 
 
SQL> desc t;
 
Name Null? Type
 
----------------------------------------- -------- ----------------------------
 
ID NUMBER
 
C1 NUMBER
 
C2 DATE
 
 
 
SQL> desc tv;
 
Name Null? Type
 
----------------------------------------- -------- ----------------------------
 
ID NUMBER
 
C1 NUMBER
 
C2 VARCHAR2(19)
 
 
 
SQL>

Column types and data in the t table are clear, and the view created as presented above, it converts date column into a string in some custom format.

Now at MariaDB side:

[root@809121087b77 ~]# mysql -uroot test
 
Welcome to the MariaDB monitor. Commands end with ; or \g.
 
Your MariaDB connection id is 9
 
Server version: 10.3.27-MariaDB MariaDB Server
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> create table connect_tv engine=connect table_type=ODBC tabname='connect_tv' Connection='DSN=oracle' SRCDEF='select ID, C1, C2 from TV';
 
Query OK, 0 rows affected (0.362 sec)
 
 
 
MariaDB [test]> select * from connect_tv;
 
+------+------+---------------------+
 
| ID | C1 | C2 |
 
+------+------+---------------------+
 
| 1 | 1 | 2020-12_24 14:52:18 |
 
| 2 | 3 | 2020-12_20 00:00:00 |
 
+------+------+---------------------+
 
2 rows in set (0.107 sec)
 
 
 
MariaDB [test]> select id, c2 from connect_tv;
 
+------+------+
 
| id | c2 |
 
+------+------+
 
| 1 | 1 |
 
| 2 | 3 |
 
+------+------+
 
2 rows in set (0.107 sec)
 
 
 
MariaDB [test]> select id, c1, c2 from connect_tv;
 
+------+------+---------------------+
 
| id | c1 | c2 |
 
+------+------+---------------------+
 
| 1 | 1 | 2020-12_24 14:52:18 |
 
| 2 | 3 | 2020-12_20 00:00:00 |
 
+------+------+---------------------+
 
2 rows in set (0.104 sec)
 
 
 
MariaDB [test]> select c2 from connect_tv;
 
+------+
 
| c2 |
 
+------+
 
| 1 |
 
| 2 |
 
+------+
 
2 rows in set (0.098 sec)
 
 
 
MariaDB [test]> select c2, id, c2 from connect_tv;
 
+------+------+------+
 
| c2 | id | c2 |
 
+------+------+------+
 
| 1 | 1 | 1 |
 
| 3 | 2 | 3 |
 
+------+------+------+
 
2 rows in set (0.139 sec)
 
 
 
MariaDB [test]> select c2, id, c1 from connect_tv;
 
+---------------------+------+------+
 
| c2 | id | c1 |
 
+---------------------+------+------+
 
| 2020-12_24 14:52:18 | 1 | 1 |
 
| 2020-12_20 00:00:00 | 2 | 3 |
 
+---------------------+------+------+
 
2 rows in set (0.096 sec)

So, while SELECT * works as expected, depending on the explicit list of columns in SELECT clause we see c2 with data either from correct or from some other column. The data are not random, nor corrupted, they just come from some other column it seems.

This is a bug.

 Comments   
Comment by Olivier Bertrand [ 2021-10-13 ]

A bug indeed not fixed yet.
But note that using SRCDEF in this case is unnatural and a simple turnaround is to create the table as:

 
create table connect_tv
 
engine=connect table_type=ODBC tabname='TV'
 
Connection='DSN=oracle';

No bug with this one.

Comment by Olivier Bertrand [ 2021-10-15 ]

Fixed. However, when the columns are manually defined, for instance to change the data type or column name, they must be defined in the same order they appear in the SRCDEF option.

Comment by Olivier Bertrand [ 2021-10-15 ]

BTW this was not related to Oracle an views. It was a CONNECT ODBC bug happening with SRCDEF option that would happen with any such queries whatever the server or the remote table is.

Generated at Thu Feb 08 09:30:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.