dblink使用详解及报错排查

1. 什么是透明网关

2. 如何使用透明网关

3. 启动网关：

[gbase@liuyang-node-2 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19]$ sh gt.sh

2. 检查网关是否启动：

[gbase@liuyang-node-2 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19]$ ps -aux|grep gateway

gbase 26450 15.0 0.5 4598560 46796 pts/0 Sl 09:19 0:01 java -Dfile.encoding=UTF-8 -Dcom.sun.management.jmxremote -Xmx2048m -cp gateway/gbaseGateway.jar:lib/commons-beanutils-1.7.0.jar:lib/commons-collections-3.2.2.jar:lib/commons-lang-2.4.jar:lib/commons-logging-1.1.jar:lib/commons-net-1.4.1.jar:lib/dom4j-1.6.1.jar:lib/ezmorph-1.0.6.jar:lib/gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:lib/jaxen-1.1-beta-6.jar:lib/json-lib-2.4-jdk15.jar:lib/jtds-1.2.5.jar:lib/log4j-api-2.17.0.jar:lib/ojdbc8.jar:lib/commons-configuration-1.8.jar:lib/mysql-connector-java-8.0.23.jar:lib/tdgssconfig.jar:lib/terajdbc4.jar:lib/log4j-core-2.17.0.jar:lib/oscarJDBC.jar:lib/oscarClusterJDBC.jar:lib/hive-jdbc-3.1.0-standalone.jar cn.com.gbase.gbaseGateway.server.GBaseGateway

gbase 26466 0.0 0.0 112832 992 pts/0 S+ 09:19 0:00 grep --color=auto gateway

为什么要检查是否启动呢，因为网关未启动时不会出现报错，而如果未安装最新的java，网关不会开启，这时需要通过yum安装java

3.配置网关

[gbase@liuyang-node-2 conf]$ pwd

/home/gbase/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf

[gbase@liuyang-node-2 conf]$ vi conf.properties

conf.properties配置文件用来配置网关，

gbase.gt.port是网关的端口号，默认为9898

gbase.gt.encode是网关的字符集设置，

主要使用的就是这两个，当更改配置文件后，需要重新启动网关才能生效

4.配置源端信息dblink

[gbase@liuyang-node-2 dataSource]$ pwd

/home/gbase/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf/dataSource

[gbase@liuyang-node-2 dataSource]$ cat gbase_link2.properties

[gcluster]

dataSource_IP=10.10.55.36

dataSource_port=5258

dataSource_dbname=test

dataSource_dbtype=gcluster

dataSource_user=root

dataSource_pwd=

dataSource_charset=utf-8

dataSource_IP为源端集群的ip，dataSource_port为集群的端口号，dataSource_dbname为数据库名，dataSource_dbtype为数据库类型，

dataSource_vc可指定vc，不加此参数会以默认vc进行查询set default_vc for root=name暂时此参数还不存在

根据源端数据库类型可以写为mysql/gcluster/oracle/hive/teradata,

dataSource_user和dataSource_pwd分别是账户和密码，dataSource_charset是设置字符集

如果源端是td数据库的话多一个参数

dataSource_url=jdbc:teradata://10.10.58.0/TMODE=ANSI,CHARSET=ASCII,CLIENT_CHARSET=UTF8,database=test

如果源端是异构数据库的话还需要配置

[gbase@liuyang-node-2 conf]$ pwd

/home/gbase/GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.19/conf

[gbase@liuyang-node-2 conf]$ vi gcluster/gbase8a_gcluster.properties

[gc5]

gcluster_IP=10.10.13.120

gcluster_port=5258

gcluster_user=root

gcluster_pwd=

gcluster_encode=utf-8

用来进行回连，这里填写的是目标数据库信息.

若是同源数据库，“insert into 本地表 select * from 源端表 ”不需要配置上述信息，而insert into 源端表 select * from 本地表 ”需要配置上述信息。

为使用方便最好还是配置好信息，而且需要将目标集群的所有coor节点都写入配置文件

5.配置目标集群的配置文件

在目标集群的/opt/10.10.13.120/gcluster/config/gbase_8a_gcluster.cnf文件中增加

gbase_dblink_gateway_ip=和gbase_dblink_gateway_port=,他们是网关的ip和端口号，然后重启服务

注意：若目标集群有多个coor节点，dblink只能在更改了配置文件的节点使用

6.在目标数据库创建dblink

create database link link2 connect to ‘’ identified by ‘’ using ‘oracle_link1’;

create private database link link1 connect to ''identified by ‘’ using ‘gbase_link2’;

select * from gbase.db_links;可查看创建的dblink信息

drop private database link link11;删除私有dblink

7.使用dblink

本地表与远端表不可直接进行join

gbase> select * from t1 join t1@link1;

