DruidDataSource高可用问题：Oracle数据库服务器断电之后，15分钟左右客户端才能感知到数据源已不可用

ubby3x7f  于 2023-02-04  发布在  Druid
关注(0)|答案(4)|浏览(131)

我们开发的某金融核心系统，因为技术条件的限制要求，只能使用被认可的技术，原来单台Oracle RAC模式已不能满足性能要求，所以自行开发了基于Druid和Spring Jdbc的DAL层（支持分库分表、路由和高可用、以及微服务数据库访问权限控制，实现逻辑隔离）当前在进程启动后会初始化4个数据源，为每个数据源创建一个DruidDataSource连接池，如下配置项，红色部分是对应的数据源，其中X.X.X.X和X.X.X.X是读库，也是高可用测试针对的数据库。正常情况下当一台读库断电、断网或者数据库crash或者shutdown之后能够在5秒钟之内监测到数据库服务器不能连接，而将之后的数据库请求自动路由到另一台读库上，隔离已经出问题的数据库。但是目前测试发现在断电场景下，需要15分钟作用才能感知到Oracle服务器不可用。求解决思路？

ts.db.names=default,deal
ts.db.initialSize=0
ts.db.minIdle=20
ts.db.maxWait=180000
ts.db.maxActive=100
ts.db.testOnBorrow=false
ts.db.testOnReturn=false
ts.db.testWhileIdle=true
ts.db.poolPerparedStatements=true
ts.db.filters=stat,wall,log4j
ts.db.validationQuerySql=SELECT * FROM DUAL
ts.db.timeBetweenEvictionRunsMillis=60000
ts.db.minEvictableIdleTimeMillis=300000
ts.db.maxPoolPreparedStatementPerConnectionSize=20
ts.db.removeAbandonedTimeout=180

ts.db.default.ds.dialet=org.hibernate.dialect.OracleDialect
ts.db.default.ds.driver=oracle.jdbc.driver.OracleDriver
ts.db.default.ds.url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = XX)))
ts.db.default.ds.username=Jnw5ofpjlbE=bXMtopzWpjiLZU80Y0x3DynZ074CN3qGzvtDA51zTNo=
ts.db.default.ds.password=Jnw5ofpjlbE=uXeUyTycHa7O7nwTYBizjzPptOZv0UC1tsyqDHv13pWkrGBFVbKRnw==
ts.db.default.ds.encrypt=true

ts.db.deal.ds.types=write,read1,read2
ts.db.deal.ds.write.dialet=org.hibernate.dialect.OracleDialect
ts.db.deal.ds.write.driver=oracle.jdbc.driver.OracleDriver
ts.db.deal.ds.write.url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = XXX)))
ts.db.deal.ds.write.username=Jnw5ofpjlbE=bXMtopzWpjiLZU80Y0x3D63JyZgA+9q9v2azTZJhtcrwFcKCgCuzXQ==
ts.db.deal.ds.write.password=Jnw5ofpjlbE=uXeUyTycHa7O7nwTYBizjzPptOZv0UC149aAkf8yMJ44e0in7Iat1w==
ts.db.deal.ds.write.encrypt=true

ts.db.deal.ds.read1.dialet=org.hibernate.dialect.OracleDialect
ts.db.deal.ds.read1.driver=oracle.jdbc.driver.OracleDriver
ts.db.deal.ds.read1.url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = XXXX)))
ts.db.deal.ds.read1.username=Jnw5ofpjlbE=bXMtopzWpjiLZU80Y0x3D63JyZgA+9q9v2azTZJhtcrwFcKCgCuzXQ==
ts.db.deal.ds.read1.password=Jnw5ofpjlbE=uXeUyTycHa7O7nwTYBizjzPptOZv0UC149aAkf8yMJ44e0in7Iat1w==
ts.db.deal.ds.read1.encrypt=true

ts.db.deal.ds.read2.dialet=org.hibernate.dialect.OracleDialect
ts.db.deal.ds.read2.driver=oracle.jdbc.driver.OracleDriver
ts.db.deal.ds.read2.url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = X.X.X.X)(PORT = 1522))(CONNECT_DATA =(SERVICE_NAME = XXXXX)))
ts.db.deal.ds.read2.username=Jnw5ofpjlbE=bXMtopzWpjiLZU80Y0x3D63JyZgA+9q9v2azTZJhtcrwFcKCgCuzXQ==
ts.db.deal.ds.read2.password=Jnw5ofpjlbE=uXeUyTycHa7O7nwTYBizjzPptOZv0UC149aAkf8yMJ44e0in7Iat1w==
ts.db.deal.ds.read2.encrypt=true

目前主要问题是在将数据库showdown immediately时，一个线程异步通过执行查询操作可以在5秒内获得异常信息如下：

private boolean isDataSourceAvailable(Plugin plugin, String name,
			DruidDataSource dataSource, int timeout) {
		// 指定当前构件
		PluginHolder.swithToPlugin(plugin);
		Connection conn = null;
		try {
			conn = dataSource.getConnectionDirect(timeout);// 设置超时时间5m
			if (conn == null) {
				throw new SQLException("无法获取连接");
			}
			Statement stmt = conn.createStatement();
			stmt.setQueryTimeout(timeout);// 默认设置两秒超时时间
			boolean success = stmt.execute("SELECT * FROM DUAL");// 目前仅支持Oracle
			stmt.close();
			return success;
		} catch (Exception e) {
			LOG.error("检查数据源" + name + "高可用发现异常", e);
			return false;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					LOG.error("Close Connection Exception", e);
				}
			}
		}
	}

