[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1059
  • Last Modified:

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.
0
furedde
Asked:
furedde
  • 4
  • 2
1 Solution
 
Gurvinder Pal SinghCommented:
If you are using hibernate, then
https://forum.hibernate.org/viewtopic.php?t=935513&
0
 
ChristoferDutzCommented:
Are you actually loosing the connection? I guess your problem is that you are using database connection pools and are keeping open a number of connections. After about 8 hours MySQL will silently kill the connection-handler on it's side. As soon as you try to use the connection that was connected to a killed handler, you will get the exceptions you are having.

the keep-alive option never really worked for us, so we switched to a connection pool, that is able to test a connection before returning it to the application. This validation-query solved allmost all of our problems.

Here comes our spring-config for the datasource:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${cweb.database.driver}"/>
        <property name="url" value="${cweb.database.url}"/>
        <property name="username" value="${cweb.database.user}"/>
        <property name="password" value="${cweb.database.password}"/>
        <property name="initialSize" value="5"/>
        <property name="maxIdle" value="5"/>
        <property name="maxActive" value="20"/>
        <property name="validationQuery" value="SELECT 1"/>
        <property name="testOnBorrow" value="true"/>
        <property name="testWhileIdle" value="true"/>
        <property name="timeBetweenEvictionRunsMillis" value="10000"/>
        <property name="minEvictableIdleTimeMillis" value="60000"/>
</bean>

Open in new window

0
 
ChristoferDutzCommented:
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)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
fureddeAuthor Commented:
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.
0
 
ChristoferDutzCommented:
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 :-)
0
 
ChristoferDutzCommented:
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)
0
 
fureddeAuthor Commented:
Thanks for tha answer, it seems to work now =)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now