0
I am having trouble losing connection to Mysql database, which occurs after few minutes of use.
Please, can someone help me? I have no idea what it could be.
Follows my codes:
Hibernate.cfg.xml
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/**********?useSSL=false</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">123456</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.acquireIncrement">2</property>
<property name="hibernate.c3p0.initialPoolSize">3</property>
<property name="hibernate.c3p0.minPoolSize">2</property>
<property name="hibernate.c3p0.maxPoolSize">8</property>
<property name="hibernate.c3p0.maxIdleTime">1200</property>
<property name="hibernate.c3p0.maxConnectionAge">1200</property>
<property name="hibernate.c3p0.acquireRetryAttempts">1</property>
<property name="hibernate.c3p0.acquireRetryDelay">250</property>
Hibernateutil.java
public class HibernateUtil {
private static SessionFactory sessionFactory;
private static ServiceRegistry serviceRegistry;
public static Session getSession() {
return HibernateUtil.getFactory().openSession();
}
public static SessionFactory getFactory() {
if (sessionFactory == null || sessionFactory.isClosed()) {
sessionFactory = HibernateUtil.buildSessionFactory();
}
return sessionFactory;
}
public static void closeFactory() {
sessionFactory.close();
StandardServiceRegistryBuilder.destroy(serviceRegistry);
}
private static SessionFactory buildSessionFactory() {
try {
Configuration configuration = new Configuration().configure();
serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
SessionFactory factory = configuration.buildSessionFactory(serviceRegistry);
return factory;
} catch (HibernateException ex) {
System.err.println("FALHA NA CRIACAO DA SESSION FACTORY = " + ex);
throw new ExceptionInInitializerError(ex);
}
}
}
The mistake
WARN: SQL Error: 0, SQLState: 08S01
ERROR: Communications link failure
The last packet successfully received from the server was 478,001 milliseconds ago. The last packet sent successfully to the server was 22,321 milliseconds ago.
Grave: ==> ERRO ORIGINAL AO EXECUTAR A QUERY: org.hibernate.exception.JDBCConnectionException: Could not open connection
Advertência: StandardWrapperValve[br.com.forcavendasweb.controller.ClientesFVController]: Servlet.service() for servlet br.com.forcavendasweb.controller.ClientesFVController threw exception
org.hibernate.exception.JDBCConnectionException: Could not open connection
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:132)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:235)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:171)
at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67)
at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1431)
at br.com.portalserver.dao.ClienteDAO.listarClientesRepresentante(ClienteDAO.java:109)
at br.com.forcavendasweb.controller.ClientesFVController.atualizarListaCliente(ClientesFVController.java:261)
at br.com.forcavendasweb.controller.ClientesFVController.doGet(ClientesFVController.java:100)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:687)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673)
at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174)
at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:416)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:283)
at com.sun.enterprise.v3.services.impl.ContainerMapper$HttpHandlerCallable.call(ContainerMapper.java:459)
at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:167)
at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:206)
at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:180)
at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:235)
at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:283)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:200)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:132)
at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:111)
at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77)
at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:536)
at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:112)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:117)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:56)
at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:137)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:591)
at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:571)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 478,001 milliseconds ago. The last packet sent successfully to the server was 22,321 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3459)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3900)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2483)
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4819)
at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912)
at org.hibernate.c3p0.internal.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:94)
at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:380)
at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:228)
... 37 more
Caused by: java.net.SocketException: Operation timed out (Read failed)
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.net.SocketInputStream.read(SocketInputStream.java:171)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3008)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3469)
... 47 more
DAO method where the exception was exceeded
public List<Clientes> listarClientesRepresentante(UsuariosFV usu) {
Transaction transaction = null;
Session session;
List<Clientes> listaCliente = Singleton.getListaCliente();
String hql = "FROM Clientes WHERE codcli <= 0 AND idemp = :idemp AND codrep = :codrep";
session = HibernateUtil.getSession();
try {
transaction = session.beginTransaction();
listaCliente = session
.createQuery(hql)
.setParameter("idemp", usu.getIdemp())
.setParameter("codrep", usu.getIdufv())
.list();
if (!transaction.wasCommitted()) {
transaction.commit();
}
} catch (RuntimeException erro) {
if (transaction != null) {
try {
transaction.rollback();
} catch (Exception ex) {
System.err.println("==> ERRO AO FAZER ROLLBACK: " + ex.toString());
}
}
System.err.println("==> ERRO ORIGINAL AO EXECUTAR A QUERY: " + erro.toString());
throw erro;
} finally {
session.close();
}
return listaCliente;
}
Thank you very much
When you connect to the bank without using the C3p0connectionprovider pool, the same problem occurs?
– Bruno Spy
Try to isolate, and if the problem doesn’t occur. It might be something related to C3PO version, search if there are issues related to version and look if you are using the latest version.
– Bruno Spy
Here I use version 0.9.1.2
– Bruno Spy
@Bruno Spy The version of C3P0 is the same, 0.9.1.2. I will do what you suggested and connect without C3P0 and I will see what will be the behavior of it. Return with the result. Thank you very much.
– EduOK
@Bruno Spy did the test, deletes C3p0connectionprovider and even then after a while he loses connection. unfortunately the error continues.
– EduOK
Where is the Exception that you play and print the msg ORIGINAL ERROR WHEN RUNNING THE QUERY?
– Weslley Barbosa
Likely, problem in Mysql, I would try to reinstall. I had a problem similar to this, but the database was remote. Very difficult to know the reason for this type of problem. I believe it is not of application level.
– Bruno Spy
@Weslley Barbosa, follows up the DAO Method where the exception burst.
– EduOK
@Weslley Barbosa, but anywhere in the code, after a few minutes stopped, the application pops this exception. Mysql is parameterized with 28000 timeout.
– EduOK