Solved

Reconnect mysql

Posted on 2010-09-13
7
1,038 Views
Last Modified: 2013-11-11
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
Comment
Question by:furedde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 40

Expert Comment

by:gurvinder372
ID: 33661083
If you are using hibernate, then
https://forum.hibernate.org/viewtopic.php?t=935513&
0
 
LVL 20

Accepted Solution

by:
ChristoferDutz earned 500 total points
ID: 33661226
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
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 33661250
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:furedde
ID: 33661465
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
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 33661666
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
 
LVL 20

Expert Comment

by:ChristoferDutz
ID: 33661684
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
 

Author Comment

by:furedde
ID: 33664052
Thanks for tha answer, it seems to work now =)
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question