「YashanDB个人版体验」通过 ODBC 连接 YashanDB 和 MariaDB

原创严少安 2023-11-29

670

前言

前文介绍了如何搭建 YashanDB 个人版，本文将介绍如何通过 ODBC 连接 YashanDB 数据库，并介绍如何将 MairaDB 和 YashanDB 数据库进行互连。

「YashanDB个人版体验」YashanDB 个人版安装初体验

环境信息

Rocky Linux 9.3
YashanDB 23.1 个人版
MariaDB 10.6.16

安装 unixODBC

在 Linux 环境下，通过 ODBC 连接 YashanDB 数据库，需要先安装 unixODBC 依赖。

sudo dnf install unixODBC unixODBC-devel

[shawnyan@rocky9 ~]$ sudo dnf install unixODBC unixODBC-devel
Dependencies resolved.
=========================================================================================================================
 Package                         Architecture            Version                        Repository                  Size
=========================================================================================================================
Installing:
 unixODBC                        x86_64                  2.3.9-4.el9                    appstream                  458 k
 unixODBC-devel                  x86_64                  2.3.9-4.el9                    crb                         50 k

Transaction Summary
=========================================================================================================================
Install  2 Packages

Total download size: 508 k
Installed size: 1.6 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): unixODBC-devel-2.3.9-4.el9.x86_64.rpm                                             118 kB/s |  50 kB     00:00
(2/2): unixODBC-2.3.9-4.el9.x86_64.rpm                                                   789 kB/s | 458 kB     00:00
-------------------------------------------------------------------------------------------------------------------------
Total                                                                                    867 kB/s | 508 kB     00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                 1/1
  Installing       : unixODBC-2.3.9-4.el9.x86_64                                                                     1/2
  Installing       : unixODBC-devel-2.3.9-4.el9.x86_64                                                               2/2
  Running scriptlet: unixODBC-devel-2.3.9-4.el9.x86_64                                                               2/2
  Verifying        : unixODBC-2.3.9-4.el9.x86_64                                                                     1/2
  Verifying        : unixODBC-devel-2.3.9-4.el9.x86_64                                                               2/2

Installed:
  unixODBC-2.3.9-4.el9.x86_64                              unixODBC-devel-2.3.9-4.el9.x86_64

Complete!
[shawnyan@rocky9 ~]$

YashanDB ODBC 驱动安装

下载 YashanDB ODBC

进入 YashanDB 下载中心，选择【YashanDB Connector/ODBC (Linux X86)】，并下载。

[shawnyan@rocky9 yashan]$ ll -h
total 243M
-rw-r--r-- 1 shawnyan shawnyan 176K Nov  2 16:40 yashandb-odbc-23.1.1.100-linux-x86_64.tar.gz
-rw-r--r-- 1 shawnyan shawnyan 243M Nov 21 16:29 yashandb-personal-23.1.1.100-linux-x86_64.tar.gz
[shawnyan@rocky9 yashan]$

解压、安装 ODBC 驱动

[shawnyan@rocky9 yashandb-odbc-23.1.1.100-linux-x86_64]$ ll -h
total 724K
-rwxrwxr-x 1 shawnyan shawnyan 197K Oct 19 17:01 libyas_odbc.so
-rwxrwxr-x 1 shawnyan shawnyan 197K Oct 19 17:01 libyas_odbc.so.0
-rwxrwxr-x 1 shawnyan shawnyan 197K Oct 19 17:01 libyas_odbc.so.0.1.0
-rwxrwxr-x 1 shawnyan shawnyan 8.4K Oct 19 17:01 yasodbcinstall
-rwxrwxr-x 1 shawnyan shawnyan 112K Oct 19 17:01 yasodbctest
[shawnyan@rocky9 yashandb-odbc-23.1.1.100-linux-x86_64]$

Ps. yasodbcinstall 这个工具并不好用，直接执行后，会生成乱码。

[root@rocky9 yashan]# cat /etc/odbcinst.ini | tail -n4
[YashanDB ODBC Driver]
@=
UsageCount=1

[root@rocky9 yashan]#

官方文档里没有该工具的说明，暂且忽略掉了。

配置驱动 odbcinst.ini

[root@rocky9 yashan]# vi /etc/odbcinst.ini

[YashanDB]
Description     = ODBC for YashanDB
Driver          = /usr/lib64/yashandb/libyas_odbc.so
Setup           = /usr/lib64/yashandb/libyas_odbc.so
Driver64        = /usr/lib64/yashandb/libyas_odbc.so
Setup64         = /usr/lib64/yashandb/libyas_odbc.so
FileUsage       = 1

配置 YashanDB 数据源

[root@rocky9 yashan]# vi /etc/odbc.ini

[YashanDB]
Description  = YashanDB
Driver       = YashanDB
SERVER       = 127.0.0.1
PORT         = 1688
USER         = sys
PWD          = yasdb_123

使用 isql 测试连接 YashanDB

连接数据库：

isql -v YashanDB

测试输出：

