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

CONNECT sometimes returns wrong column's data for the ODBC table created based on Oracle view

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration