Connection Timeout with MySQL Database

Troubleshooting Databases

このページの内容

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

症状

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:

  1. 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. 
    In the STASH_HOME/shared/stash-config.properties file, add the following line and define the number of minutes for the connection timeout:

    db.pool.idle.testInterval=1
  2. If the MySQL wait_timeout is less than a minute, add the interactiveClient=true into the JDBC URL in stash-config.properties file to use MySQL interactive_timeout default value 28800 seconds, eg:

    jdbc.url=jdbc:mysql://localhost:3306/stash212?autoReconnect=true&characterEncoding=utf8&useUnicode=true&sessionVariables=storage_engine%3DInnoDB&interactiveClient=true

     

Note that MySQL retains several scopes for the wait_timeout and interactive_timeout variable. Double check the same setting of the variable by running the following queries on the database:

  1. SHOW VARIABLES
  2. SHOW SESSION VARIABLES
  3. SHOW GLOBAL VARIABLES
最終更新日 2016 年 3 月 30 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.