Solved

Reconnect mysql

Posted on 2010-09-13
7
1,037 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
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…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

679 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