日志打印：
2017-10-27 15:13:34,509 CST ERROR CODE create connection error, url: jdbc:oracle:thin:@X.X.X.X:1521/CFBMODB2, errorCode 17002, state 08006||com.alibaba.druid.pool
.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2103)
java.sql.SQLRecoverableException: IO Error: Got minus one from a read call
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:148)
at com.alibaba.druid.filter.FilterAdapter.connection_connect(FilterAdapter.java:785)
at com.alibaba.druid.filter.FilterEventAdapter.connection_connect(FilterEventAdapter.java:38)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
at com.alibaba.druid.filter.FilterAdapter.connection_connect(FilterAdapter.java:785)
at com.alibaba.druid.filter.FilterEventAdapter.connection_connect(FilterEventAdapter.java:38)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
at com.alibaba.druid.filter.FilterAdapter.connection_connect(FilterAdapter.java:785)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
at com.alibaba.druid.filter.stat.StatFilter.connection_connect(StatFilter.java:220)
at com.alibaba.druid.filter.FilterChainImpl.connection_connect(FilterChainImpl.java:142)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1463)
at com.alibaba.druid.pool.DruidAbstractDataSource.createPhysicalConnection(DruidAbstractDataSource.java:1525)
at com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2100)
Caused by: oracle.net.ns.NetException: Got minus one from a read call
at oracle.net.ns.Packet.receive(Packet.java:303)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:296)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
:

但是当数据库断电时，辅助需要15分钟左右才能返回如下异常信息

2017-10-27 14:35:15,490 CST WARN CODE not full timeout retry : 1||com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:1091)
2017-10-27 14:35:15,862 CST ERROR CODE discard connection||com.alibaba.druid.pool.DruidDataSource.handleConnectionException(DruidDataSource.java:1325)
java.sql.SQLRecoverableException: IO Error: No route to host
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:886)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1281)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1909)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1871)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)
at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2489)
at com.cfets.ts.s.jdbc.core.SqlValidationFilter.statement_execute(SqlValidationFilter.java:78)
at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487)
at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2451)
at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487)
at com.alibaba.druid.wall.WallFilter.statement_execute(WallFilter.java:398)
at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487)
at com.alibaba.druid.filter.FilterAdapter.statement_execute(FilterAdapter.java:2451)
at com.alibaba.druid.filter.FilterEventAdapter.statement_execute(FilterEventAdapter.java:188)
at com.alibaba.druid.filter.FilterChainImpl.statement_execute(FilterChainImpl.java:2487)
at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.execute(StatementProxyImpl.java:137)
at com.alibaba.druid.pool.DruidPooledStatement.execute(DruidPooledStatement.java:418)
at com.cfets.ts.s.jdbc.core.TsDynamicDataSource.isDataSourceAvailable(TsDynamicDataSource.java:459)
at com.cfets.ts.s.jdbc.core.TsDynamicDataSource.access$700(TsDynamicDataSource.java:40)
at com.cfets.ts.s.jdbc.core.TsDynamicDataSource$DataSourceProbe.checkAndAddAllDisconnectSlaveDataSource(TsDynamicDataSource.java:420)
at com.cfets.ts.s.jdbc.core.TsDynamicDataSource$DataSourceProbe.access$400(TsDynamicDataSource.java:319)
at com.cfets.ts.s.jdbc.core.TsDynamicDataSource$DataSourceProbe$1.run(TsDynamicDataSource.java:368)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.net.SocketException: No route to host
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:152)
at java.net.SocketInputStream.read(SocketInputStream.java:122)
at oracle.net.ns.Packet.receive(Packet.java:300)
at oracle.net.ns.DataPacket.receive(DataPacket.java:106)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:315)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:260)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:185)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:102)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)

druid

4条答案

按热度按时间
ac1kyiln

ac1kyiln1#

https://github.com/alibaba/druid/releases/tag/1.1.5
这个是某些缺省参数配置的问题，升级到1.1.5版本就好了

赞(0）分享  回复(0）举报  2023-02-04
dfddblmv

dfddblmv2#

druid 1.1.1 升级 1.1.5
原来1.1.1 com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlReplaceStatement; 已经删掉了？
SchemaStatVisitor getAliasMap()
SchemaStatVisitor getCurrentTable()
MySqlSchemaStatVisitor.java
这些方法是不是也没有了？

赞(0）分享  回复(0）举报  2023-02-04
kcwpcxri

kcwpcxri3#

@wenshao 大拿 是否替换成其他的方法或者类 ？

赞(0）分享  回复(0）举报  2023-02-04
m0rkklqb

m0rkklqb4#

Druid的版本从1.0.29升级到1.1.5

ds.setConnectionProperties(config.getConnectionProperties());//解决高可用缺陷
connectTimeout=2000;socketTimeout=6000

解决了断电超时检测的问题，谢谢！

赞(0）分享  回复(0）举报  2023-02-04
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com