Usage and configuration of Apache Doris ODBC Mysql under Ubuntu

Posted by maya28 on Wed, 05 Jan 2022 08:32:43 +0100

Today is the last day of 2021. I wish you all a happy New Year's day in advance. This is also the last article in 2021. Later, I will bring you more articles about Doris. At the same time, I hope Doris will take off in 2022, successfully graduate from Apache incubator into a top-level project, and bring you a faster, more stable and more ecological MPP OLAP analytical database product.

The master plan of Apache Doris community in 2022 includes functions, documents, community construction and other aspects that need to be carried out or have been carried out, but need to be continuously optimized. We look forward to more small partners participating in the discussion. I also hope to pay more attention to Doris and add Star to Doris

Doris 2022 Roadmap

Previously, I wrote about the use method and implementation of Apache Doris appearance under Centos, but many users use ubuntu system. Under this system, many users encounter the problem of BE service downtime caused by ODBC appearance. For this, I specially tested it under ubuntu 18.04

1. Software environment

  1. Operating system: ubuntu 18.04
  2. Apache Doris : 0.15
  3. mysql database: 8.0.27-0ubuntu.0 20.04.1 for Linux on x86_ sixty-four
  4. UnixODBC: 2.3.4
  5. Mysql Connector ODBC : 5.3.13,8.0.11,8.0.26

2. Install ODBC driver

First, we install the UNIX ODBC driver. Here we directly give the download address and installation command of the driver

sudo wget 
tar -xvzf unixODBC-2.3.4.tar.gz 
cd unixODBC-2.3.4/
sudo ./configure --prefix=/usr/local/unixODBC-2.3.7 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
sudo make install

After successful installation, the header files required by unixODBC are installed in / usr/inlucde, the compiled library files are installed in / usr/lib, the executable files related to unixODBC are installed in / usr/bin, and the configuration files are placed in / etc.

Verify that the installation was successful

# odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8

3. Install Mysql ODBC driver

Here I default that you know the installation method of Mysql, or you already have a Mysql database, so I won't talk about the installation and configuration of Mysql. If this is not clear, please go to Baidu.

Download the corresponding driver from the mysql site

I downloaded 8.0.26, 8.0.11 and 5.3.13


After downloading and decompressing, I directly renamed the decompressed directory for testing convenience. I did not copy bin and lib in this directory to / usr/local /. The specific operation commands are as follows:

tar zxvf mysql-connector-odbc-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
mv mysql-connector-odbc-8.0.26-linux-glibc2.12-x86-64bit mysql-odbc-8.0.26

Register Mysql driver

myodbc-installer -a -d -n "MySQL ODBC 8.0.26 Unicode Driver" -t "Driver=/root/mysql-odbc-8.0.26/lib/"
myodbc-installer -a -d -n "MySQL ODBC 8.0.26 ANSI Driver" -t "Driver=/root/mysql-odbc-8.0.26/lib/"

Then check whether the registration is successful

Use this command: myodbc installer - D - L

MySQL ODBC 5.0 Unicode Driver
MySQL ODBC 5.0 ANSI Driver
MySQL ODBC 8.0 Unicode Driver
MySQL ODBC 8.0.26 Unicode Driver
MySQL ODBC 8.0.26 ANSI Driver

Here I installed the drivers of the three versions mentioned above, and the installation methods of the other two versions are the same

4. Verify that Mysql is accessed through ODBC

Let's configure the parameters for ODBC to access Mysql

Edit / etc / ODBC INI file, add the following content, and replace the information with your own

Description     = Data source MySQL
Driver          = MySQL ODBC 8.0 Unicode Driver
Server          = localhost
Host            = localhost
Database        = demo
Port            = 3306
User            = root
Password        = zhangfeng

Then we passed:

isql -v mysql

isql -v mysql
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |

This indicates that our ODBC configuration is successful

5. Test the appearance of Apache Doris ODBC

For Doris installation and configuration, please refer to my blog: Apache Doris installation and deployment , or documents on the official website

First, we see a demo library and corresponding tables in the Mysql database

CREATE TABLE `test_cdc` (
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)

Corresponding test data

INSERT INTO `test_cdc` VALUES (123, 'this is a update');
INSERT INTO `test_cdc` VALUES (1212, 'test flink CDC');
INSERT INTO `test_cdc` VALUES (1234, 'This is a test');
INSERT INTO `test_cdc` VALUES (11233, 'zhangfeng_1');
INSERT INTO `test_cdc` VALUES (21233, 'zhangfeng_2');
INSERT INTO `test_cdc` VALUES (31233, 'zhangfeng_3');
INSERT INTO `test_cdc` VALUES (41233, 'zhangfeng_4');
INSERT INTO `test_cdc` VALUES (51233, 'zhangfeng_5');
INSERT INTO `test_cdc` VALUES (61233, 'zhangfeng_6');
INSERT INTO `test_cdc` VALUES (71233, 'zhangfeng_7');
INSERT INTO `test_cdc` VALUES (81233, 'zhangfeng_8');
INSERT INTO `test_cdc` VALUES (91233, 'zhangfeng_9');

