All geek questions in one place

Hibernate / mysql connection problems

We are working on a basic java struts2 web application with Hibernate 3 ORM. We use mysql as our database.

We found some database related exceptions in the logs. The connection to the database expires before the set time. Here are the main exceptions that we have discovered.

104343235 [pool-6-thread-19] ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 100,838,460 milliseconds ago. The last packet sent successfully to the server was 100,838,461 milliseconds ago. is longer than the server configured 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 'autoReconnect=true' to avoid this problem. org.hibernate.exception.JDBCConnectionException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2536) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) at org.hibernate.loader.Loader.list(Loader.java:2271) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at com.myproject.model.dao.entities.EntitiesDAO.getByIds(EntitiesDAO.java:148) Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 100,838,460 milliseconds ago. The last packet sent successfully to the server was 100,838,461 milliseconds ago. is longer than the server configured 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 'autoReconnect=true' to avoid this problem. at sun.reflect.GeneratedConstructorAccessor345.newInstance(Unknown Source)104343242 [pool-6-thread-16] ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 100,838,544 milliseconds ago. The last packet sent successfully to the server was 100,838,544 milliseconds ago. is longer than the server configured 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 'autoReconnect=true' to avoid this problem. 104343242 [pool-6-thread-16] ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 100,838,544 milliseconds ago. The last packet sent successfully to the server was 100,838,544 milliseconds ago. is longer than the server configured 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 'autoReconnect=true' to avoid this problem. at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:525) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117) at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3829) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2449) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1953) at org.hibernate.loader.Loader.doQuery(Loader.java:802) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) at org.hibernate.loader.Loader.doList(Loader.java:2533) ... 21 more

Every day, we restart our application server as a temporary fix for the problem.

Any help would be greatly appreciated.

+4
source share
2 answers

This is because the MySQL server terminates the connection without activity after a timeout. You need to add some lines to the dbcp configuration. Try adding the following parameters:

 validationQuery="SELECT 1" testOnBorrow="true

How it works: The connection pool tries to start validationQuery before returning the connection. If validationQuesry fails, dbcp will cancel the connection, create a new one and return it.

Here is an example:

 <Resource name="jdbc/cooldatabase" description="Strandls.com license database" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/cooldatabase?autoReconnect=true" username="cooluser" password="coolpassword" initialSize="0" maxActive="20" maxIdle="10" minIdle="0" maxWait="-1" validationQuery="SELECT 1" testOnBorrow="true" poolPreparedStatements="true" removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"/>

Here you can find complete information: http://amitcodes.wordpress.com/2008/07/26/16/

+5
source

Use connection pool with Hibernate. For example c3p0 https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool .

0
source

Source: https://habr.com/ru/post/1442669/

More articles:


All Articles