java - 通信链路故障问题

Question

我托管了我的第一个 Java EE 网站，但我的 mySQL 连接在几分钟后超时，我收到此错误消息:

HTTP Status 500 - com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

type Exception report

message com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

description The server encountered an internal error that prevented it from fulfilling this request.

exception

dao.DAOException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
dao.OffreDaoImpl.lister(OffreDaoImpl.java:95)
filters.PrechargementFilter.doFilter(PrechargementFilter.java:96)
org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)

root cause

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.
sun.reflect.GeneratedConstructorAccessor28.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:525)
com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
dao.OffreDaoImpl.lister(OffreDaoImpl.java:90)
filters.PrechargementFilter.doFilter(PrechargementFilter.java:96)
org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)

root cause

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2431)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2882)
com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
        com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
dao.OffreDaoImpl.lister(OffreDaoImpl.java:90)
filters.PrechargementFilter.doFilter(PrechargementFilter.java:96)
org.apache.catalina.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108) 

我要求我的托管商增加超时值，这是他的回复:

Please accept our sincerest apologizes for misunderstanding. Unfortunately due to shared hosting environment your request could not be >accomplished - it will affect other customers, that share server resources >for you. (10 min is max value for this parameter). Since you strongly need some features - we can only suggest you considering >to move to VPS or dedicated server where you will have full admin >privileges and can do whatever you want without affecting of another >customers.

As a solution, we would like to suggest you to add in your jdbc connection >string following command: &autoReconnect=true&failOverReadOnly=false&maxReconnects=10

In this case, after timeout, you will be reconnected again.

所以我编辑了我的 config.properties 文件:

url = jdbc:mysql://localhost:3306/myDatabase&autoReconnect=true&failOverReadOnly=false&maxReconnects=10
driver = com.mysql.jdbc.Driver
username = xxxxxx
password = xxxxxx

但我仍然面临着同样的问题。

Answer 1

是的，即使我托管网站，我也在使用 localhost。但是我试图用 URL 连接中的服务器地址 IP 替换 localhost 字符串，但我收到了这个错误消息:

Caused by: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://xxx.xxx.xxx.xx:3306/database_db?autoReconnect=true&failOverReadOnly=false&maxReconnects=10, username = user_name. Terminating connection pool. Original Exception: ------com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 10 times. Giving up.

所以我向我的托管商指出了这个问题，这是他的回复:

Dear Customer,

Kindly use localhost instead IP address, as for your string, we actually asking you to not maintain connections as long as you wish. The thing is we have hard limit on opened connections overall = 300 and 50 per user.

And as this is shared server these connections are being allocated on demand to each user, and if your account consumes 50 connections it is 1/6 of all connections available.