Here is Doris's ODBC appearance. Here we use ODBC_Resource to create ODBC appearance, which is also the recommended way, so that ODBC resource s can be reused

First, we set conf / ODBC Inst Ini, add our ODBC driver of 8.0.26 just registered ([MySQL ODBC 8.0.26])

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/
Setup           = /usr/lib/
FileUsage       = 1
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL ODBC 8.0 Unicode Driver]
Description     = ODBC for MySQL
Driver          = /usr/lib64/
FileUsage       = 1
[MySQL ODBC 5.0 Unicode Driver]
Description     = ODBC for MySQL
FileUsage       = 1
[MySQL ODBC 8.0]
Description     = ODBC for MySQL
FileUsage       = 1
[MySQL ODBC 8.0.26]
Description     = ODBC for MySQL
FileUsage       = 1
# Driver from the oracle-connector-odbc package
# Setup from the unixODBC package
[Oracle 19 ODBC driver]
Description=Oracle ODBC driver for Oracle 19

First, we create a resource

"host" = "localhost",
 "port" = "3306",
 "user" = "root",
 "password" = "zhangfeng",
 "database" = "demo",
 "table" = "test_cdc",
 "driver" = "MySQL ODBC 8.0.26",  --Note that the name here should be the same as that in the red box above[]The names in the are consistent
 "odbc_type" = "mysql",
 "type" = "odbc_catalog"

Create an ODBC facade based on this resource

CREATE EXTERNAL TABLE `test_odbc_8_0_26` (
  `id` int NOT NULL ,
  `name` varchar(255) null
"odbc_catalog_resource" = "mysql_8_0_26", --The name here is what we defined above resource Name of
"database" = "demo",
"table" = "test_cdc"

results of enforcement

mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table test_odbc_8_0_26;
Query OK, 0 rows affected (0.01 sec)
mysql>  CREATE EXTERNAL RESOURCE `mysql_8_0_26`
    ->  PROPERTIES (
    -> "host" = "localhost",
    ->  "port" = "3306",
    ->  "user" = "root",
    ->  "password" = "zhangfeng",
    ->  "database" = "demo",
    ->  "table" = "test_cdc",
    ->  "driver" = "MySQL ODBC 8.0.26",
    ->  "odbc_type" = "mysql",
    ->  "type" = "odbc_catalog"
    ->  );
Query OK, 0 rows affected (0.01 sec)
mysql>  CREATE EXTERNAL TABLE `test_odbc_8_0_26` (
    ->   `id` int NOT NULL ,
    ->   `name` varchar(255) null
    -> ) ENGINE=ODBC
    -> "odbc_catalog_resource" = "mysql_8_0_26",
    -> "database" = "demo",
    -> "table" = "test_cdc"
    -> );
Query OK, 0 rows affected (0.01 sec)

Everything is normal. Here is the time to witness the miracle. We perform the query operation

select * from test_odbc_8_0_26;
ERROR 1064 (HY000): errCode = 2, detailMessage = there is no scanNode Backend. [10002: in black list(io.grpc.StatusRuntimeException: UNAVAILABLE: Network closed for unknown reason)]

At this time, it shows that the BE node is hung. We can view it through the show backends command, which also shows that the BE node is hung

mysql> show backends\G;
*************************** 1. row ***************************
            BackendId: 10002
              Cluster: default_cluster
        HeartbeatPort: 9050
               BePort: 9060
             HttpPort: 8040
             BrpcPort: 8060
        LastStartTime: 2021-12-31 10:43:16
        LastHeartbeat: 2021-12-31 11:03:00
                Alive: false
 SystemDecommissioned: false
ClusterDecommissioned: false
            TabletNum: 0
     DataUsedCapacity: 0.000
        AvailCapacity: 83.224 GB
        TotalCapacity: 98.305 GB
              UsedPct: 15.34 %
       MaxDiskUsedPct: 15.34 %
                  Tag: {"location" : "default"}
              Version: 0.15.1-rc09-Unknown
               Status: {"lastSuccessReportTabletsTime":"2021-12-31 11:02:22","lastStreamLoadTime":-1}
1 row in set (0.00 sec)

View BE's log