ERROR 1149 (42000): (GBA-02SC-1001) DBLink table join with (normal table || from sub query) is forbidden

可以将远端表作为子查询进行join

gbase> select * from t1 join (select * from t1@link1) aa;

±-----±-----+

| a | a |

±-----±-----+

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

| 2 | 1 |

| 2 | 2 |

| 2 | 3 |

| 2 | 1 |

| 2 | 2 |

| 2 | 3 |

| 3 | 1 |

| 3 | 2 |

| 3 | 3 |

| 3 | 1 |

| 3 | 2 |

| 3 | 3 |

±-----±-----+

18 rows in set (Elapsed: 00:00:00.31)

异构数据库的表不可直接进行查询，需要将其作为子查询

gbase> select * from t1@link2;

ERROR 1149 (42000): (GBA-02SC-1001) DBLink table from heterogeneous data source must belong to the relation subquery.

gbase> select * from (select * from t1@link2) a ;

±-----±-----±-----±-----+

| A | B | C | D |

±-----±-----±-----±-----+

| 1 | 1 | 1 | 1 |

| 1 | 1 | 1 | 1 |

±-----±-----±-----±-----+

2 rows in set (Elapsed: 00:00:00.53)

直通模式:请求网关直接转发sql语句dao源端数据库执行，只支持自动提交模式。

支持insert 、insert select 、delete、update、truncate、merge、create、drop

passthrough link link2 using ’insert into t2 select * from 2’;

8.多vc使用

_t_gcluster_dblink_ignore_use_db为1远端连接会跟随远端默认vc

_t_gcluster_dblink_ignore_use_db为0时远端连接会跟随本地

gbase> use vc2.test;

Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> show processlist;

±—±----------------±-------------------±-----±------±--------±-----±-----------------------±-----------------+

| Id | User | Host | vc | db | Command | Time | State | Info |

±—±----------------±-------------------±-----±------±--------±-----±-----------------------±-----------------+

| 1 | event_scheduler | localhost | NULL | NULL | Daemon | 9032 | Waiting for event lock | NULL |

| 49 | gbase | 10.10.11.14:55812 | NULL | NULL | Sleep | 3375 | | NULL |

| 74 | root | localhost | vc1 | test2 | Query | 0 | NULL | show processlist |

| 91 | root | 10.10.13.120:64959 | vc2 | test | Sleep | 3 | | NULL |

±—±----------------±-------------------±-----±------±--------±-----±-----------------------±-----------------+

4 rows in set (Elapsed: 00:00:00.00)

gbase> select * from t1@link1;

±-----+

| a |

±-----+

| 2 |

±-----+

1 row in set (Elapsed: 00:00:00.12)

gbase> use vc1.test;

Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> show processlist;

±—±----------------±-------------------±-----±------±--------±-----±-----------------------±-----------------+

| Id | User | Host | vc | db | Command | Time | State | Info |

±—±----------------±-------------------±-----±------±--------±-----±-----------------------±-----------------+

| 1 | event_scheduler | localhost | NULL | NULL | Daemon | 9046 | Waiting for event lock | NULL |

| 49 | gbase | 10.10.11.14:55812 | NULL | NULL | Sleep | 3389 | | NULL |

| 74 | root | localhost | vc1 | test2 | Query | 0 | NULL | show processlist |

| 91 | root | 10.10.13.120:64959 | vc1 | test | Sleep | 2 | | NULL |

±—±----------------±-------------------±-----±------±--------±-----±-----------------------±-----------------+

4 rows in set (Elapsed: 00:00:00.00)

gbase> show databases;

±-------------------+

| Database |

±-------------------+

| information_schema |

| performance_schema |

| gbase |

| gctmpdb |

| gclusterdb |

| test2 |

±-------------------+

源端的进程可以看到连接通道显示vc1.test，但是源端数据库不存在vc1.test

常见问题：

gbase> select * from t1@link1;

ERROR 1105 (HY000): connect gateway server timeout. Connection refused

透明网关未启动

gbase> select * from t1@link1;

ERROR 1105 (HY000): The message from gateway is invalid.

检查datasource文件，ip密码等是否写错

gbase> insert into t1@link1 select * from t1;

ERROR 1105 (HY000): errorCode: 1045, errorMsg: Access denied for user ‘root’@‘10.10.13.120’ (using password: YES)

检查网关的gbase8a_gcluster.properties文件，目标集群的密码错误

gbase> insert into t1@link1 select * from t1;

ERROR 1105 (HY000): errorCode: 0, errorMsg: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

检查网关的gbase8a_gcluster.properties文件,未配置目标集群的信息或不正确

gbase> insert into t1@link1 select * from t1;

ERROR 1105 (HY000): errorCode: 1064, errorMsg: You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near ‘t1’ at line 1

检查网关的gbase8a_gcluster.properties文件,端口号写为gnode的端口号