[pgpool-general: 1072] Re: [SOLVED] JDBC, PGPOOL and unexpected EOF on client connection
Stelios Limnaios
stelios.limnaios at mekon.com
Thu Oct 4 18:42:18 JST 2012
Hi again,
It is much better now.
We downloaded the latest version from git and installed it (after doing a 'make uninstall').
JDBC connections look healthy now and they are not terminated.
Thank you for your help!
Stelios
-----Original Message-----
From: Tatsuo Ishii [mailto:ishii at postgresql.org]
Sent: 03 October 2012 23:45
To: Stelios Limnaios
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 1063] JDBC, PGPOOL and unexpected EOF on client connection
This time the log looks totally different. Before pgpool complained about non existence of a database. Now it's segfaults. Not sure why but we have somes fixes including segfault in some cases since 3.2.0 was out. I'm not sure if that's your case but you might want to try
3.2 stable tree.
http://git.postgresql.org/gitweb/?p=pgpool2.git;a=shortlog;h=refs/heads/V3_2_STABLE
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> Yes, we can connect to the 1rst backend:
> psql: we can connect to both -p 9999 and -p 5432, either by using localhost or the ip address.
> jdbc: we can connect to port 5432 but not to port 9999, no matter if we use localhost or ip address in the url.
>
> Our backend_* parameters are:
>
> backend_socket_dir = '/tmp'
> backend_hostname0 = 'xx.xxx.xx.89'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/pgsql/9.1/data'
> backend_flag0= 'ALLOW_TO_FAILOVER'
> backend_hostname1 = 'xx.xxx.xx.239'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/var/lib/pgsql/9.1/data'
> backend_flag1= 'ALLOW_TO_FAILOVER'
>
> Now, I did a clean out of some double entries in iptables (on both servers), and now the problem seems to be slightly different:
>
> 11:06:35.460 (1) PostgreSQL 9.1 JDBC4 (build 901)
> 11:06:35.465 (1) Trying to establish a protocol version 3 connection
> to localhost:9999
> 11:06:35.483 (1) FE=> StartupPacket(user=postgres, database=notus,
> client_encoding=UTF8, DateStyle=ISO, extra_float_digits=2)
> 11:06:35.505 (1) <=BE AuthenticationOk
> 11:06:35.520 (1) <=BE ParameterStatus(application_name = )
> 11:06:35.521 (1) <=BE ParameterStatus(client_encoding = UTF8)
> 11:06:35.521 (1) <=BE ParameterStatus(DateStyle = ISO, MDY)
> 11:06:35.521 (1) <=BE ParameterStatus(integer_datetimes = on)
> 11:06:35.521 (1) <=BE ParameterStatus(IntervalStyle = postgres)
> 11:06:35.521 (1) <=BE ParameterStatus(is_superuser = on)
> 11:06:35.521 (1) <=BE ParameterStatus(server_encoding = UTF8)
> 11:06:35.522 (1) <=BE ParameterStatus(server_version = 9.1.5)
> 11:06:35.522 (1) <=BE ParameterStatus(session_authorization =
> postgres)
> 11:06:35.522 (1) <=BE ParameterStatus(standard_conforming_strings =
> on)
> 11:06:35.522 (1) <=BE ParameterStatus(TimeZone = GB)
> 11:06:35.522 (1) <=BE BackendKeyData(pid=1517,ckey=1682657227)
> 11:06:35.522 (1) <=BE ReadyForQuery(I)
> 11:06:35.523 (1) simple execute,
> handler=org.postgresql.core.SetupQueryRunner$SimpleResultHandler at 45747
> 1e0, maxRows=0, fetchSize=0, flags=23
> 11:06:35.524 (1) FE=> Parse(stmt=null,query="SET extra_float_digits =
> 3",oids={})
> 11:06:35.525 (1) FE=> Bind(stmt=null,portal=null)
> 11:06:35.526 (1) FE=> Execute(portal=null,limit=1)
> 11:06:35.526 (1) FE=> Sync
> 11:06:35.541 (1) FE=> Terminate
> org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
> at org.postgresql.core.SetupQueryRunner.run(SetupQueryRunner.java:77)
> at org.postgresql.core.v3.ConnectionFactoryImpl.runInitialQueries(ConnectionFactoryImpl.java:526)
> at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:114)
> at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
> at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
> at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
> at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
> at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32)
> at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
> at org.postgresql.Driver.makeConnection(Driver.java:393)
> at org.postgresql.Driver.connect(Driver.java:267)
> at java.sql.DriverManager.getConnection(DriverManager.java:582)
> at java.sql.DriverManager.getConnection(DriverManager.java:185)
> at replication.Main.printVersion(Main.java:41)
> at replication.Main.main(Main.java:23)
> SQLException: SQLState(08006)
>
> I've attached the new pgpool log for this.
> The error occurred at 11:06:35.
>
>
> Regards,
> Stelios
>
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at postgresql.org]
> Sent: 03 October 2012 08:49
> To: Stelios Limnaios
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 1063] JDBC, PGPOOL and unexpected EOF on
> client connection
>
> Can you connect to 1 th backend by using jdbc or psql?
> What are backend_* parameters in pgpool.conf?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
>
>> Hi,
>>
>> Please find attached the PGPool log file.
>> The error occurred at 07:58:22.
>>
>> I think this message:
>> 2012-10-03 07:58:22 LOG: pid 2114: pool_read_kind: error message from 1 th backend:database "notus" does not exist
>> is misleading, as the database 'notus' does exist. We can connect to it using the Postgres port (5432) in the jdbc url.
>>
>> Again thank you all for your quick responses.
>>
>> Regards,
>> Stelios
>>
>> -----Original Message-----
>> From: Tatsuo Ishii [mailto:ishii at postgresql.org]
>> Sent: 03 October 2012 00:06
>> To: Stelios Limnaios
>> Cc: pgpool-general at pgpool.net
>> Subject: Re: [pgpool-general: 1063] JDBC, PGPOOL and unexpected EOF
>> on client connection
>>
>> Can you show us pgpool log?
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>>> I kindly need your help and expertise on a strange situation we're facing when using PGPool II.
>>>
>>> Our setup is 2 Centos 5.3 servers with installed Postgres 9.1 (on port 5432) and PGPool II 3.2.0.0 (on port 9999).
>>> What we want to do, is to establish replication between the 2 servers, with one to serve as the main server and the other as the standby.
>>> To achieve that, we followed the instructions in this tutorial:
>>> http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html
>>> and we managed to successfully establish replication.
>>> When we run (as postgres)
>>> createddb -p 9999 dbname
>>> on the main server then we can see the changes on the standby server.
>>> So far, so good.
>>>
>>> The problem we've got is when Java come into play.
>>> For some reason Java applications are not able establish a connection to PGPool but are able to do it to the Postgres server.
>>> For example, connecting to jdbc:postgresql://localhost:9999/dbname and running SELECT VERSION() throws an error.
>>> But, if we connect to jdbc:postgresql://localhost:5432/dbname we get back some results.
>>>
>>> The error in Postgres log is:
>>> LOG: unexpected EOF on client connection
>>>
>>> This is strange, as createdb -p 9999 connects to PGPool with no problem.
>>>
>>> So, we set loglevel=2 to the jdbc url in order to get some more advanced network messages and we got:
>>> PostgreSQL 9.1 JDBC4 (build 901)
>>> Trying to establish a protocol version 3 connection to
>>> localhost:9999 FE=> StartupPacket(user=postgres, database=dbname,
>>> client_encoding=UTF8, DateStyle=ISO, extra_float_digits=2) <=BE
>>> AuthenticationOk
>>> org.postgresql.util.PSQLException: The connection attempt failed.
>>> at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:152)
>>> at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
>>> at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
>>> at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
>>> at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
>>> at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32)
>>> at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
>>> at org.postgresql.Driver.makeConnection(Driver.java:393)
>>> at org.postgresql.Driver.connect(Driver.java:267)
>>> at java.sql.DriverManager.getConnection(DriverManager.java:582)
>>> at java.sql.DriverManager.getConnection(DriverManager.java:185)
>>> at replication.Main.printVersion(Main.java:41)
>>> at replication.Main.main(Main.java:23)
>>> SQLException: SQLState(08001)
>>> getConnection failed: org.postgresql.util.PSQLException: The connection attempt failed.
>>>
>>> We looked for SQLState (08001) and for Postgres it means that 'sql client unable to establish connection'.
>>> And this is where we ran out of ideas.
>>>
>>> So, to summarize, we're in a situation where replication has been established successfully on port 9999.
>>> Java (jdbc) can connect with success to Postgres (5432) but not to PGPool (9999), as the connection gets terminated for some reason right after AuthenticationOK is sent to the client.
>>> (I don't know if that is important to mention but we have opened
>>> ports 5432, 9999 and 9898 for tcp connections on both server
>>> firewalls)
>>>
>>> Do you have any ideas what is causing this problem?
>>> Any help and directions will be much appreciated.
>>>
>>>
>>> Kind regards,
>>> Stelios
>>>
>>>
More information about the pgpool-general
mailing list