[Sqlrelay-discussion] Router not working with two databases (oracle and mysql)

[David E. <dav...@et...>]

I have what I think is a simple configuration with two databases
(oracle and mysql) and a router to allow one connection to use both
DBs. sqlrsh works fine when connecting directly to either instance,
but does not work at all when I connect to the router instance. I just
get this error:

"There was an error, but the connection died trying to retrieve it.  Sorry."

I get this error no matter what query I try. I'm hitting a deadline on
getting this implemented and the router is the only thing left in my
way so that I can SELECT from the oracle instance and INSERT that row
into MySQL.

Here is my sqlrelay.conf:



<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

<!-- Regular SQL Relay Instance -->
        <instance id="oracle" port="" socket="/tmp/oracle.socket"
dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5"
growby="1" ttl="60" maxsessioncount="1000" endofsession="commit"
sessiontimeout="600" runasuser="nobody" runasgroup="nobody"
cursors="5" maxcursors="10" cursors_growby="1" authtier="connection"
handoff="pass" deniedips="" allowedips="" maxquerysize="65536"
maxstringbindvaluelength="4000" maxlobbindvaluelength="71680"
idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0"
reloginatstart="no" fakeinputbindvariables="no"
translatebindvariables="no" isolationlevel="read committed"
ignoreselectdatabase="no" waitfordowndatabase="yes">
                <users>
                        <user user="rushmoreuser" password="rushmore"/>
                </users>
                <session>
                        <start>
                                <runquery>alter session set
nls_date_format='MM-DD-YYYY HH24:MI:SS'</runquery>
                        </start>
                        <end>
                                <runquery>alter session set
nls_date_format='DD-MON-YYYY'</runquery>
                        </end>
                </session>
                <connections>
                        <connection connectionid="rushmore1"
string="user=someuser;password=somepwd;oracle_sid=(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED) (SID = rmsprod)));" metric="1"
behindloadbalancer="no"/>
                </connections>
        </instance>

        <instance id="rms" port="" socket="/tmp/rms.socket"
dbase="mysql" connections="3" maxconnections="15" maxqueuelength="5"
growby="1" ttl="60" maxsessioncount="1000" endofsession="commit"
sessiontimeout="600" runasuser="nobody" runasgroup="nobody"
cursors="5" maxcursors="10" cursors_growby="1" authtier="connection"
handoff="pass" deniedips="" allowedips="" maxquerysize="65536"
maxstringbindvaluelength="4000" maxlobbindvaluelength="71680"
idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0"
reloginatstart="no" fakeinputbindvariables="no"
translatebindvariables="no" isolationlevel="read committed"
ignoreselectdatabase="no" waitfordowndatabase="yes">
                <users>
                        <user user="rmsuser" password="rushmore"/>
                </users>
                <connections>
                        connection connectionid="rms1"
string="user=someuser;password=somepwd;db=rei" metric="1"
behindloadbalancer="no"/>
                </connections>
        </instance>

<!-- This instance sends queries containing "rei." to the mysql
                database and "rms." to the oracle database -->
        <instance id="router" port="9000" socket="/tmp/router.socket"
dbase="router" connections="3" maxconnections="15" maxqueuelength="5"
growby="1" ttl="60" endofsession="commit" sessiontimeout="600"
runasuser="nobody" runasgroup="nobody" cursors="5"  deniedips=""
allowedips="" debug="none" maxquerysize="65536"
maxstringbindvaluelength="4000" maxlobbindvaluelength="71680"
idleclienttimeout="-1">
                <users>
                        <user user="rushmore" password="rushmore"/>
                </users>
                <router>
                        <!-- send all mysqldb queries to "mysql" -->
                        <route host="" port=""
socket="/tmp/rms.socket" user="rmsuser" password="rushmore">
                                <query pattern="+\srei\."/>
                        </route>
                        <!-- send all postgresqldb queries to "oracle" -->
                        <route host="" port=""
socket="/tmp/oracle.socket" user="rushmoreuser" password="rushmore">
                                <query pattern="+\srms\."/>
                        </route>
                </router>
        </instance>

</instances>