It shows that the BE is down due to ODBC problems, because I had no problems in centos before (but the version is not this, it is 8.0.11). Here, it is considered that the version may not match. Later, I asked the developers of the community, and the answer is really this question. Let's try another version. At this time, the first thing I thought of was that I don't use 8 X version of the driver, using 5 X try, about 5 I won't explain the driver installation of X in detail, and 8 The installation method of X is the same. Refer to the above section

*** Aborted at 1640918068 (unix time) try "date -d @1640918068" if you are using GNU date ***
PC: @     0x7f8caaf29b7e (unknown)
*** SIGSEGV (@0x0) received by PID 56420 (TID 0x7f8c62370700) from PID 0; stack trace: ***
    @          0x3022682 google::(anonymous namespace)::FailureSignalHandler()
    @     0x7f8cab0f93c0 (unknown)
    @     0x7f8caaf29b7e (unknown)
    @          0x34ef4ac getrn
    @          0x34ef722 lh_insert
    @          0x34abd14 OBJ_NAME_add
    @     0x7f8c09f9d115 ossl_init_ssl_base_ossl_
    @     0x7f8cab0f647f __pthread_once_slow
    @     0x7f8c0a3af194 CRYPTO_THREAD_run_once
    @     0x7f8c09f9cf87 OPENSSL_init_ssl
    @     0x7f8c0a77e504 ssl_start()
    @     0x7f8c0a751f55 mysql_server_init
    @     0x7f8c0a75c425 mysql_init
    @     0x7f8c0a72cbcd DBC::connect()
    @     0x7f8c0a72f403 MySQLDriverConnect()
    @     0x7f8c0a74ea08 SQLDriverConnectW
    @          0x3b060ec SQLDriverConnect
    @          0x1ec046b doris::ODBCConnector::open()
    @          0x1eb9706 doris::OdbcScanNode::open()
    @          0x189e6e9 doris::PlanFragmentExecutor::open_internal()
    @          0x189fb4c doris::PlanFragmentExecutor::open()
    @          0x181b70e doris::FragmentExecState::execute()
    @          0x181f706 doris::FragmentMgr::_exec_actual()
    @          0x1828d4f std::_Function_handler<>::_M_invoke()
    @          0x198a963 doris::ThreadPool::dispatch_thread()
    @          0x1984aac doris::Thread::supervise_thread()
    @     0x7f8cab0ed609 start_thread
    @     0x7f8caaec5293 clone
    @                0x0 (unknown)

Let's start to test whether the driver of version 5.3.13 is normal to the appearance of Apache Doris. First, we start the BE node

For ODBC driver installation, registration and Doris BE node configuration in 5.3.13, please refer to the above section. We will directly enter the testing phase

From the test in the figure below, everything is normal

mysql> use demo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
    ->  PROPERTIES (
    -> "host" = "localhost",
    ->  "port" = "3306",
    ->  "user" = "root",
    ->  "password" = "zhangfeng",
    ->  "database" = "demo",
    ->  "table" = "test_cdc",
    ->  "driver" = "MySQL ODBC 5.0 Unicode Driver",
    ->  "odbc_type" = "mysql",
    ->  "type" = "odbc_catalog"
    ->  );
Query OK, 0 rows affected (0.01 sec)
mysql>  CREATE EXTERNAL TABLE `test_odbc_5` (
    ->   `id` int NOT NULL ,
    ->   `name` varchar(255) null
    -> ) ENGINE=ODBC
    -> "odbc_catalog_resource" = "mysql_5",
    -> "database" = "demo",
    -> "table" = "test_cdc"
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> select * from `test_odbc_5`;
| id    | name             |
|   123 | this is a update |
|  1212 | test flink CDC    |
|  1234 | This is a test         |
| 11233 | zhangfeng_1      |
| 21233 | zhangfeng_2      |
| 31233 | zhangfeng_3      |
| 41233 | zhangfeng_4      |
| 51233 | zhangfeng_5      |
| 61233 | zhangfeng_6      |
| 71233 | zhangfeng_7      |
| 81233 | zhangfeng_8      |
| 91233 | zhangfeng_9      |
12 rows in set (0.01 sec)

At this time, I was thinking that 8.0.11 is normal under centos, and is this version normal under ubuntu, so I tested 8.0.11 again. From the test, it is normal. I didn't do any more tests. Maybe 8.0.26 is too new, and all kinds of Linux need to be manually configured, This requires you to be very familiar with these. For the ODBC appearance problem of Doris, there are exceptions. Basically, you use the wrong driver version. Try another version again

Finally, the Mysql ODBC driven version of Apache Doris Mysql ODBC under Ubuntu is recommended: 5.3.13 and 8.0.11. You can also try other versions yourself

Topics: MySQL odbc Doris