clojure/java.jdbc: MySQL problem "NoRouteToHostException Cannot assign requested address"

[Raju Bitter]

Hi all,

I'm running into a problem with the clojure/java.jdbc wrapper. In my
program, I'm reading out a large text file and inserting around 24000
items into one table. After around 18000 items I get the following
error message:
NoRouteToHostException Cannot assign requested address
java.net.PlainSocketImpl.socketConnect (PlainSocketImpl.java:-2)

When I use c3p0 for connection pooling, I don't see the exception.
http://clojure.github.com/java.jdbc/doc/clojure/java/jdbc/ConnectionPooling.html

Seems to be a problem with socket connections failing to localhost,
where both the client and MySQL server are running. Has anyone else
experienced the same problem in the past?

OS: Mint Linux 3.0.0-12-generic #20-Ubuntu SMP Fri Oct 7 14:56:25 UTC
2011 x86_64 x86_64 x86_64 GNU/Linux
MySQL: 5.1.63-0ubuntu0.11.10.1
MySQL connector JAR: 5.1.6
Clojure: 1.3.0
Java: java version "1.6.0_31" Java(TM) SE Runtime Environment (build
1.6.0_31-b04)

Thanks,
Raju

[Sean Corfield]

Since you don't see the problem with connection pooling in play, I
suspect your code is structured so that it is holding connections open
over the inserts. Without connection pooling, a new connection is
created for each DB operation.

It's also possible you've found a bug in c.j.jdbc that is failing to
close connections in some situations (which would be more noticeable
without connection pooling).

Perhaps you can share your code so we can all take a look?

Sean

[Raju Bitter]

Thanks, Sean, here's simplified test which shows the behavior:

Project dependencies:
:dependencies [[org.clojure/clojure "1.3.0"]
[org.clojure/java.jdbc "0.2.2"]
[mysql/mysql-connector-java "5.1.6"]

Code:
(ns jdbc-test.core
(:require [clojure.java.jdbc :as sql])
(:import (java.text SimpleDateFormat)
(java.util Date)))

;; MySQL connection configuration
(def mysql-db
{:classname "com.mysql.jdbc.Driver"
:subprotocol "mysql"
:user "root"
:password "root"
:subname "//localhost/clojurejdbc"})

;; Table definition

(defn add-row-to-table
[label datetime]
(sql/with-connection mysql-db
(sql/insert-records :inserttest
{:id 0 :label label :created datetime})))

(defn generate-entries
[no]
(let [date-formatter (SimpleDateFormat. "yyyy-MM-dd HH:mm:ss")]
(loop [counter 1
datetime (.format date-formatter (Date.))]
(if (= 0 (mod counter 1000))
(println (str counter " rows added")))
(add-row-to-table (str "Line #" counter) datetime)
(if (< counter no)
(recur (inc counter) datetime)))))

The MySQL database and table definition:
CREATE DATABASE clojurejdbc;

USE clojurejdbc;

CREATE TABLE inserttest (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
label CHAR(255),
created DATETIME
);

I call the function generate-entries like this:
(generate-entries 1000000)

On my machine, the program stops after 34000+ rows have been added to the table:
...
32000 rows added
33000 rows added
34000 rows added

NoRouteToHostException Cannot assign requested address
java.net.PlainSocketImpl.socketConnect (PlainSocketImpl.java:-2)

The problem is probably the high number of connections kept open to
the localhost port, but that's just a guess:
http://stackoverflow.com/questions/1572215/how-to-avoid-a-noroutetohostexception

"netstat -a" shows a lot of TIME_WAIT entries

tcp 0 0 localhost:mysql *:* LISTEN
tcp 0 0 localhost:mysql localhost:46850 TIME_WAIT
tcp 0 0 localhost:mysql localhost:46737 TIME_WAIT
tcp 0 0 localhost:mysql localhost:46340 TIME_WAIT
(long list continued) ....

So this is not Clojure/JDBC specific, but a general problem with Java
and MySQL executing many queries to localhost. I'll investigate more
later.

Thanks,
Raju

[Raju Bitter]

Ok, I found the problem. It's not related to the Clojure JDBC API, but
to the number of ports opened on localhost in a very short period of
time. Linux has a TIME_WAIT configuration for connections opened, and
default TIME_WAIT period is set to 60s. The maximum number of open
connections is limited on a system, and if within a minute to many
connections are made to a machine, the TIME_WAIT setting can block any
new connections to the system.

I could work around this problem by setting
net.ipv4.tcp_tw_recycle=1
in /etc/sysctl.conf, but that setting might cause TCP problems as I've read.

With that setting, inserting 100.000 items into the table just ran
through without any problems/exceptions. Here's a blog post describing
some performance tuning settings for Ubuntu, for anyone running into a
similar problem:
http://samiux.blogspot.de/2011/04/howto-performance-tuning-on-ubuntu.html

Or just use c3p0 with clojure.java.jdbc.

- Raju