Solved

Reconnect mysql

Posted on 2010-09-13
7
1,021 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
  • 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Creating and Managing Databases with phpMyAdmin in cPanel.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now