Java JDBC MySQL 连接问题 - 尝试重新连接 3 次。放弃

JDBC MySQL Connection Issue - Attempted reconnect 3 times. Giving up

提问by HymanB

I have a rest service application running the Java Spring framework. The application depends on a connection to an external MySQL DB, which is connected via JDBC.

我有一个运行 Java Spring 框架的休息服务应用程序。该应用程序依赖于通过 JDBC 连接的外部 MySQL 数据库的连接。

My issue is maintaining a solid connection between the rest service and the MySQL db. I have what I consider a rudimentary connection failsafe in place that looks something like:

我的问题是在其余服务和 MySQL 数据库之间保持稳固的连接。我有我认为的基本连接故障保护，看起来像：

public Connection getConnection() throws SQLException {
    if(connection == null){
         this.buildConnection();
    }
    else if(!connection.isValid(10)){ //Rebuild connection if it is no longer valid
        connection.close();
        this.buildConnection();
    }
    return connection;
}

Using this method should ensure that the connection is valid before any query is executed. My problem is that I periodically get an exception thrown when calling this method:

使用此方法应确保在执行任何查询之前连接有效。我的问题是在调用此方法时会定期抛出异常：

Could not create connection to database server. Attempted reconnect 3 times. Giving up. SQLState: 08001. ErrorCode: 0.

无法创建到数据库服务器的连接。尝试重新连接 3 次。放弃。SQLState：08001。错误代码：0。

The things that have me super perplexed about this are:

让我非常困惑的事情是：

1. This error only happens periodically. Many times the connection works just find.
2. I test this same application on my developer machine and this error never occurs.

1. 此错误只会定期发生。很多时候连接工作只是找到。
2. 我在我的开发人员机器上测试了这个相同的应用程序，这个错误从未发生。

I custom configured the MySQL DB on my own server, so I control all its config options. From this I know that this issue isn't related to the maximum number of connections allowed, or a connection timeout.

我在自己的服务器上自定义配置了 MySQL 数据库，因此我控制了它的所有配置选项。由此我知道这个问题与允许的最大连接数或连接超时无关。

Edit - Update 1:

编辑 - 更新 1：

• This service is hosted as Cloud Service on Microsoft Azure platform.
• I accidentally set it up as an instance in Northern Europe, while the DB is located in North America - probably not related, but trying to paint the whole picture.
• Tried the advice at this linkwith no success. Not using thread pools, and all ResultSets and Statements/PreparedStatements are closed after.

• 此服务作为云服务托管在 Microsoft Azure 平台上。
• 我不小心将它设置为北欧的一个实例，而数据库位于北美 - 可能不相关，但试图描绘整个画面。
• 尝试了此链接中的建议，但没有成功。不使用线程池，并且所有 ResultSets 和 Statements/PreparedStatements 都在之后关闭。

Edit - Update 2

编辑 - 更新 2

After some refactoring, I was able to successfully implement a HikariCP Connection Pool as outlined by @M.Deinum below. Unfortunately, the same problem persists. Everything works great on my local machine, and all Unit Tests pass, but as soon as I push it to Azure and wait more than a few minutes between requests, I get this error, when trying to grab a connection from the pool:

经过一些重构，我能够成功实现一个 HikariCP 连接池，如下面@M.Deinum 所述。不幸的是，同样的问题仍然存在。在我的本地机器上一切正常，并且所有单元测试都通过了，但是一旦我将它推送到 Azure 并在请求之间等待几分钟以上，我就会在尝试从池中获取连接时收到此错误：

springHikariCP - Connection is not available, request timed out after 38268ms. SQLState: 08S01. ErrorCode: 0.

springHikariCP - 连接不可用，请求在 38268 毫秒后超时。SQL 状态：08S01。错误代码：0。

My HikariCP configuration is as follows:

我的HikariCP配置如下：

//Set up connection pool
HikariConfig config = new HikariConfig();
config.setDriverClassName("com.mysql.jdbc.Driver"); 
config.setJdbcUrl("jdbc:mysql://dblocation");

//Connection pool properties
Properties prop = new Properties();
prop.setProperty("user", "Username");
prop.setProperty("password", "Password");
prop.setProperty("verifyServerCertificate", "false");
prop.setProperty("useSSL","true");
prop.setProperty("requireSSL","true");
config.setDataSourceProperties(properties);

