Problem

1. Deleting a user or other operations in Jira causes the MySQL daemon to crash.
2. Failed to connect Jira to MySQL database.
3. 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 atlassian-jira.log:

com.mysql.jdbc.exceptions.jdbc.CommunicationsException: Communications link failure

OR

Connection failed: Could not connect to the DB: Communications link failure

OR

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. 

Causes

The issue could be observed with the conditions below:

1. The validationQuery is not set or the network is encountering problems.
2. 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.

   1. 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

3. 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.
   

4. 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).
   

5. 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>

Resolution

Cause 1:

1. Make sure to set the validation query — for details see:
   
   • Surviving Connection Closures (in JIRA 4.3.x and earlier) or
   • Surviving Connection Closures (in JIRA 4.4.x and later).
2. Validate network connectivity

Cause 2 & 3:

1. Upgrade MySQL database version beyond 5.1.36.

Cause 4:

1. 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.

2. 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
   
   

3. 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.
4. 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.

Cause 5:

1. Go into your $JIRA-HOME/dbconfig.xml file and add the following parameter: <num-tests-per-eviction-run> = pool-max-size
2. On MySQL DB server side (my. cnf file) set wait_timeout to be >= min-evictable-idle-time-millis + time-between-eviction-runs-millis