A Fix for MySQL JDBC Communications Link Failure

We're going to look at some factors and one fix for the following Exception that reads something like this:

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

The last packet successfully received from the server was 627,125 milliseconds ago. 
The last packet sent successfully to the server was 22 milliseconds ago.

Here's what the Exception looks like on the page.

The really bad thing about the Communications Link Failure Exception is that your site is now dead in the water.

Possible Problems

A few possible problems causing the Communications Exception may stem from bad code design. But let's assume your code logic isn't crap and something else is going on.

Site inactivity. No one is hitting your site so the connection pool loses contact with the MySQL Database Server.

Caching could be another issue. Your site may be getting hits, but your caching is so efficient that you don't have to hit the database for hours at a time.

Finally, perhaps your MySQL Server Settings need a tweak.

MySQL Server Setting Tweaks for $500, Alex!

We're going to go straight to MySQL Server Setting Tweaks. Let's look at that exception again. The last packet successfully received from the server was 627,125 milliseconds ago…

627,125 milliseconds ago, eh? What's 600,000 milliseconds in seconds? That's right 600.

Now let's look at some MySQL Server settings with the following SQL command:

show variables like '%timeout%';

We're interested in wait_timeout highlighted below. That is in seconds.


The Fix

We'll start by changing the wait_timeout property on our MySQL Server in some /etc/mysql/my.cnf file. This NixMash Post has helpful tips on updating MySQL Server Properties.

Since we're using Spring we can also take advantage of Spring Database Connection Validation when idle. Here are the settings we've added to our mysql.properties file.

Careful with your property names. For instance if we look at our logs we see we're using Tomcat for JDBC Pooling. So you may want to use spring.datasource.tomcat as your property prefix. But that wouldn't work and you'd have no idle connection validation work happening.

Which Properties Bonus

This issue opened up the vast depths of Spring Configuration Property Names. What they are, where to find them. Here's a bonus if you're using IntelliJ IDEA Ultimate 2017.1+. You can change the Search Scope for everywhere and you'll retrieve property usage from Spring Metaconfiguration and other Settings files.

One final word on properties: CamelCase on non-camel-case. A very cool tidbit is that Spring supports either. Both will work, in other words.