asked on

Reconnect mysql

Sometimes I seem to lose connection to my mysql-database and then a connection is thrown and the program stops executing as it is now. I would like to try to reconnect to the database lets say 5 times more with a sleep() of 10 seconds in between to see if I can get the connection back. Which I guess will almost always be the case.

Is there some functionality for this in spring, like a reconnect() function somewhere or what would be the best way to accomplish this?

Not sure why the connection drops in the first case but may be some mysql-problem.

Any help would be appreciated.

Gurvinder Pal Singh

If you are using hibernate, then
https://forum.hibernate.org/viewtopic.php?t=935513&

ASKER CERTIFIED SOLUTION

ChristoferDutz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ChristoferDutz

The trick with this solution is that the pool will issue the "SELECT 1" statement every 10 seconds on an idling connection. If a connection died, the pool will re-connect within 10 seconds. On the other hand whenever a connection is returned, at first the "SELECT 1" query is executed, and only if this was successfull, the connection will be returned. If it was not successfull, a new connection will be created and returned (of course this connection too will be tested first)

furedde

ASKER

Thanks a lot for the answers. I thought I am loosing the connection, not sure actually what happens. The error message loos like this. ( the stacktrace is longer but didnt include all just the first part ). I tmight very well be some timeout or something.

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
      at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:82)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:572)
      at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:786)
      at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:842)
      at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)
      at se.quantify.week.WeekDAOImpl.restoreDatabaseWithNewValues(WeekDAOImpl.java:231)
      at se.quantify.week.WeekDAOImpl.evaluate(WeekDAOImpl.java:140)
      at se.quantify.week.ThreadWeekStep.run(ThreadWeekStep.java:17)
      at java.lang.Thread.run(Thread.java:636)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

ChristoferDutz: Your datasource code looks very intresting. I will try using a BasicDataSource instead. Now I used the primitive
<bean id="dataSource"
            class="org.springframework.jdbc.datasource.DriverManagerDataSource">

That might be a problem, not sure of the different characterstics of the different Datasources.

ChristoferDutz

Yep ... that's the same messages we were getting. And switching to the apache BasicDataSource connection pool together with the validation-queries got rid of these problems :-)

ChristoferDutz

The problem is that the MySQL driver (that implements the socket-client code for communicating with the server) does not seem to notice that the Server killed it's side of the connection. Now as soon as you try to execue a query, your command it sent into data-nirvana and no commit of this communication is recieved. The driver now is really confused about that and complains about the communication-link failure (since it didn't at least get a networt ack message for the request)

furedde

ASKER

Thanks for tha answer, it seems to work now =)