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

4 thoughts on “When localhost is not the same as 127.0.0.1 a MySQL Connection Gotcha

  1. Woah! I’m really digging the template/theme of this blog.
    It’s simple, yet effective. A lot of times it’s very hard to get that
    “perfect balance” between superb usability
    and visual appearance. I must say that you’ve done a fantastic job with this.
    In addition, the blog loads very fast for me on Opera. Exceptional Blog!

  2. Hey would you mind sharing which blog platform you’re using?
    I’m looking to start my own blog soon but I’m having a difficult time making a decision between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design and style seems different then most blogs and I’m looking for
    something unique. P.S Apologies for getting off-topic but I had
    to ask!

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