GBase 8a
运维管理
文章
Gbase8a通过DBlink访问Gbase8a错误解决集锦
发表于2024-03-04 14:12:191052次浏览1个评论
1、ERROR 1105 (HY000): connect gateway server timeout. Connection refused
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> select * from gbase.db_links;
+--------+-------------+-------------+----------+----------+------------------------+---------------------+
| owner | db_link | dblink_priv | username | password | host | created |
+--------+-------------+-------------+----------+----------+------------------------+---------------------+
| public | gbase_link1 | PUBLIC | linkuser | NULL | gbase_link1 | 2024-03-04 12:14:25 |
+--------+-------------+-------------+----------+----------+------------------------+---------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> use vc vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> select * from test@gbase_link1;
ERROR 1105 (HY000): connect gateway server timeout. Connection refused
（2）解决方法
本地端启动gt.sh：
[gbase@gba01 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.17.2.1]$ ps -ef|grep gateway| grep -v grep
[gbase@gba01 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.17.2.1]$ sh gt.sh
[gbase@gba01 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.17.2.1]$ ps -ef|grep gateway| grep -v grep
gbase 19123 1 79 11:36 pts/2 00:00: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 cn.com.gbase.gbaseGateway.server.GBaseGateway
2、ERROR 1105 (HY000): errorCode: 10003, errorMsg: data source file gbase_link1.properties is not exists.
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> select * from gbase.db_links;
+--------+-------------+-------------+----------+----------+------------------------+---------------------+
| owner | db_link | dblink_priv | username | password | host | created |
+--------+-------------+-------------+----------+----------+------------------------+---------------------+
| public | gbase_link1 | PUBLIC | linkuser | NULL | gbase_link1.properties | 2024-03-04 12:14:25 |
+--------+-------------+-------------+----------+----------+------------------------+---------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> use vc vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> select * from test@gbase_link1;
ERROR 1105 (HY000): errorCode: 10003, errorMsg: data source file gbase_link1.properties is not exists.（2）解决方法
本地端创建dblink时，透明网关配置文件名称（不带扩展名）：
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> drop database link gbase_link1;
1 row in set (Elapsed: 00:00:00.00)
gbase> create database link gbase_link1 connect to '' identified by '' using 'gbase_link1';
Query OK, 0 rows affected (Elapsed: 00:00:00.06)
gbase> select * from gbase.db_links;
+--------+-------------+-------------+----------+----------+-------------+---------------------+
| owner | db_link | dblink_priv | username | password | host | created |
+--------+-------------+-------------+----------+----------+-------------+---------------------+
| public | gbase_link1 | PUBLIC | linkuser | NULL | gbase_link1 | 2024-03-04 12:34:23 |
+--------+-------------+-------------+----------+----------+-------------+---------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)
3、ERROR 1818 (HY000): No VC selected.
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> select * from test@gbase_link1;
ERROR 1818 (HY000): No VC selected.
（2）解决方法
本地端USE VC，或者DBA设置默认VC：
#DBA授权：
gbase> set default_vc for linkuser = vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)4、ERROR 1046 (3D000): No database selected
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use vc vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
ERROR 1046 (3D000): No database selected（2）解决方法
本地端USE DATABASE：
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)
5、ERROR 1105 (HY000): The message from gateway is invalid.
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use vc1.fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
ERROR 1105 (HY000): The message from gateway is invalid.（2）解决方法
1、查看gateway日志：
2024-03-04 11:55:04,852 INFO [pool-2-thread-4] common.JSONUtil json2CommandInfo (JSONUtil.java:54) - 获取到的long_protocol_flag为：true
2024-03-04 11:55:04,857 INFO [pool-2-thread-4] logic.GatewayLogic execute (GatewayLogic.java:81) - read datasource file:2024-03-04 11:55:04:test@gbase_link1.properties
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.
at cn.com.gbase.gbaseGateway.dataBaseDriver.gbaseDriver.GBaseGatewayGBaImpl.getDataSourceConnection(GBaseGatewayGBaImpl.java:147)
at cn.com.gbase.gbaseGateway.logic.DataSourceDataBase.getConnection(DataSourceDataBase.java:46)
at cn.com.gbase.gbaseGateway.logic.GatewayLogic.execute(GatewayLogic.java:104)
at cn.com.gbase.gbaseGateway.server.gatewayServer.SocketThreadForRequest.run(SocketThreadForRequest.java:212)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
2024-03-04 11:55:07,899 ERROR [pool-2-thread-4] gbaseDriver.GBaseGatewayGBaImpl getDataSourceConnection (GBaseGatewayGBaImpl.java:146) - Communications link failure
### 数据源端用户和密码错误：
errorCode: 1045, errorMsg: Access denied for user 'linkuser'@'192.168.195.101' (using password: YES)
at cn.com.gbase.gbaseGateway.dataBaseDriver.gbaseDriver.GBaseGatewayGBaImpl.getDataSourceConnection(GBaseGatewayGBaImpl.
at cn.com.gbase.gbaseGateway.logic.DataSourceDataBase.getConnection(DataSourceDataBase.java:46)
at cn.com.gbase.gbaseGateway.logic.GatewayLogic.execute(GatewayLogic.java:104)
at cn.com.gbase.gbaseGateway.server.gatewayServer.SocketThreadForRequest.run(SocketThreadForRequest.java:212)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
### 数据源端用户未获取数据库权限：
errorCode: 1044, errorMsg: Access denied for user 'linkuser'@'%' to database 'fixedreport'
at cn.com.gbase.gbaseGateway.dataBaseDriver.gbaseDriver.GBaseGatewayGBaImpl.getDataSourceConnection(GBaseGatewayGBaImpl.
at cn.com.gbase.gbaseGateway.logic.DataSourceDataBase.getConnection(DataSourceDataBase.java:46)
at cn.com.gbase.gbaseGateway.logic.GatewayLogic.execute(GatewayLogic.java:104)
at cn.com.gbase.gbaseGateway.server.gatewayServer.SocketThreadForRequest.run(SocketThreadForRequest.java:212)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
2、根据日志判断是连接失败，检查配置信息正确与否，解决数据源端网络连通性、数据源数据库的用户和密码正确性、数据库权限：
[gbase@gba01 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.17.2.1]$ cat conf/dataSource/test@gbase_link1.properties
[ds1]
dataSource_IP=192.168.195.232
dataSource_port=5258
dataSource_dbname=fixedreport
dataSource_dbtype=gcluster
dataSource_user=linkuser
dataSource_pwd=linkpass1
dataSource_charset=utf8
[gbase@gba01 GBase8a_MPP_Cluster_Gateway_8.5.1.2_build4.17.2.1]$ ping 192.168.195.232
PING 192.168.195.232 (192.168.195.232) 56(84) bytes of data.
From 192.168.195.101 icmp_seq=1 Destination Host Unreachable
From 192.168.195.101 icmp_seq=2 Destination Host Unreachable
From 192.168.195.101 icmp_seq=3 Destination Host Unreachable
From 192.168.195.101 icmp_seq=4 Destination Host Unreachable
^C
--- 192.168.195.232 ping statistics ---
5 packets transmitted, 0 received, +4 errors, 100% packet loss, time 4003ms
6、ERROR 1142 (42000): SELECT command denied to user 'linkuser'@'localhost' for table 'dblink_568813_0x443a000_0x430ae30'
（1）错误信息
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
gbase> select * from test@gbase_link1;
ERROR 1142 (42000): SELECT command denied to user 'linkuser'@'localhost' for table 'dblink_568813_0x443a000_0x430ae30'
（2）解决方法
本地端DBA为用户设置数据库gctmpdb、gbase权限（包括数据节点）：
[gbase@gba01 set_dblink]$ cat set_permission.sh
#!/bin/bash
###############################################################################
##creator : Tupac Tu
##create time: 2024-02-01
##Decription: 设置数据节点数据库用户权限（传参：DBA用户 DBA密码 dblink用户）
##Method: sh set_permission.sh dbaUserName dbaUserPasswd dbUserName
##Version: 1.0
###############################################################################
#配置文件路径
configDir=/opt/gbase_workspace/scripts/config
#脚本路径
ScriptsDir=/opt/gbase_workspace/scripts/set_dblink
#参数设置
dbaUserName=$1
dbaUserPasswd=$2
dbUserName=$3
#获取配置信息(未用)
config()
{
gbaseMode=`cat ${configDir}/config |grep ^GBaseMode |awk -F= '{print $2}' | awk '{print $1}'`
instanceType=`cat ${configDir}/config |grep ^InstanceType |awk -F= '{print $2}' | awk '{print $1}'`
userName=`cat ${configDir}/config |grep ^UserName |awk -F= '{print $2}' | awk '{print $1}'`
userPasswd=`cat ${configDir}/config |grep ^Password |awk -F= '{print $2}' | awk '{print $1}'`
gccli_command="gccli -u${userName} -p${userPasswd}"
}
#设置数据库用户权限
set_permission()
{
if ! isCmdExist gcadmin; then echo " Failed: execute command (gcadmin) denied to the user"; exit 1; fi
if ! isCmdExist gccli; then echo " Failed: execute command (gccli) denied to the user"; exit 1; fi
if [ "" == "$dbaUserName" ] || [ "" == "$dbaUserPasswd" ] || [ "" == "$dbUserName" ];then echo "Usage: sh $0 dbaUserName dbaUserPasswd dbUserName"; exit 1; fi
check_password
sqlResult=$(${gccli_command} -u${dbaUserName} -p${dbaUserPasswd} -Ns -e "select user from gbase.user where user='${dbUserName}'")
if [ "${sqlResult}" == "" ]; then
echo " * This user is not available in GBase database! * "
exit 1
fi
gbaseMode=$(gcadmin showcluster d|grep "virtual cluster" |wc -l)
vcList=$(gcadmin showcluster d|grep "virtual cluster" | awk -F: '{print $2}' |sed 's/,//g')
gcadmin |grep coordinator[1-9] |awk '{print $4}' > ${configDir}/coor.list
if [ ${gbaseMode} -gt 0 ];then
> ${configDir}/data.list
for vcname in ${vcList}
do
gcadmin showcluster vc ${vcname}|grep node[1-9]|awk '{print $4}' >> ${configDir}/data.list
done
gcadmin showcluster |grep FreeNode[1-9] | awk '{print $4}' >> ${configDir}/data.list
else
gcadmin |grep node[1-9]|awk '{print $4}' > ${configDir}/data.list
fi
gccli -u${dbaUserName} -p${dbaUserPasswd} -e "grant all on gctmpdb.* to ${dbUserName}"
gccli -u${dbaUserName} -p${dbaUserPasswd} -e "grant select on gbase.* to ${dbUserName}"
while read nodeip
do
gccli -u${dbaUserName} -p${dbaUserPasswd} -h${nodeip} -P5050 -e "grant all on gctmpdb.* to ${dbUserName}"
gccli -u${dbaUserName} -p${dbaUserPasswd} -h${nodeip} -P5050 -e "grant select on gbase.* to ${dbUserName}"
done < ${configDir}/data.list
}
#校验密码有效性
check_password()
{
userPasswdLen="${#dbaUserPasswd}"
sqlResult=""
sqlResultLen=""
if [ "${userPasswdLen}" -eq 0 ];then
sqlResult="null password"
echo " * You didn't enter the password for the ${dbaUserName} user! * "
exit 1
else
sqlResult="$(${gccli_command} -u${dbaUserName} -p${dbaUserPasswd} -e quit 2>&1)"
fi
sqlResultLen="${#sqlResult}"
if [ "${sqlResultLen}" -ne 0 ]; then
echo " * The password is incorrect, the GBase 8a cannot be connected! * "
exit 1
fi
}
#校验命令是否有效
isCmdExist() {
local cmd="$1"
if [ -z "$cmd" ]; then
echo "Usage isCmdExist yourCmd"
return 1
fi
which "$cmd" >/dev/null 2>&1
if [ $? -eq 0 ]; then
return 0
fi
return 2
}
main()
{
config
set_permission
}
main $@
[gbase@gba01 set_dblink]$ sh set_permission.sh gbase XXX linkuser
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)7、ERROR 1105 (HY000): SELECT command denied to user 'linkuser'@'192.168.195.101' for table 'table_distribution'
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
ERROR 1105 (HY000): SELECT command denied to user 'linkuser'@'192.168.195.101' for table 'table_distribution'（2）解决方法
1、查看gateway日志：
2024-03-04 12:59:24,782 INFO [pool-2-thread-26] common.JSONUtil json2CommandInfo (JSONUtil.java:54) - 获取到的long_protocol_flag为：true
2024-03-04 12:59:24,787 INFO [pool-2-thread-26] logic.GatewayLogic execute (GatewayLogic.java:81) - read datasource file:2024-03-04 12:59:24:gbase_link1.properties
2024-03-04 12:59:24,856 ERROR [pool-2-thread-26] gatewayServer.SocketThreadForRequest run (SocketThreadForRequest.java:283) - Thread ID = 68-1709528364780: SELECT command denied to user 'linkuser'@'192.168.195.101' for table 'table_distribution',
com.gbase.jdbc.exceptions.jdbc4.GBaseSyntaxErrorException: SELECT command denied to user 'linkuser'@'192.168.195.101' for table 'table_distribution'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_391]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_391]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_391]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_391]
at com.gbase.jdbc.Util.handleNewInstance(Util.java:409) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.Util.getInstance(Util.java:384) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.SQLError.createSQLException(SQLError.java:1055) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.checkErrorPacket(GBaseIO.java:3961) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.checkErrorPacket(GBaseIO.java:3746) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.sendCommand(GBaseIO.java:2033) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.sqlQueryDirect(GBaseIO.java:2187) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2704) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2654) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.StatementImpl.executeQuery(StatementImpl.java:1522) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at cn.com.gbase.gbaseGateway.logic.GatewayLogic.execute(GatewayLogic.java:244) ~[gbaseGateway.jar:?]
at cn.com.gbase.gbaseGateway.server.gatewayServer.SocketThreadForRequest.run(SocketThreadForRequest.java:212) [gbaseGateway.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_391]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_391]
at java.lang.Thread.run(Thread.java:750) [?:1.8.0_391]
2、根据日志判断数据源端用户对table_distribution权限不足，数据源端授gbase的select权限：
#数据源端：
gbase> grant select on gbase.* to linkuser;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
#本地库端：
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)
8、ERROR 1105 (HY000): INSERT,CREATE command denied to user 'linkuser'@'192.168.195.101' for table 'tmptab72ce949b0ace43f8a328efd29bc9f2cd'
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> insert into test select * from test@gbase_link1;
ERROR 1105 (HY000): INSERT,CREATE command denied to user 'linkuser'@'192.168.195.101' for table 'tmptab72ce949b0ace43f8a328efd29bc9f2cd'（2）解决方法
1、查看gateway日志：
2024-03-04 13:04:01,789 INFO [pool-2-thread-29] common.JSONUtil json2CommandInfo (JSONUtil.java:54) - 获取到的long_protocol_flag为：true
2024-03-04 13:04:01,885 ERROR [pool-2-thread-29] gatewayServer.SocketThreadForRequest run (SocketThreadForRequest.java:283) - Thread ID = 76-1709528641785: INSERT,CREATE command denied to user 'linkuser'@'192.168.195.101' for table 'tmptab72ce949b0ace43f8a328efd29bc9f2cd',
com.gbase.jdbc.exceptions.jdbc4.GBaseSyntaxErrorException: INSERT,CREATE command denied to user 'linkuser'@'192.168.195.101' for table 'tmptab72ce949b0ace43f8a328efd29bc9f2cd'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_391]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_391]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_391]
at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_391]
at com.gbase.jdbc.Util.handleNewInstance(Util.java:409) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.Util.getInstance(Util.java:384) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.SQLError.createSQLException(SQLError.java:1055) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.checkErrorPacket(GBaseIO.java:3961) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.checkErrorPacket(GBaseIO.java:3746) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.sendCommand(GBaseIO.java:2033) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.GBaseIO.sqlQueryDirect(GBaseIO.java:2187) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2704) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2654) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.StatementImpl.execute(StatementImpl.java:821) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at com.gbase.jdbc.StatementImpl.execute(StatementImpl.java:661) ~[gbase-connector-java-8.3.81.53-build55.5.7-bin.jar:?]
at cn.com.gbase.gbaseGateway.logic.GatewayLogic.execute(GatewayLogic.java:714) ~[gbaseGateway.jar:?]
at cn.com.gbase.gbaseGateway.server.gatewayServer.SocketThreadForRequest.run(SocketThreadForRequest.java:212) [gbaseGateway.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_391]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_391]
at java.lang.Thread.run(Thread.java:750) [?:1.8.0_391]
2、根据日志判断数据源端用户权限不足（子查询SQL语句），数据源端授gctmpdb、gclusterdb的all权限：
#数据源端：
gbase> grant all on gctmpdb.* to linkuser;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> grant all on gclusterdb.* to linkuser;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)#本地库端：
gbase> insert into test select * from test@gbase_link1;
Query OK, 1 row affected (Elapsed: 00:00:00.63)
Records: 1 Duplicates: 0 Warnings: 0
gbase> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)
9、Select查询数据正常，insert 数据异常can't get datasession（私网不通，网关模式）
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)
gbase> insert into test select * from test@gbase_link1;
ERROR 1708 (HY000): [214.185.215.20:5258](GBA-02AD-0005)Failed to query in gnode:
DETAIL: [10.185.210.105:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-02AD-0005) Gbase general error: can't get datasession: Failed to connect to: 10.174.68.208, Details: last_errno: 203(failed to connect to host address 10.174.68.208.***
gbase> show variables like '%link%';
+--------------------------------------------------+-----------------+
| Variable_name | Value |
+--------------------------------------------------+-----------------+
| _t_gcluster_dblink_clear_syntax_constraints | 0 |
| _t_gcluster_dblink_generate_interim_table_policy | 1 |
| _t_gcluster_dblink_ignore_use_db | 1 |
| _t_gcluster_dblink_insert_select_optimization | 1 |
| gbase_dblink_gateway_ip | 10.174.68.98 |
| gbase_dblink_gateway_port | 9898 |
| gbase_dblink_server_ip | |
| gbase_dblink_standby_gateway_ip | 10.174.68.118 |
| gbase_dblink_standby_gateway_port | 9898 |
| gcluster_dblink_direct_data_exchange | 0 |
| gcluster_dblink_optimize | 0 |
| gcluster_dblink_orcl_case_sensitive | 0 |
| gcluster_enable_vclink_cache | 0 |
| have_symlink | YES |
+--------------------------------------------------+-----------------+
14 rows in set (Elapsed: 00:00:00.01)
（2）解决方法
本地端DBA设置_t_gcluster_dblink_insert_select_optimization=0：
[gbase@gba01 set_dblink]$ gccli -ugbase -p
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> set global _t_gcluster_dblink_insert_select_optimization=0;
Query OK, 0 rows affected (Elapsed: 00:00:00.07)
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> show variables like '%link%';
+--------------------------------------------------+-----------------+
| Variable_name | Value |
+--------------------------------------------------+-----------------+
| _t_gcluster_dblink_clear_syntax_constraints | 0 |
| _t_gcluster_dblink_generate_interim_table_policy | 1 |
| _t_gcluster_dblink_ignore_use_db | 1 |
| _t_gcluster_dblink_insert_select_optimization | 0 |
| gbase_dblink_gateway_ip | 10.174.68.98 |
| gbase_dblink_gateway_port | 9898 |
| gbase_dblink_server_ip | |
| gbase_dblink_standby_gateway_ip | 10.174.68.118 |
| gbase_dblink_standby_gateway_port | 9898 |
| gcluster_dblink_direct_data_exchange | 0 |
| gcluster_dblink_optimize | 0 |
| gcluster_dblink_orcl_case_sensitive | 0 |
| gcluster_enable_vclink_cache | 0 |
| have_symlink | YES |
+--------------------------------------------------+-----------------+
14 rows in set (Elapsed: 00:00:00.01)
gbase> insert into test select * from test@gbase_link1;
Query OK, 1 row affected (Elapsed: 00:00:00.63)
Records: 1 Duplicates: 0 Warnings: 0
gbase> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.08)10、Select查询数据正常，子查询ERROR 1105 (HY000): errorCode: 1825, errorMsg: Access denied for user 'linkuser'@'%'. Account is locked.（私网相通，SIS 模式）
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.16)
gbase> select * from (select * from test@gbase_link1) a;
ERROR 1105 (HY000): errorCode: 1825, errorMsg: Access denied for user 'linkuser'@'%'. Account is locked.
gbase> show variables like '%link%';
+--------------------------------------------------+-----------------+
| Variable_name | Value |
+--------------------------------------------------+-----------------+
| _t_gcluster_dblink_clear_syntax_constraints | 0 |
| _t_gcluster_dblink_generate_interim_table_policy | 1 |
| _t_gcluster_dblink_ignore_use_db | 1 |
| _t_gcluster_dblink_insert_select_optimization | 1 |
| gbase_dblink_gateway_ip | 192.168.195.101 |
| gbase_dblink_gateway_port | 9898 |
| gbase_dblink_server_ip | |
| gbase_dblink_standby_gateway_ip | 192.168.195.102 |
| gbase_dblink_standby_gateway_port | 9898 |
| gcluster_dblink_direct_data_exchange | 0 |
| gcluster_dblink_optimize | 0 |
| gcluster_dblink_orcl_case_sensitive | 0 |
| gcluster_enable_vclink_cache | 0 |
| have_symlink | YES |
+--------------------------------------------------+-----------------+
14 rows in set (Elapsed: 00:00:00.01)（2）解决方法
本地端DBA设置gcluster_dblink_direct_data_exchange=1：
[gbase@gba01 set_dblink]$ gccli -ugbase -p
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> set global gcluster_dblink_direct_data_exchange=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.07)[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> show variables like '%link%';
+--------------------------------------------------+-----------------+
| Variable_name | Value |
+--------------------------------------------------+-----------------+
| _t_gcluster_dblink_clear_syntax_constraints | 0 |
| _t_gcluster_dblink_generate_interim_table_policy | 1 |
| _t_gcluster_dblink_ignore_use_db | 1 |
| _t_gcluster_dblink_insert_select_optimization | 1 |
| gbase_dblink_gateway_ip | 192.168.195.101 |
| gbase_dblink_gateway_port | 9898 |
| gbase_dblink_server_ip | |
| gbase_dblink_standby_gateway_ip | 192.168.195.102 |
| gbase_dblink_standby_gateway_port | 9898 |
| gcluster_dblink_direct_data_exchange | 1 |
| gcluster_dblink_optimize | 0 |
| gcluster_dblink_orcl_case_sensitive | 0 |
| gcluster_enable_vclink_cache | 0 |
| have_symlink | YES |
+--------------------------------------------------+-----------------+
14 rows in set (Elapsed: 00:00:00.01)
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.16)
gbase> select * from (select * from test@gbase_link1) a;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:01.92)11、Select查询数据正常，insert报ERROR 1105 (HY000): errorCode: 1825, errorMsg: Access denied for user 'linkuser'@'%'. Account is locked.（私网相通，SIS 模式）
（1）错误信息
[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select * from test@gbase_link1;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:00.16)
gbase> select * from (select * from test@gbase_link1) a;
ERROR 1105 (HY000): errorCode: 1825, errorMsg: Access denied for user 'linkuser'@'%'. Account is locked.
gbase> show variables like '%link%';
+--------------------------------------------------+-----------------+
| Variable_name | Value |
+--------------------------------------------------+-----------------+
| _t_gcluster_dblink_clear_syntax_constraints | 0 |
| _t_gcluster_dblink_generate_interim_table_policy | 1 |
| _t_gcluster_dblink_ignore_use_db | 1 |
| _t_gcluster_dblink_insert_select_optimization | 0 |
| gbase_dblink_gateway_ip | 192.168.195.101 |
| gbase_dblink_gateway_port | 9898 |
| gbase_dblink_server_ip | |
| gbase_dblink_standby_gateway_ip | 192.168.195.102 |
| gbase_dblink_standby_gateway_port | 9898 |
| gcluster_dblink_direct_data_exchange | 0 |
| gcluster_dblink_optimize | 0 |
| gcluster_dblink_orcl_case_sensitive | 0 |
| gcluster_enable_vclink_cache | 0 |
| have_symlink | YES |
+--------------------------------------------------+-----------------+
14 rows in set (Elapsed: 00:00:00.01)（2）解决方法
本地端DBA设置gcluster_dblink_direct_data_exchange=1：
[gbase@gba01 set_dblink]$ gccli -ugbase -p
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> set global gcluster_dblink_direct_data_exchange=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.07)[gbase@gba01 set_dblink]$ gccli -ulinkuser -plinkpass1
GBase client 9.5.3.27.20_patch.9fc3fcec. Copyright (c) 2004-2024, GBase. All Rights Reserved.
gbase> show variables like '%link%';
+--------------------------------------------------+-----------------+
| Variable_name | Value |
+--------------------------------------------------+-----------------+
| _t_gcluster_dblink_clear_syntax_constraints | 0 |
| _t_gcluster_dblink_generate_interim_table_policy | 1 |
| _t_gcluster_dblink_ignore_use_db | 1 |
| _t_gcluster_dblink_insert_select_optimization | 1 |
| gbase_dblink_gateway_ip | 192.168.195.101 |
| gbase_dblink_gateway_port | 9898 |
| gbase_dblink_server_ip | |
| gbase_dblink_standby_gateway_ip | 192.168.195.102 |
| gbase_dblink_standby_gateway_port | 9898 |
| gcluster_dblink_direct_data_exchange | 1 |
| gcluster_dblink_optimize | 0 |
| gcluster_dblink_orcl_case_sensitive | 0 |
| gcluster_enable_vclink_cache | 0 |
| have_symlink | YES |
+--------------------------------------------------+-----------------+
14 rows in set (Elapsed: 00:00:00.01)
gbase> use fixedreport;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> insert into test select * from test@gbase_link1;
Query OK, 1 row affected (Elapsed: 00:00:00.45)
Records: 1 Duplicates: 0 Warnings: 0
gbase> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | tutu |
+------+------+
1 row in set (Elapsed: 00:00:01.92)
热门帖子
- 12025-12-01浏览数：182759
- 22023-05-09浏览数：25052
- 42023-09-25浏览数：18521
- 52020-05-11浏览数：17526