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

[Hinton, D. <Don...@pi...>]

Haven't used the router feature, but it seems to me your regular expressions may be the problem.  Could you try something like this:

<query pattern="\s+rms\."/> 

or 

<query pattern="\s*rms\."/>

hth...
don

> -----Original Message-----
> From: David Ethell [mailto:dav...@et...]
> Sent: Wednesday, May 08, 2013 5:39 AM
> To: sql...@li...
> Subject: [Sqlrelay-discussion] Router not working with two databases
> (oracle and mysql)
> 
> 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>
> 
> --------------------------------------------------------------------------
> ----
> Learn Graph Databases - Download FREE O'Reilly Book "Graph Databases" is
> the definitive new guide to graph databases and their applications. This
> 200-page book is written by three acclaimed leaders in the field. The
> early access version is available now.
> 
> Download your free book today! http://p.sf.net/sfu/neotech_d2d_may
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you should not disseminate, distribute, alter or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmissions cannot be guaranteed to be secure or without error as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise during or as a result of e-mail transmission. If verification is required, please request a hard-copy version. This message is provided for information purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments in any jurisdiction.