Re: 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.10at 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

2008/2/22, Agustin CS <agustincs1(at)gmail(dot)com>:
>
>
> 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.
>
>
>
> Here is the debug output that we get when running the SELECT statement
> from the first post.
> JDBC driver used: 8.3-603 JDBC3
> Java Virtual Machine used: sun-java-1.5.0.11
> Platform used: Kubuntu 7.04
>
> ------------------------
>
> 17:02:29.152 (1) PostgreSQL 8.3 JDBC3 with SSL (build 603)
> 17:02:29.167 (1) Trying to establish a protocol version 3 connection to
> XXX.XXX.XXX.XXX:5432
> 17:02:29.281 (1) FE=> StartupPacket(user=userdb,
> database=database_example, client_encoding=UNICODE, DateStyle=ISO,
> extra_float_digits=2)
> 17:02:29.384 (1) <=BE AuthenticationReqPassword
> 17:02:29.386 (1) FE=> Password(password=<not shown>)
> 17:02:29.489 (1) <=BE AuthenticationOk
> 17:02:29.524 (1) <=BE ParameterStatus(client_encoding = UNICODE)
> 17:02:29.525 (1) <=BE ParameterStatus(DateStyle = ISO, DMY)
> 17:02:29.525 (1) <=BE ParameterStatus(integer_datetimes = on)
> 17:02:29.525 (1) <=BE ParameterStatus(is_superuser = off)
> 17:02:29.525 (1) <=BE ParameterStatus(server_encoding = SQL_ASCII)
> 17:02:29.525 (1) <=BE ParameterStatus(server_version = 8.2.6)
> 17:02:29.525 (1) <=BE ParameterStatus(session_authorization = userdb)
> 17:02:29.526 (1) <=BE ParameterStatus(standard_conforming_strings = off)
> 17:02:29.526 (1) <=BE ParameterStatus(TimeZone = localtime)
> 17:02:29.526 (1) <=BE BackendKeyData(pid=16418,ckey=140210510)
> 17:02:29.526 (1) <=BE ReadyForQuery(I)
> 17:02:29.526 (1) compatible = 8.3
> 17:02:29.528 (1) loglevel = 2
> 17:02:29.529 (1) prepare threshold = 5
> getConnection returning driver[className=org.postgresql.Driver,
> org(dot)postgresql(dot)Driver(at)c17164]
> 17:02:29.637 (1) simple execute, handler=
> org(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)1ca318a,
> maxRows=0, fetchSize=0, flags=17
> 17:02:29.641 (1) FE=> Parse(stmt=null,query="SELECT * FROM table1 ORDER
> BY field1",oids={})
> 17:02:29.643 (1) FE=> Bind(stmt=null,portal=null)
> 17:02:29.643 (1) FE=> Describe(portal=null)
> 17:02:29.644 (1) FE=> Execute(portal=null,limit=0)
> 17:02:29.645 (1) FE=> Sync
> 17:02:29.763 (1) <=BE ParseComplete [null]
> 17:02:29.765 (1) <=BE BindComplete [null]
> 17:02:29.767 (1) <=BE RowDescription(1)
> 17:02:29.769 (1) <=BE DataRow
> 17:02:29.769 (1) <=BE DataRow
> 17:02:29.769 (1) <=BE DataRow
> 17:02:29.769 (1) <=BE DataRow
>

>
> 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.

Exactly, the cause of the remote SELECT execution (through Internet) failure
was the MTU. After decrease the MTU value in Kubuntu, the remote SELECT
execution works fine.

Thanks Oliver