About Chandan Pandey

Chandan is Sun Certified Enterprise Architect having 8+ years of hands on coding experience and wide range of domain exposure as CMS, Telecom, Networking and Banking. He practices Agility, writes Clean Code and believes that great leaning can be enabled only through even greater sharing.

MySql connections autodrop after a certain hours

MySql is configured to drop any connection which has been Idle for more than 8 hours. What is the implication of this? After you return to your deployed app after a gap of 8 hours (If default SQL parameters have not been changed), you will be greeted with an exception.

How to solve this issue?

  1. Increase the wait_time parameter -Not a good Idea, it might unnecessarily hold on to the resources and not might be a sure shot way. Apart from that, being dependent on an “external” configuration for failover is not a very good idea -what if the server itself crashes, what if this configuration is missed in one of the instnaces, and many such
     
    issues will pop up against this approach.
  2. Use the parameter autoReconnect=true with JDBC URL -My SQl itself does not recommend this, have a look at link and people have reported that this does not work as well, refer link.
  3. Custom handling -have your code identify that connection has been lost and then recover it and try to reconnect, but then it would be a lot of fail over mechanism in code.
  4. The best way I found was to configure pooling mechanism as c3p0. See this post how to configure c3p0 in JPA for hibernate, it’s simple, easy and reliable.

So how do you test that issue is solved?

  1. Change wait_timeout in MySql to just 2 minutes, this is how it can be done from MySql workbench admin console

    mysql_timeout

  2. Keep value of idleTestPeriod less than wait_timeout -A quick recap what idleTestPeriod signifies
  3. idleTestPeriod:  default value=0; If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds
  4. Login after wait_timeout has passed -it should not throw a exception

 

Reference: MySql connections autodrop after a certain hours from our JCG partner Chandan Pandey at the Thoughts on Software design and development blog.

Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You’ll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You’ll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


− nine = 0



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.

Sign up for our Newsletter

15,153 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books