[yashan@rocky9 yashandb]$ isql -v YashanDB
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from sbtest.t1
+------------+
| ID         |
+------------+
| 1          |
+------------+
SQLRowCount returns 0
1 rows fetched
SQL> select * from sbtest.t2
[S1000][YashanDB][ODBC Driver][Yas][1:15]YAS-02012 table or view does not exist
[ISQL]ERROR: Could not SQLPrepare
SQL> select * from sbtest1.t1
[S1000][YashanDB][ODBC Driver][Yas][1:15]YAS-02010 user 'SBTEST1' does not exist
[ISQL]ERROR: Could not SQLPrepare
SQL> quit
[yashan@rocky9 yashandb]$

出现 Connected 就说明已经连接成功。后面做了两个测试，查询不存在的表和不存在的模式，都会报错，符合预期。

使用自带的 yasodbctest 程序测试连接

连接数据库：

./yasodbctest YashanDB

测试输出：

[yashan@rocky9 yashandb]$ ./yasodbctest YashanDB
Connected!
Enter SQL commands, type (control)Z to exit
SQL COMMAND>select 1 from dual
| 1            |
|           1  |

SQL COMMAND>select * from sbtest.t1
| ID           |
|           1  |

SQL COMMAND>select * from sbtest1.t1
[HY000] [YashanDB][ODBC Driver][Yas][1:15]YAS-02010 user 'SBTEST1' does not exist (2010)
SQL COMMAND>exit

Disconnected.
[yashan@rocky9 yashandb]$

到此，通过 ODBC 连接 YashanDB 数据库已讲解完成，下面将介绍如何凭借 ODBC 将 MariaDB 和 YashanDB 连接。

连接 YashanDB 和 MariaDB

安装 MairaDB 及 CONNECT 引擎

这一部分内容并不是本文重点，所以仅做简要说明。

下载 MariaDB 安装包。

解压、安装：

[shawnyan@rocky9 ~]$ sudo dnf install MariaDB-server MariaDB-connect-engine
Last metadata expiration check: 0:01:49 ago on Tue 28 Nov 2023 10:37:52 AM UTC.
Dependencies resolved.
================================================================================
 Package                    Arch       Version              Repository     Size
================================================================================
Installing:
 MariaDB-connect-engine     x86_64     10.6.16-1.el9        MariaDB       2.6 M
 MariaDB-server             x86_64     10.6.16-1.el9        MariaDB        18 M
Installing dependencies:
 MariaDB-client             x86_64     10.6.16-1.el9        MariaDB       8.5 M
 MariaDB-common             x86_64     10.6.16-1.el9        MariaDB        88 k
 MariaDB-shared             x86_64     10.6.16-1.el9        MariaDB       131 k
 daxctl-libs                x86_64     71.1-8.el9           baseos         41 k
 galera-4                   x86_64     26.4.16-1.el9        MariaDB       1.4 M
 libpmem                    x86_64     1.12.1-1.el9         appstream     111 k
 ndctl-libs                 x86_64     71.1-8.el9           baseos         88 k
 socat                      x86_64     1.7.4.1-5.el9        appstream     300 k

Transaction Summary
================================================================================
Install  10 Packages

Total size: 31 M
Total download size: 539 k
Installed size: 208 M
Is this ok [y/N]: y
...
Installed:
  MariaDB-client-10.6.16-1.el9.x86_64
  MariaDB-common-10.6.16-1.el9.x86_64
  MariaDB-connect-engine-10.6.16-1.el9.x86_64
  MariaDB-server-10.6.16-1.el9.x86_64
  MariaDB-shared-10.6.16-1.el9.x86_64
  daxctl-libs-71.1-8.el9.x86_64
  galera-4-26.4.16-1.el9.x86_64
  libpmem-1.12.1-1.el9.x86_64
  ndctl-libs-71.1-8.el9.x86_64
  socat-1.7.4.1-5.el9.x86_64

Complete!

启动 MariaDB Server 并查看 CONNECT 存储引擎

启动 MariaDB Server

[shawnyan@rocky9 ~]$ sudo systemctl start mariadb
[shawnyan@rocky9 ~]$ systemctl status mariadb
● mariadb.service - MariaDB 10.6.16 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; preset:>
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─migrated-from-my.cnf-settings.conf
     Active: active (running) since Tue 2023-11-28 10:40:59 UTC; 1s ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 196683 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_>
    Process: 196684 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] &>
    Process: 196706 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP>
   Main PID: 196695 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 12 (limit: 22756)
     Memory: 62.3M
        CPU: 247ms
     CGroup: /system.slice/mariadb.service
             └─196695 /usr/sbin/mariadbd

