local queries vs remote queries

local queries vs remote queries

am 14.09.2006 16:32:25 von Stuart Kendrick

hi,

i'm looking for trouble-shooting pointers, particularly around how to
debug query results

on one host, i have a PHP reporting tool querying a PostGres back-end.
Recently (i must have changed something ... but ... i don't remember
even logging into this box recently, let alone changing anything),
reports starting returning zero rows

however, when i point the reporting front-end at another back-end host
(the development box), the reports contain results. the results even
look correct ;)

when i run 'psql' on the production box and manually enter SELECT
statements, i see results. those results look awfully similar to the
results i see when i point my reporting front-end at the development box
back-end ;)


here's a window into my code:

[...]
echo "

$sql

";

# Query Soma
$dbh = connect_db();
$q = query_db($dbh, $sql, $place);

# Find metadata
$num_hosts = $q->numRows();
DB::isError($q) and die ($q->getMessage());
echo "

# of Records = $num_hosts

";

# Generate and print the table
generate_table($q);
[...]

function connect_db () {
$dsn = 'pgsql://foo:password@starsha.fhcrc.org/soma';
$dbh = DB::connect($dsn, array('debug' => 1));
DB::isError($dbh) and die ($dbh->getMessage());
return $dbh;
}

function query_db ($dbh, $sql, $place) {
$q = $dbh->query($sql, $place);
DB::isError($q) and die ($q->getMessage());
return $q;
}
[...]


when i perform a manual query via psql, i get results:

soma=# SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
hosts.osver = os_versions.versionid WHERE last_seen > 2006-08-14 AND
vlan = 74 ORDER BY ip_addr ASC;
[...results...]

but when i run a query using my PHP front-end, i don't. the debug output
(echo stmts) to my browser looks like this:

Querying Soma for Vlan = 74 WHERE Last_seen > 2006-08-14

SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
hosts.osver = os_versions.versionid WHERE last_seen > ? AND vlan = ?
ORDER BY ip_addr ASC

# of Records = 0

Zero records. No output. As though the database were empty. i've run
a bunch of queries ... my reporting front-end allows me to produce a
dozen or so reports ... and they all return 0 records



ok, so i enabled postgres' statement logging capability in postgresql.conf:
[...]
log_statement = 'all'
[...]

here's what i see when i perform a manual 'psql' query:


Sep 13 10:29:09 starsha postgres[24143]: [2-1] LOG: connection
received: host=[local]
Sep 13 10:29:09 starsha postgres[24143]: [3-1] LOG: connection
authorized: user=foo database=soma
Sep 13 10:29:31 starsha postgres[24143]: [4-1] LOG: statement: SELECT
mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname, dns_hostname,
version_name, snmp_sys_descr,
Sep 13 10:29:31 starsha postgres[24143]: [4-2] snmp_sys_objectid,
first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
hosts.osver = os_versions.versionid
Sep 13 10:29:31 starsha postgres[24143]: [4-3] WHERE last_seen >
2006-08-14 AND vlan = 74 ORDER BY ip_addr ASC;
Sep 13 10:29:32 starsha postgres[24143]: [5-1] LOG: duration: 93.679 ms
Sep 13 10:29:53 starsha postgres[24143]: [6-1] LOG: disconnection:
session time: 0:00:43.95 user=foo database=soma host=[local]


and here's what i see when my PHP code performs the query:

Sep 13 10:24:26 starsha postgres[24115]: [2-1] LOG: connection
received: host=starsha.fhcrc.org port=50184
Sep 13 10:24:26 starsha postgres[24115]: [3-1] LOG: connection
authorized: user=foo database=soma
Sep 13 10:24:26 starsha postgres[24115]: [4-1] LOG: statement: SELECT
mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname, ^M
Sep 13 10:24:26 starsha postgres[24115]: [4-2]
dns_hostname, version_name, snmp_sys_descr, ^M
Sep 13 10:24:26 starsha postgres[24115]: [4-3]
snmp_sys_objectid, first_seen, last_seen, last_updated FROM hosts LEFT
JOIN os_versions ON hosts.osver =
Sep 13 10:24:26 starsha postgres[24115]: [4-4] os_versions.versionid
WHERE last_seen > '2006-08-14' AND vlan = '74' ORDER BY ip_addr ASC
Sep 13 10:24:26 starsha postgres[24115]: [5-1] LOG: duration: 34.641 ms
Sep 13 10:24:26 starsha postgres[24115]: [6-1] LOG: disconnection:
session time: 0:00:00.10 user=foo database=soma
host=production.company.com port=50184


those '^M' look suspicious ... and so do the single quotes around
2006-08-14 and 74 ... when i try typing single quotes into a manual psql
query, i get nothing in response. could this be a symptom of the problem?

