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
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
- Operating system: ubuntu 18.04
- Apache Doris : 0.15
- mysql database: 8.0.27-0ubuntu.0 20.04.1 for Linux on x86_ sixty-four
- UnixODBC: 2.3.4
- 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 ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz 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 make 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 FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /root/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW 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
https://dev.mysql.com/downloads/connector/odbc/
I downloaded 8.0.26, 8.0.11 and 5.3.13
mysql-connector-odbc-8.0.26-linux-glibc2.12-x86-64bit.tar.gz
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/libmyodbc8w.so" myodbc-installer -a -d -n "MySQL ODBC 8.0.26 ANSI Driver" -t "Driver=/root/mysql-odbc-8.0.26/lib/libmyodbc8a.so"
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 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
[mysql] 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` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=91234 DEFAULT CHARSET=utf8mb4;
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 [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/libodbcpsqlS.so 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/libmyodbc8w.so FileUsage = 1 [MySQL ODBC 5.0 Unicode Driver] Description = ODBC for MySQL Driver=/root/mysql-odbc/lib/libmyodbc5w.so FileUsage = 1 [MySQL ODBC 8.0] Description = ODBC for MySQL Driver=/root/mysql-odbc-8/lib/libmyodbc8w.so FileUsage = 1 [MySQL ODBC 8.0.26] Description = ODBC for MySQL Driver=/root/mysql-odbc-8.0.26//lib/libmyodbc8w.so 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 Driver=/usr/lib/libsqora.so.19.1
First, we create a resource
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", --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 ) ENGINE=ODBC COMMENT "ODBC" PROPERTIES ( "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
-> COMMENT "ODBC"
-> PROPERTIES (
-> "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
IP: 172.16.192.81
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"}
ErrMsg:
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
mysql> CREATE EXTERNAL RESOURCE `mysql_5`
-> 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>
mysql>
mysql> CREATE EXTERNAL TABLE `test_odbc_5` (
-> `id` int NOT NULL ,
-> `name` varchar(255) null
-> ) ENGINE=ODBC
-> COMMENT "ODBC"
-> PROPERTIES (
-> "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