[pgpool-general: 1068] Re: JDBC, PGPOOL and unexpected EOF on client connection
Stelios Limnaios
stelios.limnaios at mekon.com
Wed Oct 3 19:13:25 JST 2012
Hi,
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 457471e0, 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
>>
>>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool [11-06-35].log
Type: application/octet-stream
Size: 79813 bytes
Desc: pgpool [11-06-35].log
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20121003/198614c8/attachment-0001.obj>
More information about the pgpool-general
mailing list