case 1:
then, i turned to Wireshark (Ethereal's successor) to get another view
of what is happening. for example, when i perform the manual psql query
above, i have Wireshark sniffing on the local Ethernet interface, and i
see the following packet trace
https://vishnu.fhcrc.org/php-db/local-psql-works.pdf notice how packet
#14 is expanded, and how you can see the text of the SELECT statement in
both the english decode window and in the hex decode window. no ^M
and no single quotes

case 2:
similarly, https://vishnu.fhcrc.org/php-db/remote-php-works.pdf
illustrates the case where my reporting front-end points to the
development backend. the query text sits in packet #12, which i have
expanded in this display. ahhh ... but here, i can see \r\n, i.e.
carriage return + new-line ... *and* single quotes ... so perhaps these
aren't a problem

case 3:
https://vishnu.fhcrc.org/php-db/local-php-broken.pdf illustrates the
case where my reporting front-end points to the production back-end,
i.e. to the postgres database co-located on the same box. packet #13
contains the SELECT statement. you can see that the remaining packets
are small, and in packet #15 the host closes the postgres connection
(TCP FIN). once again, i can see the carriage return + new-line
combination along with the single quotes. problem? maybe ... but these
characters show up in case 2, and case 2 returns results ...



i've stared at the text of the SELECT statement, in each packet trace,
looking for differences. aside from the \r\n and single quotes, i don't
see anything disimilar

so, back to my question. how else might i trouble-shoot this? i figure
comparing the output of 'psql' to my PHP-generated output was a good
first step ... encourages me to believe that my SQL syntax is correct
and that my database contains data. what steps might i take next, to
figure out what is happening here?

production> php -v
PHP 4.3.10 (cli) (built: Mar 22 2005 19:34:44)
Copyright (c) 1997-2004 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
production> cd /usr/share/pear
production> grep -i version DB.php
[...]
* @version Release 1.7.6
[...]

v8.1.4 PostGreSQL. OpenSuSE 10.1

--sk

stuart kendrick
fhcrc

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: local queries vs remote queries

am 19.09.2006 07:31:28 von Chris

Stuart Kendrick wrote:
> hi,
>
> i'm looking for trouble-shooting pointers, particularly around how to
> debug query results
>
> on one host, i have a PHP reporting tool querying a PostGres back-end.
> Recently (i must have changed something ... but ... i don't remember
> even logging into this box recently, let alone changing anything),
> reports starting returning zero rows
>
> however, when i point the reporting front-end at another back-end host
> (the development box), the reports contain results. the results even
> look correct ;)
>
> when i run 'psql' on the production box and manually enter SELECT
> statements, i see results. those results look awfully similar to the
> results i see when i point my reporting front-end at the development box
> back-end ;)
>
>
> here's a window into my code:
>
> [...]
> echo "

$sql

";
>
> # Query Soma
> $dbh = connect_db();
> $q = query_db($dbh, $sql, $place);
>
> # Find metadata
> $num_hosts = $q->numRows();
> DB::isError($q) and die ($q->getMessage());
> echo "

# of Records = $num_hosts

";
>
> # Generate and print the table
> generate_table($q);
> [...]
>
> function connect_db () {
> $dsn = 'pgsql://foo:password@starsha.fhcrc.org/soma';
> $dbh = DB::connect($dsn, array('debug' => 1));
> DB::isError($dbh) and die ($dbh->getMessage());
> return $dbh;
> }
>
> function query_db ($dbh, $sql, $place) {
> $q = $dbh->query($sql, $place);
> DB::isError($q) and die ($q->getMessage());
> return $q;
> }
> [...]
>
>
> when i perform a manual query via psql, i get results:
>
> soma=# SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
> dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
> first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
> hosts.osver = os_versions.versionid WHERE last_seen > 2006-08-14 AND
> vlan = 74 ORDER BY ip_addr ASC;
> [...results...]
>
> but when i run a query using my PHP front-end, i don't. the debug output
> (echo stmts) to my browser looks like this:
>
> Querying Soma for Vlan = 74 WHERE Last_seen > 2006-08-14
>
> SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
> dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
> first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
> hosts.osver = os_versions.versionid WHERE last_seen > ? AND vlan = ?
> ORDER BY ip_addr ASC
>
> # of Records = 0
>
> Zero records. No output. As though the database were empty. i've run
> a bunch of queries ... my reporting front-end allows me to produce a
> dozen or so reports ... and they all return 0 records
>
>
>
> ok, so i enabled postgres' statement logging capability in postgresql.conf:
> [...]
> log_statement = 'all'
> [...]
>
> here's what i see when i perform a manual 'psql' query:
>
>
> Sep 13 10:29:09 starsha postgres[24143]: [2-1] LOG: connection
> received: host=[local]
> Sep 13 10:29:09 starsha postgres[24143]: [3-1] LOG: connection
> authorized: user=foo database=soma
> Sep 13 10:29:31 starsha postgres[24143]: [4-1] LOG: statement: SELECT
> mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname, dns_hostname,
> version_name, snmp_sys_descr,
> Sep 13 10:29:31 starsha postgres[24143]: [4-2] snmp_sys_objectid,
> first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
> hosts.osver = os_versions.versionid
> Sep 13 10:29:31 starsha postgres[24143]: [4-3] WHERE last_seen >
> 2006-08-14 AND vlan = 74 ORDER BY ip_addr ASC;
> Sep 13 10:29:32 starsha postgres[24143]: [5-1] LOG: duration: 93.679 ms
> Sep 13 10:29:53 starsha postgres[24143]: [6-1] LOG: disconnection:
> session time: 0:00:43.95 user=foo database=soma host=[local]
>
>
> and here's what i see when my PHP code performs the query:
>
> Sep 13 10:24:26 starsha postgres[24115]: [2-1] LOG: connection
> received: host=starsha.fhcrc.org port=50184
> Sep 13 10:24:26 starsha postgres[24115]: [3-1] LOG: connection
> authorized: user=foo database=soma
> Sep 13 10:24:26 starsha postgres[24115]: [4-1] LOG: statement: SELECT
> mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname, ^M
> Sep 13 10:24:26 starsha postgres[24115]: [4-2]
> dns_hostname, version_name, snmp_sys_descr, ^M
> Sep 13 10:24:26 starsha postgres[24115]: [4-3]
> snmp_sys_objectid, first_seen, last_seen, last_updated FROM hosts LEFT
> JOIN os_versions ON hosts.osver =
> Sep 13 10:24:26 starsha postgres[24115]: [4-4] os_versions.versionid
> WHERE last_seen > '2006-08-14' AND vlan = '74' ORDER BY ip_addr ASC
> Sep 13 10:24:26 starsha postgres[24115]: [5-1] LOG: duration: 34.641 ms
> Sep 13 10:24:26 starsha postgres[24115]: [6-1] LOG: disconnection:
> session time: 0:00:00.10 user=foo database=soma
> host=production.company.com port=50184
>
>
> those '^M' look suspicious ... and so do the single quotes around
> 2006-08-14 and 74 ... when i try typing single quotes into a manual psql
> query, i get nothing in response. could this be a symptom of the problem?

