mysql - C3p0 和 MySql CommunicationsException : Communications link failure

Question

我们正在使用 Spring + Hibernate + MySql 运行一个 J2EE 应用程序，它在内部使用 C3P0 和 Connector/J 用于连接池。但我们收到通信链接失败错误以下是配置

C3P0

c3p0-0.9.5-pre8.jar 
c3p0-oracle-thin-extras-0.9.5-pre8.jar
mchange-commons-java-0.2.7.jar
Connector/J (Previous Version 5.1.7)
mysql-connector-java-5.1.30-bin.jar

C3P0 配置

# Common properties 
c3p0.initialPoolSize=10
c3p0.maxPoolSize=30
c3p0.minPoolSize=5
c3p0.acquireIncrement=5
c3p0.acquireRetryAttempts=0
c3p0.preferredTestQuery=SELECT 1
c3p0.idleConnectionTestPeriod=300
c3p0.numHelperThreads=10
c3p0.maxIdleTimeExcessConnections=240
c3p0.testConnectionOnCheckout=true

一段时间后，我的应用程序在内部调用存储过程时出现以下错误

错误日志

4548717 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#2] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 16
4548718 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#2] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [16],  attempts_remaining: 0
4548721 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#6] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 2, unused: 2, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548721 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#6] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 15
4548721 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#6] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [15],  attempts_remaining: 0
4548724 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#3] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 3, unused: 3, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548724 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#3] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 14
4548724 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#3] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [14],  attempts_remaining: 0
4548726 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#5] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 4, unused: 4, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548726 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#5] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 13
4548726 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#5] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [13],  attempts_remaining: 0
4548734 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#4] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 5, unused: 5, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548735 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#4] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 12
4548735 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#4] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [12],  attempts_remaining: 0
4548735 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#1] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 6, unused: 6, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548735 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#1] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 11
4548735 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#1] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [11],  attempts_remaining: 0
4548738 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#0] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 7, unused: 7, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548739 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#0] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 10
4548739 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#0] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [10],  attempts_remaining: 0
4548739 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#8] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 8, unused: 8, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548739 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#8] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 9
4548740 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#8] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [9],  attempts_remaining: 0
4548740 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#7] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@6242c657 [managed: 9, unused: 9, excluded: 12] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@57390ad3)
4548740 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#7] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#_decrementPendingAcquires  - decremented pending_acquires: 8
4548740 [2F5DAE4C-9FFC-B232-F122-ABA87BCD5DB8:swapnil@ssg.com] [04/24/2014 13:01:13] [C3P0PooledConnectionPoolManager[identityToken->1e3ug9s9114xvrf9lm0waq|2b40c3b9]-HelperThread-#7] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#run  - Acquisition series terminated successfully. Decremented pending_acquires [8],  attempts_remaining: 0
4548912 [B94B4723-BE95-5E33-EDE2-DADA5DDCE0FD:swapnil@ssg.com] [04/24/2014 13:01:13] [http-bio-8081-exec-141] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@59c12050 [managed: 7, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@5d5e3b92)
4548913 [B94B4723-BE95-5E33-EDE2-DADA5DDCE0FD:swapnil@ssg.com] [04/24/2014 13:01:13] [http-bio-8081-exec-141] DEBUG com.mchange.v2.resourcepool.BasicResourcePool#trace  - trace com.mchange.v2.resourcepool.BasicResourcePool@59c12050 [managed: 7, unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@5d5e3b92)
4548958 [C0EDCD6E-799E-EE3A-E0C6-27A38D5CE7DB:indra@ssg.com] [04/24/2014 13:01:13] [http-bio-8081-exec-91] ERROR org.hibernate.transaction.JDBCTransaction#toggleAutoCommit  - Could not toggle autocommit
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 709,563 milliseconds ago.  The last packet sent successfully to the server was 112 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3983)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2596)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5357)

现在，当我查看我的数据库日志时，我发现大多数查询都处于“Creating sort index”状态，如下所示

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB = "mydatabase";

   -+---------+-----------+---------------+
