Re: local queries vs remote queries

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 "<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: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/