php/postgres persistent connections fail - Mailing list pgsql-admin

From Palle Girgensohn
Subject php/postgres persistent connections fail
Date
Msg-id 3985C0D8.602224BC@partitur.se
Whole thread Raw
List pgsql-admin
Hi!

I am experiencing a very strange error usign this combination:

FreeBSD 4.0-STABLE
postgres 7.0.2
apache 1.3.12
php 4.0.1p2

Here's the problem:

PHP's pg_pconnect (persistent connect) will not keep the
connection open. What happens is, that after executing the page
just fine, upon pg_close (I guess), something happens and I get
this (when running postgres with -d 2):

ProcessQuery
CommitTransactionCommand
pq_recvbuf: unexpected EOF on client connection
proc_exit(0)
shmem_exit(0)
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 32628 exited
with status 0
...

What happens here is, that the connection gets broken. Hence,
next time I try to connect to the same "persistent" connection
(i.e. next time i hit the same apache process from the process
pool) it fails, spitting this to the apache error log:

[Mon Jul 31 19:24:26 2000] [error] PHP Warning:  PostgreSQL
query failed:  pqReadData() --  read() failed: errno=9
Bad file descriptor
 in /usr/local/www/www.sssf.se/data/internt/matrikel/foo.php on
line 20


What is happening here? I have ran tcpdump to try to see what
is going on between apache and postgres. I've attached two
dumps, one on the failing system, which will lead to the
"pq_recvbuf: unexpected EOF on client connection", and antoher
dump, doing exactly the same thing on another machine. The
machines both have php 4.0.1p2 and apache 1.3.12. Both run
FreeBSD. The failing machine has a slightly newer version of
the FreeBSD system, but both are 4.0-STABLE.

uname -a:

FreeBSD bastuba.partitur.se 4.0-STABLE FreeBSD 4.0-STABLE #0:
Sat Jul  1 15:56:11 CEST 2000
girgen@tuba.partitur.se:/usr/local/obj/usr/src/sys/TUBA  i386

FreeBSD elbas.partitur.se 4.0-STABLE FreeBSD 4.0-STABLE #0: Wed
Jun 14 20:55:20 CEST 2000
girgen@tb303.partitur.se:/usr/obj/usr/src/sys/WORKSTATION  i386

The two files attached show tcpdumps of the communication
between apache(php) and postgres. The failing machine is
bastuba while elbas is OK. Same php program, of course.

This might well be a configuration error. The bastuba is runs
around 50 virtual hosts (but not many use php), elbas is a
simple workstation running only a test setup. The problem is
finding the config error, in one exists, since I can't really
tamper too much with the bastuba setup, and I can't reporduce
the error elsewhere.

php.ini files are identical, as are phpinfo() outputs.

I'm out of ideas.

