Java c3p0 池在待机 5-10 分钟后断开连接

Question

在我的 Javafx 应用程序中，我使用 sftp 连接到 Hetzner.de 上的远程服务器。为了管理连接，我使用带有以下参数的 cp30 库连接池:

public Connection dbConnectSite() throws SQLException, PropertyVetoException {
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setDriverClass("com.mysql.jdbc.Driver");
    cpds.setJdbcUrl("jdbc:mysql://" + mySQLHost + ":" + mySQLPort + "/" + mySQLDBName + "?characterEncoding=UTF-8&autoReconnect=true"); // 192.168.100.100 v seti.
    cpds.setUser(mySQLUser);
    cpds.setPassword(mySQLPassword);
    cpds.setMinPoolSize(3);
    cpds.setMaxPoolSize(20); // Maximum number of Connections a pool will maintain at any given time.
    cpds.setAcquireIncrement(1);
    cpds.setTestConnectionOnCheckin(true); // If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid.
    cpds.setTestConnectionOnCheckout(true); // If true, an operation will be performed at every connection checkout to verify that the connection is valid.
    cpds.setIdleConnectionTestPeriod(300); // If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.
    cpds.setMaxIdleTimeExcessConnections(240);
    cpds.setMaxIdleTime(3600); // Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
    cpds.setMaxStatements(100);
    cpds.setCheckoutTimeout(0); // The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted. Zero means wait indefinitely.
    cpds.setMaxAdministrativeTaskTime(0); // Seconds before c3p0's thread pool will try to interrupt an apparently hung task. 
    cpds.setMaxConnectionAge(saytPort); // Seconds, effectively a time to live. A Connection older than maxConnectionAge will be destroyed and purged from the pool. Zero means no maximum absolute age is enforced. 
    cpds.setPreferredTestQuery("SELECT 1");
    dsSite = cpds;
    conSayt = dsSite.getConnection();
    return conSayt;
}

连接正常。连接的控制台日志为:

ноя 10, 2013 9:59:47 PM com.mchange.v2.log.MLog <clinit>
INFO: MLog clients using java 1.4+ standard logging.
ноя 10, 2013 9:59:47 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
ноя 10, 2013 9:59:47 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kfsx8yiing6p1lp8nb2|3ba701c9, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kfsx8yiing6p1lp8nb2|3ba701c9, idleConnectionTestPeriod -> 300, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://sql249.your-server.de:3306/kombinezonik?characterEncoding=UTF-8&autoReconnect=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 22, maxIdleTime -> 3600, maxIdleTimeExcessConnections -> 240, maxPoolSize -> 20, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> SELECT 1, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> true, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]
nov 10, 2013 9:59:49 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> z8kfsx8yiing6p1lp8nb2|2bc60511, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kfsx8yiing6p1lp8nb2|2bc60511, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/kombadmin?characterEncoding=UTF-8&autoReconnect=true, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]

但是如果 GUI 在不同的时间段内处于待机状态，我会收到错误消息:

nov 10, 2013 10:07:50 PM MyClass
SEVERE: null
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 344 144 milliseconds ago.  The last packet sent successfully to the server was 2 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2738)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1617)
    at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
    at MyClass.java:158
    at MyClass$1$1$1.run(MyClass.java:128)
    at com.sun.javafx.application.PlatformImpl$4$1.run(PlatformImpl.java:179)
    at com.sun.javafx.application.PlatformImpl$4$1.run(PlatformImpl.java:176)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.application.PlatformImpl$4.run(PlatformImpl.java:176)
    at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:76)
    at com.sun.glass.ui.gtk.GtkApplication._runLoop(Native Method)
    at com.sun.glass.ui.gtk.GtkApplication$3$1.run(GtkApplication.java:82)
    at java.lang.Thread.run(Thread.java:722)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
    ... 18 more

当然，我使用 try 和资源来关闭语句和结果集:

try (Statement stmt = m.conSayt.createStatement(); ResultSet rs = stmt.executeQuery(SQL);) {
    // Contents.
} catch (SQLException ex){
    // Contents.
}

Answer 1

这里的问题是您使用(恐怕是误用)c3p0 的方式。

每次获得连接时，都会创建一个新的连接池，然后启动连接池，然后让它超出范围。请注意，在您的日志中，您将在三秒内初始化三个不同的 c3p0 池，每次尝试获取连接时都会初始化一次。不好。

你永远不会清理你的连接。 (您实际上并没有在此处提供的代码中清理结果集和语句。)正如 samlewis 所说，看起来您打算只保持连接打开。

所有这些根本不是您应该如何使用连接池。如果您想获取一个 Connection 并使其保持打开状态，只需使用 DriverManager.getConnection( ... ) 直接获取 Connection 即可，从而避免一大堆复杂性。

除非您会发现，正如您已经发现的那样，长时间保持打开 JDBC 连接并期望重用它是运行应用程序的一种脆弱方式。这就是连接池要解决的问题。

对于连接池，事情必须有所不同:

1) 您创建的 ComboPooledDataSource？不要让它消失。在某处保留对它的引用，也许作为静态字段，也许作为您使用的某个对象的成员。您应该为典型的应用程序部署创建一个数据源一次一次。

2) 每次需要连接时，i) 通过在数据源上调用 getConnection() 获取"new"连接； ii) 做任何你需要做的工作； iii) 使用后立即close()该连接。

c3p0 将维护一个连接池，因此所有这些都会很快。 cpds.getConnection() 不会与 dbms 建立网络连接来建立新的连接，它只会从池中为您提供一个连接。当您 close() 连接时，与 dbms 的连接实际上不会被销毁，只是回收回池中。这是 JDBC 标准的透明连接池。

[哎呀!我没有注意到，提问者使用了java 7 try-with-resources，这应该是fina，只要Connections在资源中!]注意，调用在try中创建ResultSets，Statements或Connnections的函数{} block 根本不足以确保这些资源被 close()ed。您必须在finally block 中实际关闭它们:

Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try
{
    con = cpds.getConnection();
    stmt = cpds.createStatement();
    rs = stmt.executeQuery("SELECT something FROM somewhere");
    while ( rs.next() )
    {
       // do some stuff with results
    }
}
finally
{
   try { if (rs != null) rs.close() } catch (SQLException e) { e.printStackTrace(); }
   try { if (stmt != null) stmt.close() } catch (SQLException e) { e.printStackTrace(); }
   try { if (con != null) con.close() } catch (SQLException e) { e.printStackTrace(); }
}

同样，cpds 不应该是局部变量。另一方面，您的 Connection 应该(通常)在打开它的方法的范围内(在 finally block 中)关闭。

[请注意，finally block 内的每个资源 close() 都包装在嵌套的 try 中，以确保一个 close() 中的异常不会阻止其他资源进行最佳尝试 close()。您可以通过将嵌套尝试包装在辅助方法中来使此更清晰。]

其他一些评论:acquireIncrement为1通常是一个坏主意，如果您已经在结帐时测试连接，则没有理由将testConnectionOnCheckin设置为true。但目前，这些都不重要，您的应用程序实际执行的唯一配置是 jdbcUrl、用户和密码，因为您只能获得一个连接，然后丢弃(好吧，试图丢弃)池。

值得解释一下“试图丢弃”。 c3p0 池产生自己的维护线程。当您构造并初始化新池，然后让它们超出范围而不对其进行 close() 操作时，您正在创建线程和内存泄漏。每当您使用完 c3p0 ComboPooledDataSource 时，您必须调用 close() [或更罕见的是静态方法 DataSources.destroy( ... )] 来关闭 DataSource。通常，当应用程序关闭或重置自身时，此操作会完成一次。