config.setMaximumPoolSize(20);
config.setConnectionTestQuery("SELECT 1");
config.setPoolName("springHikariCP");
config.setLeakDetectionThreshold(5000); 

config.addDataSourceProperty("dataSource.cachePrepStmts", "true");
config.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
config.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
config.addDataSourceProperty("dataSource.useServerPrepStmts", "true");

dataSource = new HikariDataSource(config);

Any help would be greatly appreciated.

任何帮助将不胜感激。

采纳答案by M. Deinum

I suggest using a proper JDBC Connection Pool like HikariCPthat together with a validation query which will execute on correct intervals should give you fresh and proper connections each time.

我建议使用合适的 JDBC 连接池，如HikariCP，它与以正确间隔执行的验证查询一起应该每次都能为您提供新鲜和正确的连接。

Assuming you are using Spring and xml to configure the datasource.

假设您使用 Spring 和 xml 来配置数据源。

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
    <property name="poolName" value="springHikariCP" />
    <property name="dataSourceClassName"       value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" />
    <property name="dataSourceProperties">
        <props>
            <prop key="url">${jdbc.url}</prop>
            <prop key="user">${jdbc.username}</prop>
            <prop key="password">${jdbc.password}</prop>
        </props>
    </property>
</bean>

It by default validates connections on checkout. I suggest a try out.

默认情况下，它会在结帐时验证连接。我建议试一试。

As you are using java bases config I suggest the following

当您使用 java bases 配置时，我建议如下

@Bean
public DataSource dataSource() {
    HikariDataSource ds = new HikariDataSource();
    ds.setPoolName("springHikariCP");
    ds.setMaxPoolSize(20);
    ds.setLeakDetectionThreshold(5000);
    ds.setDataSourceClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource");
    ds.addDataSourceProperty("url", url);
    ds.addDataSourceProperty("user", username);
    ds.addDataSourceProperty("password", password);
    ds.addDataSourceProperty("cachePrepStmts", true);
    ds.addDataSourceProperty("prepStmtCacheSize", 250);
    ds.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
    ds.addDataSourceProperty("useServerPrepStmts", true);
    ds.addDataSourceProperty("verifyServerCertificate", false);
    ds.addDataSourceProperty("useSSL", true);
    ds.addDataSourceProperty("requireSSL", true);

    return ds;
}

回答by Peter Pan

It seems to be caused by the system variable wait_timeoutof MySQL.

好像是wait_timeoutMySQL的系统变量引起的。

For MySQL 5.0, 5.1, 5.5, 5.6, the default value for wait_timeoutis 28800 seconds (8 hours), and the maximum value for wait_timeout:

对于 MySQL 5.0、5.1、5.5、5.6，默认值为wait_timeout28800 秒（8 小时），最大值为wait_timeout：

• Linux : 31536000 seconds (365 days, one year)
• Windows : 2147483 seconds (2^31 milliseconds, 24 days 20 hours 31 min 23 seconds)

• Linux：31536000 秒（365 天，一年）
• Windows：2147483 秒（2^31 毫秒，24 天 20 小时 31 分 23 秒）

The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.

服务器在关闭非交互式连接之前等待活动的秒数。此超时仅适用于 TCP/IP 和 Unix 套接字文件连接，不适用于使用命名管道或共享内存建立的连接。

So I think you can try to use a jdbc connection to keep pinging interval some seconds, or directly using a kind of JDBC Connection Pool framework to manage jdbc connections automatically.

所以我认为你可以尝试使用jdbc连接来保持ping间隔几秒钟，或者直接使用一种JDBC连接池框架来自动管理jdbc连接。

Hope it help. Best Regards.

希望有帮助。此致。

回答by Nitin

assuming code you have now for '//Set up connection pool' is called only once, like bean creation, to initialize dataSource.

假设您现在拥有的用于 '//Set up connection pool' 的代码仅被调用一次，就像 bean 创建一样，以初始化 dataSource。

with that, your getConnection() would be just:

这样，您的 getConnection() 将只是：

public Connection getConnection() throws SQLException {
   return dataSource.getConnection();
}

and make sure wait_timeout in mysql is set to minute morethan maxLifetime in hikaricp which is default 30minutes

并确保mysql中的wait_timeout设置为比hikaricp中的maxLifetime多一分钟，默认为30分钟

Hope this helps.

希望这可以帮助。