Nov 28 10:40:59 rocky9.shawnyan.cn mariadbd[196695]: 2023-11-28 10:40:59 0 [Not>
Nov 28 10:40:59 rocky9.shawnyan.cn mariadbd[196695]: 2023-11-28 10:40:59 0 [Not>

查看 CONNECT 存储引擎

MariaDB [information_schema]> select * from information_schema.PLUGINS where PLUGIN_NAME = 'CONNECT'\G
*************************** 1. row ***************************
           PLUGIN_NAME: CONNECT
        PLUGIN_VERSION: 1.7
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: STORAGE ENGINE
   PLUGIN_TYPE_VERSION: 100616.0
        PLUGIN_LIBRARY: ha_connect.so
PLUGIN_LIBRARY_VERSION: 1.14
         PLUGIN_AUTHOR: Olivier Bertrand
    PLUGIN_DESCRIPTION: Management of External Data (SQL/NOSQL/MED), including Rest query results
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
       PLUGIN_MATURITY: Stable
   PLUGIN_AUTH_VERSION: 1.07.0003
1 row in set (0.001 sec)

可以看到 CONNECT 存储引擎已经安装成功，并处于激活状态。

创建 CONNECT 表

在 MariaDB 中创建表 t1，表类型为 ODBC，通过刚才配置的 odbc.ini 远程连接到 YashanDB，连接到 YashanDB 中的 USERNAME.T1 表。

CREATE OR REPLACE TABLE t1 (id varchar(2)) 
ENGINE=CONNECT TABLE_TYPE=ODBC 
CONNECTION='DSN=YashanDB' TABNAME='USERNAME.T1';

查看建表语句：

MariaDB [test]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` varchar(2) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CONNECTION='DSN=YashanDB' `TABLE_TYPE`=ODBC `TABNAME`='USERNAME.T1'
1 row in set (0.002 sec)

测试 CONNECT 表

在 MariaDB 侧写入数据

MariaDB [test]> select * from t1;
+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |
+------+
3 rows in set (0.021 sec)

在 YashanDB 侧读取数据

SQL> select * from username.t1;

          ID 
------------ 
           1
           2
           3

3 rows fetched.

在 MariaDB 和 YashanDB 同时增加字段

-- MariaDB
MariaDB [test]> alter table t1 add column name varchar(10);
Query OK, 0 rows affected (0.008 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- YashanDB
SQL> alter table username.t1 add column name varchar2(10);

Succeed.

在 MariaDB 侧更新数据

MariaDB [test]> update t1 set name = 'ShawnYan' where 1=1;
Query OK, 0 rows affected, 1 warning (0.021 sec)
Rows matched: 0  Changed: 0  Warnings: 1

MariaDB [test]> select * from t1;
+------+----------+
| id   | name     |
+------+----------+
| 1    | ShawnYan |
| 2    | ShawnYan |
| 3    | ShawnYan |
+------+----------+
3 rows in set (0.017 sec)

在 YashanDB 侧读取数据

SQL> select * from username.t1;

          ID NAME          
------------ ------------- 
           1 ShawnYan     
           2 ShawnYan     
           3 ShawnYan     

3 rows fetched.

需要注意的是，如果 YashanDB 侧，增加列 cd2 decimal(2,0)，而 MairaDB 侧增加列 cd2 decimal(2,1)，那么，从 MariaDB 侧写入数据后，数据可能会被截断。

-- MariaDB
MariaDB [test]> update t1 set cd2 = 1.1 where 1=1;
Query OK, 0 rows affected, 1 warning (0.023 sec)
Rows matched: 0  Changed: 0  Warnings: 1

MariaDB [test]> select * from t1;
+------+----------+------+------+
| id   | name     | cd   | cd2  |
+------+----------+------+------+
| 1    | ShawnYan |    1 |  1.0 |
| 2    | ShawnYan |    1 |  1.0 |
| 3    | ShawnYan |    1 |  1.0 |
+------+----------+------+------+
3 rows in set (0.017 sec)

-- YashanDB
SQL> select * from username.t1;

          ID NAME                   CD         CD2 
------------ ------------- ----------- ----------- 
           1 ShawnYan                1           1
           2 ShawnYan                1           1
           3 ShawnYan                1           1

3 rows fetched.

但是，如果两边增加相同精度的列，则不会出现这个问题。

分别查看 t1 表在 MariaDB 和 YashanDB 中的表结构。

-> MariaDB

MariaDB [test]> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(2)   | YES  |     | NULL    |       |
| name  | varchar(10)  | YES  |     | NULL    |       |
| cd    | decimal(2,0) | YES  |     | NULL    |       |
| cd2   | decimal(2,1) | YES  |     | NULL    |       |
| cd3   | decimal(2,1) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.003 sec)

-> YashanDB

SQL> SELECT TABLE_NAME,TABLE_TYPE FROM USER_TABLES;

TABLE_NAME                                                       TABLE_TYPE 
---------------------------------------------------------------- ---------- 
T1                                                               HEAP      

1 row fetched.

SQL> desc t1;
NAME                                                             NULL?     DATATYPE                          
---------------------------------------------------------------- --------- --------------------------------- 
ID                                                                         INTEGER                           
NAME                                                                       VARCHAR(10)                       
CD                                                                         NUMBER(2)                         
CD2                                                                        NUMBER(2)                         
CD3                                                                        NUMBER(2,1)                       

SQL>