I've got a Java Spring based web service in front of a mysql database.
I'm using all the mysql default settings so
wait_timeout=28800
The problem is my web service is constantly up and needs to have a working db connection at all times.
After 8 hours, if the service tries to access the db I get the following:
The last packet successfully received from the server was 59,626,614 milliseconds ago. The last packet sent successfully to the server was 59,626,614 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 59,626,614 milliseconds ago. The last packet sent successfully to the server was 59,626,614 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
The solution I see most recommended is to change wait_timeout to an arbitrarily huge number which seems wrong.
For all I know some internal part of mysql needs some kind of a timeout.
Additional research suggested adding
autoReconnect=true
to my connection string. This seemed reasonable until I dug this up:
You should code for network glitches and deal with auto-reconnect yourself.
auto-reconnect is deliberately OFF because of several 'application' bugs that can silently happen when the connection goes away and comes back in a different state.
Anyway, a comment shows that this is somewhat a Duplicate question.
from MySql JDBC timeout even with 'autoReconnect=true'
So, what is the safe and responsible way of ensuring I don't get the timeout I've been getting?
