Connection Timeout with MySQL Database
When installing Stash or during the application usage, the following message pattern is reported in the log files:
2012-08-12 22:20:25,437 ERROR [main] com.jolbox.bonecp.ConnectionHandle Database access problem. Killing off all remaining connections in the connection pool. SQL State = 08S01 2012-08-12 22:20:25,456 ERROR [main] o.h.transaction.JDBCTransaction JDBC begin failed com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 18,058 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
This problem is caused due to MySQL server timing-out database connections faster than the Stash connection pool can notice. This exhausts the Stash connection pool as it will keep its connections open while the MySQL side of the connections is already closed.
Stash is shipped with a connection test interval default of 10 minutes.
MySQL has its wait_timeout variable default value set to 28800 seconds (8 hours).
Therefore, if both sides of the connection still keep the defaults, the problem will never happen, as MySQL will never timeout a connection before Stash does it.
If the MySQL wait_timeout variable had its value reduced:
In this case, in order to avoid having MySQL close the connections and Stash not notice, define the Stash test interval value to one that is smaller then the MySQL value.
STASH_HOME/shared/stash-config.propertiesfile, add the following line and define the number of minutes for the connection timeout:
If the MySQL wait_timeout is less than a minute, add the
interactiveClient=trueinto the JDBC URL in
stash-config.propertiesfile to use MySQL interactive_timeout default value 28800 seconds, eg:
Note that MySQL retains several scopes for the
interactive_timeout variable. Double check the same setting of the variable by running the following queries on the database:
- SHOW VARIABLES
- SHOW SESSION VARIABLES
- SHOW GLOBAL VARIABLES