| ID  | USER     | HOST            | DB       | COMMAND | TIME | STATE               | INFO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+---------+-----------+---------------+
| 923 | myuser | %:49513         | mydatabase | Query   | 1049 | Creating sort index | MY Stored Procedure Query | 1048800 |         0 |             0 |
| 853 | myuser | %:37920         | mydatabase | Query   | 3101 | Creating sort index | MY Stored Procedure Query 2 | 3101147 |         0 |             0 |
| 771 | myuser | %:42514         | mydatabase | Query   | 3258 | Creating sort index | MY Stored Procedure Weekly Report | 3258032 |         0 |             0 |
| 924 | myuser | 127.0.0.1:49514 | mydatabase | Sleep   |  149 |                     | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |  149272 |         1 |             0 |
| 854 | myuser | %:37921         | mydatabase | Query   | 3082 | Creating sort index | MY Stored Procedure Query 4 | 3081772 |         0 |             0 |
| 926 | myuser | %:49516         | mydatabase | Query   | 1049 | Creating sort index | MY Stored Procedure Weekly Report | 1048800 |         0 |             0 |
| 851 | myuser | %:37918         | mydatabase | Query   | 3258 | Creating sort index | MY Stored Procedure Query | 3257389 |         0 |             0 |
| 881 | myuser | %:60298         | mydatabase | Query   | 2634 | Creating sort index | MY Stored Procedure Weekly Report | 2634119 |         0 |             0 |
| 873 | myuser | %:33062         | mydatabase | Query   | 3052 | Creating sort index | MY Stored Procedure Weekly Report | 3051387 |         0 |             0 |
| 874 | myuser | %:33063         | mydatabase | Query   | 2996 | Creating sort index | MY Stored Procedure Query 4 | 2996020 |         0 |             0 |
| 875 | myuser | %:33064         | mydatabase | Query   | 3051 | Creating sort index | MY Stored Procedure Query 3 | 3050931 |         0 |             0 |
| 876 | myuser | %:33065         | mydatabase | Query   | 3005 | Creating sort index | MY Stored Procedure Query | 3005103 |         0 |             0 |
| 877 | myuser | %:33066         | mydatabase | Query   | 3005 | Creating sort index | MY Stored Procedure Query 3 | 3005103 |         0 |             0 |
| 887 | myuser | %:60303         | mydatabase | Query   | 2370 | Creating sort index | MY Stored Procedure Query 5 | 2370288 |         0 |        624783 |
| 888 | myuser | %:60304         | mydatabase | Query   | 2634 | Creating sort index | MY Stored Procedure Query | 2634142 |         0 |             0 |
| 889 | myuser | %:60305         | mydatabase | Query   | 2635 | Creating sort index | MY Stored Procedure Query 4 | 2635321 |         0 |             0 |
| 890 | myuser | %:60306         | mydatabase | Query   | 2636 | Creating sort index | MY Stored Procedure Query 6  | 2635389 |         0 |             0 |
| 893 | myuser | %:60335         | mydatabase | Query   | 2634 | Creating sort index | MY Stored Procedure Query 3 | 2633677 |         0 |             0 |
| 894 | myuser | %:60336         | mydatabase | Query   | 2634 | Creating sort index | MY Stored Procedure Query 7 | 2633830 |         0 |             0 |
| 895 | myuser | %:60337         | mydatabase | Query   | 2602 | Creating sort index | MY Stored Procedure Weekly Report | 2601646 |         0 |             0 |
| 896 | myuser | %:60338         | mydatabase | Query   | 2633 | Creating sort index | MY Stored Procedure Query 4 | 2633061 |         0 |             0 |
| 897 | myuser | %:60339         | mydatabase | Query   | 2634 | Creating sort index | MY Stored Procedure Query 4 | 2634026 |         0 |             0 |
| 863 | myuser | %:33046         | mydatabase | Query   | 3052 | Creating sort index | MY Stored Procedure Query 4 | 3052325 |         0 |             0 |
| 928 | myuser | %:49518         | mydatabase | Query   | 1049 | Creating sort index | MY Stored Procedure Query 4 | 1048805 |         0 |             0 |
| 925 | myuser | 127.0.0.1:49515 | mydatabase | Sleep   |  149 |                     | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |  149273 |         1 |             0 |
| 864 | myuser | %:33045         | mydatabase | Query   | 3099 | Creating sort index | MY Stored Procedure Query 7 | 3098693 |         0 |             0 |
| 865 | myuser | %:33047         | mydatabase | Query   | 3061 | Creating sort index | MY Stored Procedure Query 3 | 3060639 |         0 |             0 |
| 866 | myuser | %:33048         | mydatabase | Query   | 3062 | Creating sort index | MY Stored Procedure Query 4 | 3062017 |         0 |             0 |
| 867 | myuser | %:33049         | mydatabase | Query   | 3062 | Creating sort index | MY Stored Procedure Weekly Report | 3061918 |         0 |             0 |
| 855 | myuser | %:37922         | mydatabase | Query   | 3257 | Creating sort index | MY Stored Procedure Query 3 | 3257106 |         0 |             0 |
| 778 | myuser | %:42523         | mydatabase | Query   | 3114 | Creating sort index | MY Stored Procedure Query 5 | 3113575 |         0 |        624783 |
| 776 | myuser | %:42519         | mydatabase | Query   | 3016 | Creating sort index | MY Stored Procedure Query 5 | 3015348 |         0 |        624783 |

32 rows in set (0.39 sec)

谁能帮我解决这个问题...

编辑

1.c3p0初始化日志

INFO  com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource#getPoolManager  - Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 5, acquireRetryAttempts -> 0, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1e3ug9s9115zzlcg148d713|36592bb1, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceUseNamedDriverClass -> false, identityToken -> 1e3ug9s9115zzlcg148d713|36592bb1, idleConnectionTestPeriod -> 300, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://127.0.0.1:33073/rptdb_qa?useOldAliasMetadataBehavior=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 240, maxPoolSize -> 30, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, numHelperThreads -> 10, preferredTestQuery -> SELECT 1, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 3600     |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 3600     |
+-----------------------------+----------+

Answer 1

一些事情。

1. 您的 c3p0 配置很奇怪。您确定 c3p0 已按您的意愿配置了吗？标准 c3p0.properties 文件中的 c3p0 属性看起来像 c3p0.propertyName 而不是 jdbc.propertyName。要采用您的配置，您必须读取此文件并以编程方式设置这些属性。为了检查，c3p0 在池初始化时将配置信息转储到 INFO。看那里。

2. 一般来说，为了防止连接超时，您需要某种形式的连接测试。您目前只有一个启用测试的配置参数 testConnectionOnCheckout，并且它已被注释掉。

3. 可能是您的排序需要很长时间。但是，如果问题是超时(也许不是!)，您的连接似乎很快就会超时，仅在 710 秒之后，不到 12 分钟。您可以考虑检查，如果有必要 updating server side timeout settings .还有timeout related settings you might include as part of your JDBC url .

4. 看看这个堆栈溢出答案CommunicationsException: Communications link failure该问题可能与结果的长度有关。