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 "<p>$sql</p>";
# 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 "<p># of Records = $num_hosts</p>";
# Generate and print the table
generate_table($q);
[...]
function connect_db () {
$dsn = 'pgsql://foo:[EMAIL PROTECTED]/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
