[Sqlrelay-discussion] SQLRelay - queries won't run, waiting for available connection (HPUX 11.11, Oracle 10g)

[Bryan R. <BRe...@fn...>]

I'm running SQLRelay on an HPUX 11.11 system, attempting to connect to an Oracle 10g database.

Everything installs and starts without errors, but when attempting to connect and run any sort of query, nothing returns.

I've tried using the command-line utility "fields" as well as "sqlrsh" to connect - either will successfully spawn a new sqlr-listener-debug program (config has debug="listener_and_connection"), but then the listener simply stops after logging:
10/14/2008 08:22:35 CDT listener [23566] : acquiring exclusive shm access

10/14/2008 08:22:35 CDT listener [23566] : done acquiring exclusive shm access

10/14/2008 08:22:35 CDT listener [23566] : waiting for an available connection

-- and then does nothing further until it times out and exits.

There is 1 sqlr-connection-oracle8-debug daemon launched at start, the tail of whose log shows:
10/14/2008 08:22:35 CDT connection [23406] :    done releasing announce mutex

10/14/2008 08:22:35 CDT connection [23406] : done announcing availability...

10/14/2008 08:22:35 CDT connection [23406] : waiting for client...

-- and never logs any further data.

If I attempt multiple times, new listeners and (after the first attempt, when a new listener is spawned and "queued") new connection daemons are launched, but none ever establish a connection between listener and connection, simply getting the same "waiting for an available connection" and "waiting for client" message, respectively.

I've checked the "common problems" list in your documentation, and confirmed the var/sqlrelay/tmp directory has 777 permissions (as do the three subfolders: ipc, pids, and sockets), cleared the temp files out of all three subdirectories, confirmed 666 permissions on sockseq, cleared all visible shared memory and semaphore values in ipcs (originally there were a few "hung" from another user that I suspected for this problem), and still no connections.  I'm using "-config ~/sqlrelay/etc/sqlrelay.conf -id dev" flags that are identical to the ones used for sqlr-start, so I don't expect it should be attempting the wrong port for the connection.

When attempting the connection with sqlrsh, I receive:

SQLRShell - Version 0.22
        Connected to: localhost:9000 as sqlrelay

        type help; for a help.

0> select col2 from testtable;
Failed to authenticate.
 A network error may have ocurred.

        Rows Returned   : 0
        Fields Returned : 0
        System time     : 10000

0>


-- though when I check the logs for the listener on that connection, it still shows the same "waiting for available connection" message as the last line of the debug, and never shows having connected to the sqlr-connection daemon to even authenticate against Oracle itself - and shows having already authenticated against sqlrelay in the output above.  I also used sqlplus to connect to Oracle directly, and confirmed the ID/PW and oracle instance being used in the config information.  I also confirmed in the sqlr-connection debug logs, they are showing:

10/14/2008 08:22:04 CDT connection [23406] : logging in...

10/14/2008 08:22:04 CDT connection [23406] : done logging in

...

10/14/2008 08:22:04 CDT connection [23406] : waiting for available database...

connection : database is available

10/14/2008 08:22:04 CDT connection [23406] : database is available

-- seeming to imply to me that it is in fact connecting to oracle with the provided login information successfully in the connection daemon.

Config setup currently in place (I turned down the listenertimeout and sessiontimeout variables after first experiencing the issue, so that I didn't wait 10 minutes for a listener to die.  I've also tried it with connections="3" to no avail, as all three still fail to show as available to the spawned listener when attempting a connection, despite showing in their respective logs that they are flagged as available.  I'm currently starting the process manually with sqlr-start as the user bredshaw.):

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <!-- Regular SQL Relay Instance -->
        <instance id="dev" port="9000" socket="/tmp/dev.socket" dbase="oracle8" connections="1" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="rollback" sessiontimeout="30" runasuser="bredshaw" runasgroup="usergrp" cursors="1" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="listener_and_connection" maxquerysize="512" maxstringbindvaluelength="512" maxlobbindvaluelength="1024" idleclienttimeout="-1" maxlisteners="10" listenertimeout="30" reloginatstart="no">
                <users>
                        <user user="sqlrelay" password="XXXXXXXX"/>
                </users>
                <connections>
                        <connection connectionid="dev" string="user=bredshaw;password=XXXXXXXX;oracle_sid=dev;oracle_home=/usr3/oracle" metric="1" behindloadbalancer="no"/>
                </connections>
        </instance>
</instances>

(note, some server names have been changed, to protect the innocent :)

Bryan Redshaw