C3p0 and MySql CommunicationsException: Communications link failure

Question

We are running a J2EE application with Spring + Hibernate + MySql which internally uses C3P0 and Connector/J for connection pooling. but we are getting Communications link failure Error following is configuration

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 Configuration

# 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

My application internally call stored procedure after some time I am getting following error

Error Log

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)

now when I look into my database log I found that most of the queries are in "Creating sort index" state as follows

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)

Can any one help me on this...

EDIT

1.c3p0 Initializing Log

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     |
+-----------------------------+----------+

take a loo at this answer http://stackoverflow.com/a/25198914/2260568 — demon101, Aug 08 '14 at 08:19

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

A few things.

Your c3p0 config is odd. Are you sure c3p0 is configured as you'd like? c3p0 properties in standard c3p0.properties files look like c3p0.propertyName not jdbc.propertyName. For your config to take, you must be reading this file and setting these properties programmatically. To check, c3p0 dumps config information at INFO upon pool initialization. Look there.
In general, to prevent Connection timeouts, you want some form of Connection testing. You currently have only one testing-enabling config param, testConnectionOnCheckout, and it is commented out.
It could be that your sorts just take a very long time. But if the issue is a timeout (maybe not!) your Connections seem to be timing out pretty quickly, after just 710 seconds, less than 12 minutes. You might consider checking and if necessary updating server side timeout settings. There are also timeout related settings you might include as part of your JDBC url.
Take a look at this stack overflow answer CommunicationsException: Communications link failure The issue could be related to the length of your results.

edited May 23 '17 at 10:32

Community

1
1

answered Apr 24 '14 at 16:15

Steve Waldman

13,689
1
35
45

Thanks for your reply.. I have make the changes as per your post but still having the same Error please check my edits. – Ashish Jagtap Apr 25 '14 at 06:25
We have multiple databases for multiple ventures running on multiple ports. also we use port forwarding technique so that our application will connect to local port (3306 on localhost) which internally connect to our mysql server. Also I have return same query which will run for multiple ventures which works fine for all ventures except one venture where I seen that status of all the queries is "Creating sort index" but I don't find same query on other ventures ? – Ashish Jagtap Apr 25 '14 at 06:39
value of wait_timeout parameter causing the problem should I increase the value ? – Ashish Jagtap Apr 25 '14 at 12:20

C3p0 and MySql CommunicationsException: Communications link failure

1 Answers1