Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3.23, 10.3.27
-
None
-
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.