Operations in Jira server fail with communications link failure in logs with MySQL
- Deleting a user or other operations in Jira causes the MySQL daemon to crash.
- Failed to connect Jira to MySQL database.
- This error can also occur in the logs anytime Jira attempts to use a connection that has already been severed from the database side
The following appears in the
com.mysql.jdbc.exceptions.jdbc.CommunicationsException: Communications link failure
Connection failed: Could not connect to the DB: Communications link failure
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT DISTINCT ID FROM rundetails WHERE JOB_ID=? (Communications link failure The last packet successfully received from the server was 532,227 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.) at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:641) at org.ofbiz.core.entity.GenericDAO.createEntityListIterator(GenericDAO.java:967) at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:883) at org.ofbiz.core.entity.GenericDelegator.findListIteratorByCondition(GenericDelegator.java:1237) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:398) ... 16 more Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet successfully received from the server was 532,227 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
The issue could be observed with the conditions below:
- The validationQuery is not set or the network is encountering problems.
- MySQL connector/J version 5.1.36, when connecting to MySQL server 5.0.x. This is tracked at http://bugs.mysql.com/bug.php?id=77665.
Knowledge Base article - Unknown system variable 'language' on Database Setup page when using MySQL
5.1.36 (driver) has no issues with 5.1.76, 5.5.46, 5.6.27 and 5.7.8
5.1.35 (driver) works with 5.0.96, 5.1.76, 5.5.46, 5.6.27 and 5.7.8
MySQL database version beyond 5.1.36. MySQL versions 5.1.32 - 5.1.36 contains a bug: http://bugs.mysql.com/bug.php?id=45357.
- This problem is caused due to MySQL server timing-out database connections faster than the Jira connection pool can notice. This exhausts the Jira connection pool as it will keep its connections open while the MySQL side of the connections is already closed. This usually happens because the MySQL wait_timeout and/or interactive_timeout variables have been greatly reduced from their default of 28800 seconds (8 hours).
MySQL evictor mechanism is not clearing stale connections. By default it will check 3 connections every time the evictor runs:
// The number of objects (connections) to examine during each run of the idle object evictor thread (if any). <num-tests-per-eviction-run>3</num-tests-per-eviction-run>
- Make sure to set the validation query— for details see:
- Validate network connectivity
Cause 2 & 3:
Upgrade MySQL database version beyond 5.1.36.
- Go into your $JIRAHOME/dbconfig.xml and look to see what value you have for the
<time-between-eviction-runs-millis>parameter. The default is 300000 milliseconds (or 5 minutes) as per Connecting Jira to MySQL.
From your MySQL instance, run the following select queries to determine the wait_timeout and interactive_timeout values of your system. For MySQL it could be set in any one of 3 locations:
SHOW VARIABLES SHOW SESSION VARIABLES SHOW GLOBAL VARIABLES
- If any of these select queries return a value less than the
<time-between-eviction-runs-millis>parameter in the dbconfig.xml, which is 300 (seconds), for any of the wait_timeout and/or interactive_timeout variables, then the MySQL variable value should be increased as a means to prevent these errors. In order to this you may need to stop Jira, then make these changes to MySQL, at which point you can then start Jira again.
- Alternatively, it might be possible in some cases to modify the dbconfig.xml file to reduce the time between evictions, however, if Jira is using its default value here, then it is not usually recommended to reduce the interval between evictions because this can put a higher overhead on both Jira and the database instance to perform this action more and more often. Please note that any changes made to the dbconfig.xml will need to be saved and Jira has to be restarted before these changes can take effect.
- Go into your $JIRAHOME/dbconfig.xml and add the following parameter
<num-tests-per-eviction-run> = pool-max-size
- On MySQL DB server side set wait_timeout to be >= min-evictable-idle-time-millis + time-between-eviction-runs-millis