The last packet successfully received from the server was 109,253 milliseconds ago

Question

I know this question has been asked and answered many times but no solution is working for this.

On production I got error like this "The last packet successfully received from the server was 109,253 milliseconds ago.". So i siwtched to c3p0 and has following confguration in persistence.xml and for testing set the property of mysql in my.cnf as wait_timeout=60 and used under settings.

<properties>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
            <property name="hibernate.show_sql" value="true"></property>
            <property name="hibernate.connection.url" value="jdbc:mysql://localhost/Test"/>
            <property name="hibernate.connection.user" value="****" />
            <property name="hibernate.connection.password" value="***" />
            <property name="hibernate.hbm2ddl.auto" value="update" />
            <property name="hibernate.hibernate.auto_close_session" value="true" />

            <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider"/> 
            <property name="hibernate.c3p0.acquire_increment" value="1"/>
            <property name="hibernate.c3p0.idle_test_period" value="10"/>
            <property name="hibernate.c3p0.min_size" value ="5"/>
            <property name="hibernate.c3p0.max_size" value="20"/>
            <property name="hibernate.c3p0.timeout" value="50"/>
            <property name="hibernate.c3p0.preferredTestQuery" value="select 1"/>
            <property name="hibernate.c3p0.testConnectionOnCheckout" value="true"/>             
    </properties>

even in logs I see

Nov 20, 2014 1:06:35 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.2.1 [built 20-March-2013 10:47:27 +0000; debug? true; trace: 10]
Nov 20, 2014 1:06:35 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@1e8ef9ca [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@1f5f812 [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> z8kfsx951kn0e3017dmiyd|24ba9ccb, idleConnectionTestPeriod -> 10, initialPoolSize -> 5, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 50, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 75, maxStatementsPerConnection -> 0, minPoolSize -> 5, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@fa4d1db0 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> z8kfsx951kn0e3017dmiyd|29c3481b, jdbcUrl -> jdbc:mysql://localhost/DAL, properties -> {user=******, password=******} ], preferredTestQuery -> select 1, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> z8kfsx951kn0e3017dmiyd|126d8fa6, numHelperThreads -> 3 ]

but still get same problem. I tried many thing even made hibrenate.properties in class path as i read some where there are some properties like preferredTestQuery whic need to set in properties file so I created a properties file in class path with following properties

hibernate.c3p0.idle_test_period=10
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=75
hibernate.c3p0.max_statements=75
hibernate.c3p0.timeout=50
hibernate.c3p0.preferredTestQuery=select 1
hibernate.c3p0.testConnectionOnCheckout=true

Even properties are loading as in log I can see

org.hibernate.cfg.Environment - HHH000205: Loaded properties from resource hibernate.properties: {hibernate.c3p0.testConnectionOnCheckout=true, hibernate.c3p0.timeout=50, hibernate.c3p0.min_size=5, hibernate.c3p0.max_size=75, hibernate.bytecode.use_reflection_optimizer=false, hibernate.c3p0.preferredTestQuery=select 1, hibernate.c3p0.max_statements=75, hibernate.c3p0.idle_test_period=10

but still get same error if I try after one minute . I can't understand what I am missing. Please let me know?

Solution

Your application may be hold Connections open rather than returning them promptly to the pool.

To test this, try setting unreturnedConnectionTimeout and debugUnreturnedConnectionStackTraces. See here for details.

Then check your logs. If your Connections time-out as unreturned, see the logged stack traces of the codepath by which the Connections were open, and modify your application code to close() those Connections promptly and reliably, and to acquire Connections from the DataSource as needed and just-in-time.

Comments

[Dhruv Pal]

Yes debugUnreturnedConnectionStackTraces helped me out. The connections were not closing properly

[Dhruv Pal]

I was using was Injecting private EntityManager em; At its placed I used Injected private Provider<EntityManager> entityManagerProvider; and it solved my problem