I have a webapp that is running on amazon ec2 on tomcat with hibernate and rest, my mySQL is standalone instance through amazon rds.
Once i start my webapp - everything is working fine, but recently i configured daily backups on my database and then started seeing problems with my webapp connecting to mySQL.
Basically the problem is only happens if my webapp was started before i mysql instance was restarted(backed up). Then after mySQL restart for some reason any connections to it from my webapp are failing.
It all resolves once i restart my ec2 vm (It might resolve if i restart tomcat as well, but i haven't tried that)
How can i make sure my webapp gets connected back to the mysql after mysql restart?
This is what im getting written to my log:
21-May-2015 11:42:27.857 WARN [http-nio-8080-exec-2] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions SQL Error: 0, SQLState: 08S01
21-May-2015 11:42:27.857 ERROR [http-nio-8080-exec-2] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions Communications link failure
The last packet successfully received from the server was 200,187 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
Any suggestions on what to dig into?
You should use a connection pool. For Hibernate, you can use c3p0. In your hibernate properties set the following
hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider
Then, in a c3p0.properties file, put these properties to retry to reconnect indefinitely every 3 seconds when database is down:
c3p0.acquireRetryAttempts = 0
c3p0.acquireRetryDelay = 3000
c3p0.breakAfterAcquireFailure = false
See this section for more details on how to recover from a database outage.