^M's are newlines, nothing to worry about.

So if you do the same query with & without the quotes it behaves
differently?

SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
hosts.osver = os_versions.versionid WHERE last_seen > 2006-08-14 AND
vlan = 74 ORDER BY ip_addr ASC;

vs

SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
hosts.osver = os_versions.versionid WHERE last_seen > '2006-08-14' AND
vlan = '74' ORDER BY ip_addr ASC;

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: local queries vs remote queries

am 19.09.2006 17:28:51 von Stuart Kendrick

hi chris,

right. if i run 'psql -d soma' and then type the first SELECT, i get
results ... if i type the second SELECT, i get zero rows. this seemed
odd to me ... i wouldn't think that postgres would care. but perhaps it
does

hmm. ok, i just tried this again ... and now i'm getting results, no
matter whether i include single quotes or not, in the syntax of the SELECT

and ... now i'm getting the same results, from both the psql interface
*and* from my php interface

so. where does this leave me. was i hallucinating a few days ago? i
find that hard to believe -- i spent a couple hours building that post,
along with all the supporting traces

ok, if this effect were continuing ... where the single quotes mattered
.... what would that lead you to suggest? some issue inside PostGres itself?

--sk

>
> ^M's are newlines, nothing to worry about.
>
> So if you do the same query with & without the quotes it behaves
> differently?
>
> SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
> dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
> first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
> hosts.osver = os_versions.versionid WHERE last_seen > 2006-08-14 AND
> vlan = 74 ORDER BY ip_addr ASC;
>
> vs
>
> SELECT mac, ip_addr, vlan, ad_ou, current_os_user, os_hostname,
> dns_hostname, version_name, snmp_sys_descr, snmp_sys_objectid,
> first_seen, last_seen, last_updated FROM hosts LEFT JOIN os_versions ON
> hosts.osver = os_versions.versionid WHERE last_seen > '2006-08-14' AND
> vlan = '74' ORDER BY ip_addr ASC;
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: local queries vs remote queries

am 20.09.2006 08:59:40 von Chris

Stuart Kendrick wrote:
> hi chris,
>
> right. if i run 'psql -d soma' and then type the first SELECT, i get
> results ... if i type the second SELECT, i get zero rows. this seemed
> odd to me ... i wouldn't think that postgres would care. but perhaps it
> does
>
> hmm. ok, i just tried this again ... and now i'm getting results, no
> matter whether i include single quotes or not, in the syntax of the SELECT
>
> and ... now i'm getting the same results, from both the psql interface
> *and* from my php interface
>
> so. where does this leave me. was i hallucinating a few days ago? i
> find that hard to believe -- i spent a couple hours building that post,
> along with all the supporting traces
>
> ok, if this effect were continuing ... where the single quotes mattered
> ... what would that lead you to suggest? some issue inside PostGres
> itself?

Well you'll probably need quotes around the date otherwise it could be
interpreted as

2006 -
0008 -
0014
====
....

because you can use math functions in where clauses etc.

The quotes around the int field shouldn't matter however.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php