Обсуждение: Problem executing remote SELECT's (through internet) with JDBC

Hello all,

I have a problem invocating SELECT statements using Java JDBC driver having a remote communication between PostgreSQL database client-server through Internet.
If I run a database client (a java application using JDBC3 or JDBC4 driver) and launch a simple SELECT statement against remote server (through Internet), the 'executeQuery' method never ends. This problem only occurs if the amount of data retrieved by the SELECT statement exceeds a certain value, the communication between client and server is through Internet and the client app is executed on linux (tested on Kubuntu 7.04 and 7.10). The problem does NOT occur if the communication is through loopback interface or local network. In addition if i execute the same client app on Windows (tested on Windows XP), whatever the communication may be (local, local network or internet) it works fine.

Since the problem doesn't appear on windows i think the problem may be related to the interaction between the JDBC Driver and the linux Java Virtual Machine. So I have monitored the communications using "Wireshark Network Analizer". After analizing the exchanged PGSQL (contains SELECT statement data) and TCP (ACK messages) messages between client and server it looks like the client closes the communication after a concrete number of PGSQL and TCP messages with the database server, so the server detects that the client doesn't acknowledge to PGSQL messages and retries several more times (~8 times) without response from the client.

--------------------

if someone wants reproduce the problem only has to follow this steps. Create a PostgreSQL database with the following table

CREATE TABLE TABLE1 (
  field1 integer NOT NULL,
  CONSTRAINT pk_table1 PRIMARY KEY (field1)
)

I have used the PostgreSQL database server (v8.2) executed in Kubuntu 7.10 at the server side.

Then create a Java application which send a simple SELECT statement. For example

try {
    Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

try {
    conn = DriverManager.getConnection("jdbc:postgresql://AAA.BBB.CCC.DDD:5432/","user","password");
} catch (SQLException e) {
    e.printStackTrace();
}    

Statement st = null;
try {
    st = conn.createStatement();

    ResultSet rs = st.executeQuery("SELECT * FROM TABLE1");

    rs.close();
    st.close();
} catch (SQLException e) {
    e.printStackTrace();
}

I have executed the client on Kubuntu 7.04 and 7.10 using all JDBC3 (JVM 1.5) and JDBC4 (JVM 1.6) drivers for testing.

Once you have installed the database server in any REMOTE PC, fill TABLE1 with 1012 rows (or more) and execute the Java client. The client should be waiting indefinitely when running executeQuery("SELECT * FROM TABLE1"); On the other hand, if the table contains 1011 rows or less the Java app works fine (at least for me).

--------------------

Tip:

After testing successfully the client on windows i don't think that's a firewall problem on the server side. Besides when making tests on linux i've even tried to disable the firewall (at the client), so i also don't think this is failing. Anyway the connection is established, and some packets are exchanged.

Also remember that it works fine even from a linux client when having both client and server on the same network (a lan, for example).


Thanks in advance

Agustin CS wrote:
> Hello all,
>
> I have a problem invocating SELECT statements using Java JDBC driver
> having a remote communication between PostgreSQL database client-server
> through Internet.

You may want to re-run with logLevel=2 as a connection parameter; then
the driver will log all the network I/O it is doing.

Without a detailed TCP trace I can't be sure but it sounds like perhaps
you have a path MTU discovery problem between the server and client, or
something similar. I suspect that when you turn on driver debugging, you
will see the driver just stop receiving new data from the network at
some point.

(if you like, mail me a tcpdump or wireshark packet capture off-list -
ideally one capture from each end of the connection - and I'll take a look)

-O

I found the source of the problem... The database server is behind a router and any configuration parameter in that router close the
communication between client and server.

Thanks