Best regards,
Palle Girgensohn
Partitur$ tcpdump -s 1532 -Xx -i lo0 tcp port 5432
...
19:24:27.626723 localhost.4750 > localhost.5432: S 5242155:5242155(0) win 16384 <mss 16344,nop,wscale
0,nop,nop,timestamp249858575 0> (DF) 
0x0000   4500 003c 3c53 4000 4006 0067 7f00 0001        E..<<S@.@..g....
0x0010   7f00 0001 128e 1538 004f fd2b 0000 0000        .......8.O.+....
0x0020   a002 4000 14ad 0000 0204 3fd8 0103 0300        ..@.......?.....
0x0030   0101 080a 0ee4 8a0f 0000 0000                  ............
19:24:27.626834 localhost.5432 > localhost.4750: S 5323579:5323579(0) ack 5242156 win 57344 <mss 16344,nop,wscale
0,nop,nop,timestamp249858575 249858575> (DF) 
0x0000   4500 003c 3c54 4000 4006 0066 7f00 0001        E..<<T@.@..f....
0x0010   7f00 0001 1538 128e 0051 3b3b 004f fd2c        .....8...Q;;.O.,
0x0020   a012 e000 a01b 0000 0204 3fd8 0103 0300        ..........?.....
0x0030   0101 080a 0ee4 8a0f 0ee4 8a0f                  ............
19:24:27.626881 localhost.4750 > localhost.5432: . ack 1 win 57344 <nop,nop,timestamp 249858575 249858575> (DF)
0x0000   4500 0034 3c55 4000 4006 006d 7f00 0001        E..4<U@.@..m....
0x0010   7f00 0001 128e 1538 004f fd2c 0051 3b3c        .......8.O.,.Q;<
0x0020   8010 e000 0604 0000 0101 080a 0ee4 8a0f        ................
0x0030   0ee4 8a0f                                      ....
19:24:27.627285 localhost.4750 > localhost.5432: P 1:297(296) ack 1 win 57344 <nop,nop,timestamp 249858575 249858575>
(DF)
0x0000   4500 015c 3c56 4000 4006 ff43 7f00 0001        E..\<V@.@..C....
0x0010   7f00 0001 128e 1538 004f fd2c 0051 3b3c        .......8.O.,.Q;<
0x0020   8018 e000 e777 0000 0101 080a 0ee4 8a0f        .....w..........
0x0030   0ee4 8a0f 0000 0128 0002 0000 7373 7366        .......(....sssf
0x0040   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0050   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0060   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0070   0000 0000 0000 0000 0000 0000 6e6f 626f        ............nobo
0x0080   6479 0000 0000 0000 0000 0000 0000 0000        dy..............
0x0090   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00a0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00b0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00c0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00d0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00e0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00f0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0100   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0110   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0120   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0130   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0140   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0150   0000 0000 0000 0000 0000 0000                  ............
19:24:27.628571 localhost.5432 > localhost.4750: P 1:6(5) ack 297 win 57344 <nop,nop,timestamp 249858576 249858575>
(DF)
0x0000   4500 0039 3c57 4000 4006 0066 7f00 0001        E..9<W@.@..f....
0x0010   7f00 0001 1538 128e 0051 3b3c 004f fe54        .....8...Q;<.O.T
0x0020   8018 e000 b2cd 0000 0101 080a 0ee4 8a10        ................
0x0030   0ee4 8a0f 5200 0000 00                         ....R....
19:24:27.666312 localhost.5432 > localhost.4750: P 6:16(10) ack 297 win 57344 <nop,nop,timestamp 249858579 249858575>
(DF)
0x0000   4500 003e 3c58 4000 4006 0060 7f00 0001        E..><X@.@..`....
0x0010   7f00 0001 1538 128e 0051 3b41 004f fe54        .....8...Q;A.O.T
0x0020   8018 e000 f961 0000 0101 080a 0ee4 8a13        .....a..........
0x0030   0ee4 8a0f 4b00 0089 90dc 949e 9a5a             ....K........Z
19:24:27.666651 localhost.4750 > localhost.5432: P 297:327(30) ack 16 win 57344 <nop,nop,timestamp 249858579 249858576>
(DF)
0x0000   4500 0052 3c59 4000 4006 004b 7f00 0001        E..R<Y@.@..K....
0x0010   7f00 0001 128e 1538 004f fe54 0051 3b4b        .......8.O.T.Q;K
0x0020   8018 e000 3766 0000 0101 080a 0ee4 8a13        ....7f..........
0x0030   0ee4 8a10 5173 656c 6563 7420 6765 7464        ....Qselect.getd
0x0040   6174 6162 6173 6565 6e63 6f64 696e 6728        atabaseencoding(
0x0050   2900                                           ).
19:24:27.675513 localhost.5432 > localhost.4750: P 16:77(61) ack 327 win 57344 <nop,nop,timestamp 249858580 249858579>
(DF)
0x0000   4500 0071 3c5a 4000 4006 002b 7f00 0001        E..q<Z@.@..+....
0x0010   7f00 0001 1538 128e 0051 3b4b 004f fe72        .....8...Q;K.O.r
0x0020   8018 e000 15d0 0000 0101 080a 0ee4 8a14        ................
0x0030   0ee4 8a13 5062 6c61 6e6b 0054 0001 6765        ....Pblank.T..ge
0x0040   7464 6174 6162 6173 6565 6e63 6f64 696e        tdatabaseencodin
0x0050   6700 0000 0013 0020 ffff ffff 4480 0000        g...........D...
0x0060   000a 4c41 5449 4e31 4353 454c 4543 5400        ..LATIN1CSELECT.
0x0070   5a                                             Z
19:24:27.746310 localhost.4750 > localhost.5432: P 327:386(59) ack 77 win 57344 <nop,nop,timestamp 249858587 249858580>
(DF)
0x0000   4500 006f 3c70 4000 4006 0017 7f00 0001        E..o<p@.@.......
0x0010   7f00 0001 128e 1538 004f fe72 0051 3b88        .......8.O.r.Q;.
0x0020   8018 e000 3711 0000 0101 080a 0ee4 8a1b        ....7...........
0x0030   0ee4 8a14 5153 454c 4543 5420 656e 616d        ....QSELECT.enam
0x0040   6e20 6672 6f6d 206d 6174 7269 6b65 6c20        n.from.matrikel.
0x0050   7768 6572 6520 7573 6572 6964 3d27 5061        where.userid='Pa
0x0060   756c 2047 6972 6765 6e73 6f68 6e27 00          ul.Girgensohn'.
19:24:27.768812 localhost.5432 > localhost.4750: P 77:128(51) ack 386 win 57344 <nop,nop,timestamp 249858590 249858587>
(DF)
0x0000   4500 0067 3c7a 4000 4006 0015 7f00 0001        E..g<z@.@.......
0x0010   7f00 0001 1538 128e 0051 3b88 004f fead        .....8...Q;..O..
0x0020   8018 e000 e7d8 0000 0101 080a 0ee4 8a1e        ................
0x0030   0ee4 8a1b 5062 6c61 6e6b 0054 0001 656e        ....Pblank.T..en
0x0040   616d 6e00 0000 0019 ffff ffff ffff 4480        amn...........D.
0x0050   0000 000e 4769 7267 656e 736f 686e 4353        ....GirgensohnCS
0x0060   454c 4543 5400 5a                              ELECT.Z
19:24:27.769410 localhost.4750 > localhost.5432: F 386:386(0) ack 128 win 57344 <nop,nop,timestamp 249858590 249858590>
(DF)
0x0000   4500 0034 3c7b 4000 4006 0047 7f00 0001        E..4<{@.@..G....
0x0010   7f00 0001 128e 1538 004f fead 0051 3bbb        .......8.O...Q;.
0x0020   8011 e000 03e5 0000 0101 080a 0ee4 8a1e        ................
0x0030   0ee4 8a1e                                      ....
19:24:27.769462 localhost.5432 > localhost.4750: . ack 387 win 57344 <nop,nop,timestamp 249858590 249858590> (DF)
0x0000   4500 0034 3c7c 4000 4006 0046 7f00 0001        E..4<|@.@..F....
0x0010   7f00 0001 1538 128e 0051 3bbb 004f feae        .....8...Q;..O..
0x0020   8010 e000 03e5 0000 0101 080a 0ee4 8a1e        ................
0x0030   0ee4 8a1e                                      ....
19:24:27.769691 localhost.5432 > localhost.4750: F 128:128(0) ack 387 win 57344 <nop,nop,timestamp 249858590 249858590>
(DF)
0x0000   4500 0034 3c7d 4000 4006 0045 7f00 0001        E..4<}@.@..E....
0x0010   7f00 0001 1538 128e 0051 3bbb 004f feae        .....8...Q;..O..
0x0020   8011 e000 03e4 0000 0101 080a 0ee4 8a1e        ................
0x0030   0ee4 8a1e                                      ....
19:24:27.769746 localhost.4750 > localhost.5432: . ack 129 win 57344 <nop,nop,timestamp 249858590 249858590> (DF)
0x0000   4500 0034 3c7e 4000 4006 0044 7f00 0001        E..4<~@.@..D....
0x0010   7f00 0001 128e 1538 004f feae 0051 3bbc        .......8.O...Q;.
0x0020   8010 e000 03e4 0000 0101 080a 0ee4 8a1e        ................
0x0030   0ee4 8a1e                                      ....
$ tcpdump -s 1532 -Xx -i lo0 tcp port 5432
...
19:23:06.691601 localhost.partitur.se.1903 > localhost.partitur.se.5432: S 3689158218:3689158218(0) win 16384 <mss
16344>(DF) 
0x0000   4500 002c 659b 4000 4006 d72e 7f00 0001        E..,e.@.@.......
0x0010   7f00 0001 076f 1538 dbe4 164a 0000 0000        .....o.8...J....
0x0020   6002 4000 112a 0000 0204 3fd8                  `.@..*....?.
19:23:06.691660 localhost.partitur.se.5432 > localhost.partitur.se.1903: S 3689275855:3689275855(0) ack 3689158219 win
57344<mss 16344> (DF) 
0x0000   4500 002c 659c 4000 4006 d72d 7f00 0001        E..,e.@.@..-....
0x0010   7f00 0001 1538 076f dbe5 e1cf dbe4 164b        .....8.o.......K
0x0020   6012 e000 b362 0000 0204 3fd8                  `....b....?.
19:23:06.691687 localhost.partitur.se.1903 > localhost.partitur.se.5432: . ack 1 win 57344 (DF)
0x0000   4500 0028 659d 4000 4006 d730 7f00 0001        E..(e.@.@..0....
0x0010   7f00 0001 076f 1538 dbe4 164b dbe5 e1d0        .....o.8...K....
0x0020   5010 e000 0544 0000                            P....D..
19:23:06.691961 localhost.partitur.se.1903 > localhost.partitur.se.5432: P 1:297(296) ack 1 win 57344 (DF)
0x0000   4500 0150 659e 4000 4006 d607 7f00 0001        E..Pe.@.@.......
0x0010   7f00 0001 076f 1538 dbe4 164b dbe5 e1d0        .....o.8...K....
0x0020   5018 e000 e6b7 0000 0000 0128 0002 0000        P..........(....
0x0030   7373 7366 0000 0000 0000 0000 0000 0000        sssf............
0x0040   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0050   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0060   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0070   6e6f 626f 6479 0000 0000 0000 0000 0000        nobody..........
0x0080   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0090   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00a0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00b0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00c0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00d0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00e0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x00f0   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0100   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0110   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0120   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0130   0000 0000 0000 0000 0000 0000 0000 0000        ................
0x0140   0000 0000 0000 0000 0000 0000 0000 0000        ................
19:23:06.693384 localhost.partitur.se.5432 > localhost.partitur.se.1903: P 1:6(5) ack 297 win 57344 (DF)
0x0000   4500 002d 659f 4000 4006 d729 7f00 0001        E..-e.@.@..)....
0x0010   7f00 0001 1538 076f dbe5 e1d0 dbe4 1773        .....8.o.......s
0x0020   5018 e000 b20e 0000 5200 0000 00               P.......R....
19:23:06.716696 localhost.partitur.se.5432 > localhost.partitur.se.1903: P 6:16(10) ack 297 win 57344 (DF)
0x0000   4500 0032 65a6 4000 4006 d71d 7f00 0001        E..2e.@.@.......
0x0010   7f00 0001 1538 076f dbe5 e1d5 dbe4 1773        .....8.o.......s
0x0020   5018 e000 29bc 0000 4b00 00f5 2d6c 4b8d        P...)...K...-lK.
0x0030   155a                                           .Z
19:23:06.716928 localhost.partitur.se.1903 > localhost.partitur.se.5432: P 297:327(30) ack 16 win 57344 (DF)
0x0000   4500 0046 65a7 4000 4006 d708 7f00 0001        E..Fe.@.@.......
0x0010   7f00 0001 076f 1538 dbe4 1773 dbe5 e1df        .....o.8...s....
0x0020   5018 e000 36ab 0000 5173 656c 6563 7420        P...6...Qselect.
0x0030   6765 7464 6174 6162 6173 6565 6e63 6f64        getdatabaseencod
0x0040   696e 6728 2900                                 ing().
19:23:06.722171 localhost.partitur.se.5432 > localhost.partitur.se.1903: P 16:77(61) ack 327 win 57344 (DF)
0x0000   4500 0065 65a8 4000 4006 d6e8 7f00 0001        E..ee.@.@.......
0x0010   7f00 0001 1538 076f dbe5 e1df dbe4 1791        .....8.o........
0x0020   5018 e000 1519 0000 5062 6c61 6e6b 0054        P.......Pblank.T
0x0030   0001 6765 7464 6174 6162 6173 6565 6e63        ..getdatabaseenc
0x0040   6f64 696e 6700 0000 0013 0020 ffff ffff        oding...........
0x0050   4480 0000 000a 4c41 5449 4e31 4353 454c        D.....LATIN1CSEL
0x0060   4543 5400 5a                                   ECT.Z
19:23:06.796484 localhost.partitur.se.1903 > localhost.partitur.se.5432: P 327:386(59) ack 77 win 57344 (DF)
0x0000   4500 0063 65c2 4000 4006 d6d0 7f00 0001        E..ce.@.@.......
0x0010   7f00 0001 076f 1538 dbe4 1791 dbe5 e21c        .....o.8........
0x0020   5018 e000 3662 0000 5153 454c 4543 5420        P...6b..QSELECT.
0x0030   656e 616d 6e20 6672 6f6d 206d 6174 7269        enamn.from.matri
0x0040   6b65 6c20 7768 6572 6520 7573 6572 6964        kel.where.userid
0x0050   3d27 5061 756c 2047 6972 6765 6e73 6f68        ='Paul.Girgensoh
0x0060   6e27 00                                        n'.
19:23:06.813505 localhost.partitur.se.5432 > localhost.partitur.se.1903: P 77:128(51) ack 386 win 57344 (DF)
0x0000   4500 005b 65d1 4000 4006 d6c9 7f00 0001        E..[e.@.@.......
0x0010   7f00 0001 1538 076f dbe5 e21c dbe4 17cc        .....8.o........
0x0020   5018 e000 e733 0000 5062 6c61 6e6b 0054        P....3..Pblank.T
0x0030   0001 656e 616d 6e00 0000 0019 ffff ffff        ..enamn.........
0x0040   ffff 4480 0000 000e 4769 7267 656e 736f        ..D.....Girgenso
0x0050   686e 4353 454c 4543 5400 5a                    hnCSELECT.Z
19:23:06.908042 localhost.partitur.se.1903 > localhost.partitur.se.5432: . ack 128 win 57344 (DF)
0x0000   4500 0028 6625 4000 4006 d6a8 7f00 0001        E..(f%@.@.......
0x0010   7f00 0001 076f 1538 dbe4 17cc dbe5 e24f        .....o.8.......O
0x0020   5010 e000 0344 0000                            P....D..

pgsql-admin by date:

Previous
From: "ryan"
Date:
Subject: Installation problem
Next
From: Ken Kinder
Date:
Subject: Re: How do I port data into Postgres from other RDBMSs