When localhost is not the same as 127.0.0.1 a MySQL Connection Gotcha

Setting up MySQL connection pooling has been standard stuff in server-side apps for as long as I’ve been writing them, so I was at a loss when, every 5 days or so, my newly deployed app died with this exception:

Exception in thread "main" 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
Communications link failure

Last packet sent to the server was 0 ms ago.

I’d already set up the driver with what I understood to be the correct reconnection properties – autoReconnectForPools=true and a validation query – in my Grails app:

dataSource {
    dbCreate = "update"
    driverClassName = "com.mysql.jdbc.Driver"
    username = "lourishbeta"
    password = "lourishbeta"
    url = "jdbc:mysql://${System.getProperty("dbHostName",
            "localhost")}/beta?autoReconnectForPools=true"
    properties {
        validationQuery = "select 1"
        maxActive = 150
    }
}

I initially thought it must be something to do with the (generally very reliable) CloudFoundry dropping the connection, but eventually I came upon this enormous thread on the MySQL forums which, in a very, very roundabout way gave the solution:

Use 127.0.0.1 instead of localhost.

So now my datasource looks like this:

dataSource {
    dbCreate = "update"
    driverClassName = "com.mysql.jdbc.Driver"
    username = "lourishbeta"
    password = "lourishbeta"
    url = "jdbc:mysql://${System.getProperty("dbHostName",
            "127.0.0.1")}/beta?autoReconnectForPools=true"
    properties {
        validationQuery = "select 1"
        maxActive = 150
    }
}

And the MySQL connection has been up for over 2 weeks. Phew!

About these ads

One thought on “When localhost is not the same as 127.0.0.1 a MySQL Connection Gotcha

  1. Thanks a lot for this thread, man. Even I’ve been facing the same problem, except that when I changed the localhost in server.xml of tomcat to the IP of the system (not 127.0.0.1), it stopped working.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s