0

I have connection testing enabled in my hibernate c3p0 properties but even after that i am facing connection issue.

Here is my connection configuration:

hibernate.connection.url = "jdbc:mysql://abc.eu-west-1.rds.amazonaws.com:3306/abc";
hibernate.dialect = "org.hibernate.dialect.MySQLDialect";
hibernate.cache.provider_class = "org.hibernate.cache.NoCacheProvider";
hibernate.hbm2ddl.auto = "update";
hibernate.show_sql = "false";
hibernate.connection.provider_class = "org.hibernate.c3p0.internal.C3P0ConnectionProvider";
hibernate.c3p0.min_size = "50";
hibernate.c3p0.max_size = "50";
hibernate.c3p0.idle_test_period = "100";
hibernate.c3p0.max_statements = "0";
hibernate.c3p0.timeout = "43200";
hibernate.c3p0.preferredTestQuery = "select 1";
hibernate.c3p0.testConnectionOnCheckout = "true";

Despite having enabled hibernate.c3p0.testConnectionOnCheckout i am getting the error:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 48,002,613 milliseconds ago.  The last packet sent successfully to the server was 48,002,615 milliseconds ago. is longer than the server con
figured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnec
t=true' to avoid this problem.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_192]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_192]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_192]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_192]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) ~[mysql-connector-java-bin.jar:5.1.33]
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1036) ~[mysql-connector-java-bin.jar:5.1.33]
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3661) ~[mysql-connector-java-bin.jar:5.1.33]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) ~[mysql-connector-java-bin.jar:5.1.33]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[mysql-connector-java-bin.jar:5.1.33]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526) ~[mysql-connector-java-bin.jar:5.1.33]
        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4842) ~[mysql-connector-java-bin.jar:5.1.33]
        at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:1059) ~[c3p0-0.9.5.1.jar:0.9.5.1]
        at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.begin(AbstractLogicalConnectionImplementor.java:67) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:235) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:52) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1525) ~[hibernate-core-5.1.0.Final.jar:5.1.0.Final]
        at org.hibernate.jpa.internal.TransactionImpl.begin(TransactionImpl.java:45) ~[hibernate-entitymanager-5.1.0.Final.jar:5.1.0.Final]
        at com.google.inject.persist.jpa.JpaLocalTxnInterceptor.invoke(JpaLocalTxnInterceptor.java:66) ~[guice-persist-4.0.jar:?]
        at com.google.inject.internal.InterceptorStackCallback$InterceptedMethodInvocation.proceed(InterceptorStackCallback.java:77) ~[guice-4.0.jar:?]
        ...

Shouldn't testConnectionOnCheckout test the connection before performing any query and prevent this kind of error? If not what should i do to correct this error?

Belphegor21
  • 454
  • 1
  • 5
  • 24
  • From the above it looks like the connections should have been kicked out (with timeout) and verified every 100 s, so this error shouldn't have been seen. Your best bet is to enabling [c3p0](https://stackoverflow.com/questions/20101036/c3p0-logging-is-not-working) [logging](https://www.mchange.com/projects/c3p0/#configuring_logging) and understanding whats happening under the hood. – Andy Dufresne Jan 17 '19 at 14:49
  • You have set `idle_test_period` but not `maxIdleTime` which is zero by default, meaning the [connection will stay idle in the pool forever](https://www.mchange.com/projects/c3p0/#maxIdleTime). Set it to a value greater than `idle_test_period`. – LMC Jan 23 '19 at 13:49
  • maxIdleTime is a c3p0 property. The equivalent property in hibernate is `hibernate.c3p0.timeout` which is set. source: https://www.mchange.com/projects/c3p0/#hibernate-specific – Belphegor21 Jan 24 '19 at 06:44

0 Answers0