jdbc连接postgresql数据库timeout
异常
数据库挂的时候启动报错(The connection attempt failed)
org.postgresql.util.PSQLException: 尝试连线已失败。
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:233) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:144) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.Driver.makeConnection(Driver.java:410) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.Driver.connect(Driver.java:280) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:718) [tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:650) [tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:468) [tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:143) [tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:118) [tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:107) [tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131) [tomcat-jdbc-8.5.6.jar:na]
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:68) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:254) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:228) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:207) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:207) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111) [hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:847) [hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:874) [hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:353) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:373) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:362) [spring-orm-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1648) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1585) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:554) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) [spring-beans-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1081) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:856) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) [spring-context-4.3.5.RELEASE.jar:4.3.5.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:761) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:371) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1186) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1175) [spring-boot-1.4.3.RELEASE.jar:1.4.3.RELEASE]
at com.xixicat.DatasourcePgTimeoutDemoApplication.main(DatasourcePgTimeoutDemoApplication.java:10) [classes/:na]
Caused by: java.net.SocketException: Operation timed out
at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_71]
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116) ~[na:1.8.0_71]
at java.net.SocketInputStream.read(SocketInputStream.java:170) ~[na:1.8.0_71]
at java.net.SocketInputStream.read(SocketInputStream.java:141) ~[na:1.8.0_71]
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:282) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:386) ~[postgresql-9.3-1102-jdbc4.jar:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:181) ~[postgresql-9.3-1102-jdbc4.jar:na]
... 54 common frames omitted
SocketInputStream#socketRead0
/Library/Java/JavaVirtualMachines/jdk1.8.0_71.jdk/Contents/Home/src.zip!/java/net/SocketInputStream.java
/**
* Reads into an array of bytes at the specified offset using
* the received socket primitive.
* @param fd the FileDescriptor
* @param b the buffer into which the data is read
* @param off the start offset of the data
* @param len the maximum number of bytes read
* @param timeout the read timeout in ms
* @return the actual number of bytes read, -1 is
* returned when the end of the stream is reached.
* @exception IOException If an I/O error has occurred.
*/
private native int socketRead0(FileDescriptor fd,
byte b[], int off, int len,
int timeout)
throws IOException;
soTimeout
socket timeout没有设置的话，为0，理论上是无限阻塞等待，但是实际是由java native根据操作系统的实现来决定的。
To summarize, timeout for blocking sockets is implemented by calling poll on Linux (and select on Windows) to determine that data is available before calling recv. However, at least on Linux, both methods can spuriously indicate that data is available when it is not, leading to recv blocking indefinitely.
正确设置postgresql的timeout
<!-- postgresql jdbc -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.3-1102-jdbc4</version>
</dependency>
ConnectionFactoryImpl
/Users/xixicat/.m2/repository/org/postgresql/postgresql/9.3-1102-jdbc4/postgresql-9.3-1102-jdbc4-sources.jar!/org/postgresql/core/v3/ConnectionFactoryImpl.java
public ProtocolConnection openConnectionImpl(HostSpec[] hostSpecs, String user, String database, Properties info, Logger logger) throws SQLException {
// Extract interesting values from the info properties:
// - the SSL setting
boolean requireSSL;
boolean trySSL;
String sslmode = info.getProperty("sslmode");
if (sslmode==null)
{ //Fall back to the ssl property
requireSSL = trySSL = (info.getProperty("ssl") != null);
} else {
if ("disable".equals(sslmode))
{
requireSSL = trySSL = false;
}
//allow and prefer are not handled yet
/*else if ("allow".equals(sslmode) || "prefer".equals(sslmode))
{
//XXX Allow and prefer are treated the same way
requireSSL = false;
trySSL = true;
}*/
else if ("require".equals(sslmode) || "verify-ca".equals(sslmode) || "verify-full".equals(sslmode))
{
requireSSL = trySSL = true;
} else {
throw new PSQLException (GT.tr("Invalid sslmode value: {0}", sslmode), PSQLState.CONNECTION_UNABLE_TO_CONNECT);
}
}
// - the TCP keep alive setting
boolean requireTCPKeepAlive = (Boolean.valueOf(info.getProperty("tcpKeepAlive")).booleanValue());
// NOTE: To simplify this code, it is assumed that if we are
// using the V3 protocol, then the database is at least 7.4. That
// eliminates the need to check database versions and maintain
// backward-compatible code here.
//
// Change by Chris Smith <cdsmith@twu.net>
for (int whichHost = 0; whichHost < hostSpecs.length; ++whichHost) {
HostSpec hostSpec = hostSpecs[whichHost];
if (logger.logDebug())
logger.debug("Trying to establish a protocol version 3 connection to " + hostSpec);
//
// Establish a connection.
//
int connectTimeout = 0;
String connectTimeoutProperty = info.getProperty("connectTimeout", "0");
try {
connectTimeout = Integer.parseInt(connectTimeoutProperty) * 1000;
} catch (NumberFormatException nfe) {
logger.info("Couldn't parse connectTimeout value:" + connectTimeoutProperty);
}
PGStream newStream = null;
try
{
newStream = new PGStream(hostSpec, connectTimeout);
// Construct and send an ssl startup packet if requested.
if (trySSL)
newStream = enableSSL(newStream, requireSSL, info, logger, connectTimeout);
// Set the socket timeout if the "socketTimeout" property has been set.
String socketTimeoutProperty = info.getProperty("socketTimeout", "0");
try {
int socketTimeout = Integer.parseInt(socketTimeoutProperty);
if (socketTimeout > 0) {
newStream.getSocket().setSoTimeout(socketTimeout*1000);
}
} catch (NumberFormatException nfe) {
logger.info("Couldn't parse socketTimeout value:" + socketTimeoutProperty);
}
// Enable TCP keep-alive probe if required.
newStream.getSocket().setKeepAlive(requireTCPKeepAlive);
// Try to set SO_SNDBUF and SO_RECVBUF socket options, if requested.
// If receiveBufferSize and send_buffer_size are set to a value greater
// than 0, adjust. -1 means use the system default, 0 is ignored since not
// supported.
// Set SO_RECVBUF read buffer size
String receiveBufferSizeProperty = info.getProperty("receiveBufferSize", "-1");
try {
int receiveBufferSize = Integer.parseInt(receiveBufferSizeProperty);
if (receiveBufferSize > -1) {
// value of 0 not a valid buffer size value
if (receiveBufferSize > 0) {
newStream.getSocket().setReceiveBufferSize(receiveBufferSize);
} else {
logger.info("Ignore invalid value for receiveBufferSize: " + receiveBufferSize);
}
}
} catch (NumberFormatException nfe) {
logger.info("Couldn't parse receiveBufferSize value: " + receiveBufferSizeProperty);
}
// Set SO_SNDBUF write buffer size
String sendBufferSizeProperty = info.getProperty("sendBufferSize", "-1");
try {
int sendBufferSize = Integer.parseInt(sendBufferSizeProperty);
if (sendBufferSize > -1) {
if (sendBufferSize > 0) {
newStream.getSocket().setSendBufferSize(sendBufferSize);
} else {
logger.info("Ignore invalid value for sendBufferSize: " + sendBufferSize);
}
}
} catch (NumberFormatException nfe) {
logger.info("Couldn't parse sendBufferSize value: " + sendBufferSizeProperty);
}
logger.info("Receive Buffer Size is " + newStream.getSocket().getReceiveBufferSize());
logger.info("Send Buffer Size is " + newStream.getSocket().getSendBufferSize());
// Construct and send a startup packet.
String[][] params = {
{ "user", user },
{ "database", database },
{ "client_encoding", "UTF8" },
{ "DateStyle", "ISO" },
{ "extra_float_digits", "2" },
{ "TimeZone", createPostgresTimeZone() },
};
sendStartupPacket(newStream, params, logger);
// Do authentication (until AuthenticationOk).
doAuthentication(newStream, hostSpec.getHost(), user, info, logger);
// Do final startup.
ProtocolConnectionImpl protoConnection = new ProtocolConnectionImpl(newStream, user, database, info, logger, connectTimeout);
readStartupMessages(newStream, protoConnection, logger);
runInitialQueries(protoConnection, info, logger);
// And we're done.
return protoConnection;
}
catch (UnsupportedProtocolException upe)
{
// Swallow this and return null so ConnectionFactory tries the next protocol.
if (logger.logDebug())
logger.debug("Protocol not supported, abandoning connection.");
try
{
newStream.close();
}
catch (IOException e)
{
}
return null;
}
catch (ConnectException cex)
{
// Added by Peter Mount <peter@retep.org.uk>
// ConnectException is thrown when the connection cannot be made.
// we trap this an return a more meaningful message for the end user
if (whichHost + 1 < hostSpecs.length) {
// still more addresses to try
continue;
}
throw new PSQLException (GT.tr("Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections."), PSQLState.CONNECTION_UNABLE_TO_CONNECT, cex);
}
catch (IOException ioe)
{
if (newStream != null)
{
try
{
newStream.close();
}
catch (IOException e)
{
}
}
if (whichHost + 1 < hostSpecs.length) {
// still more addresses to try
continue;
}
throw new PSQLException (GT.tr("The connection attempt failed."), PSQLState.CONNECTION_UNABLE_TO_CONNECT, ioe);
}
catch (SQLException se)
{
if (newStream != null)
{
try
{
newStream.close();
}
catch (IOException e)
{
}
}
if (whichHost + 1 < hostSpecs.length) {
// still more addresses to try
continue;
}
throw se;
}
}
throw new PSQLException (GT.tr("The connection url is invalid."), PSQLState.CONNECTION_UNABLE_TO_CONNECT);
}
这里读取两个timeout，connectTimeout以及socketTimeout，读取后都分别*1000了，表示设置的时候是以秒为单位的。
connectTimeout
public PGStream(HostSpec hostSpec, int timeout) throws IOException
{
this.hostSpec = hostSpec;
Socket socket = new Socket();
socket.connect(new InetSocketAddress(hostSpec.getHost(), hostSpec.getPort()), timeout);
changeSocket(socket);
setEncoding(Encoding.getJVMEncoding("US-ASCII"));
_int2buf = new byte[2];
_int4buf = new byte[4];
}
socketTimeout
if (socketTimeout > 0) {
newStream.getSocket().setSoTimeout(socketTimeout*1000);
}
在连接url上设置
spring:
jpa:
show-sql: true
datasource:
url: jdbc:postgresql://192.168.99.100:5432/test?socketTimeout=1&connectTimeout=1
driverClassName: org.postgresql.Driver
username: postgres
password: xxxxxx
validation-query: SELECT 1
test-while-idle: true
test-on-borrow: true
原文链接：https://segmentfault.com/a/1190000008310730
关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台
除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接
本文链接：https://choupangxia.com/2020/06/30/jdbc-postgresql-timeout/
