sqlrelay-discussion — Discussion of topics related to SQL Relay

[Sqlrelay-discussion] Compiling SQL Relay with DB2 V9.7

[Cal H. <ca...@fb...>]

Hi everyone,

I'm messing around with the latest sqlrelay-0.53.1 source, and I can't get
the configure script to detect my DB2 client correctly.

This is a CentOS 5.6 machine, 64 bit.  gcc 4.1.2.

The config.log shows:

configure:34754: checking for 32-bit build
configure:34780: g++ -o conftest   -I/opt/ibm/db2/V9.7/include  conftest.cc
-L/opt/ibm/db2/V9.7/lib32 -ldb2 >&5
/usr/bin/ld: skipping incompatible /opt/ibm/db2/V9.7/lib32/libdb2.so when
searching for -ldb2
/usr/bin/ld: cannot find -ldb2
collect2: ld returned 1 exit status
configure:34786: $? = 1
configure: failed program was:
| /* confdefs.h.  */
|
   ....


I copied the contents of the C++ test script into a new file, and tried to
compile it from the command line:

sqlrelay-0.53.1]$ g++ -o conftest   -I/opt/ibm/db2/V9.7/include test.cpp
-L/opt/ibm/db2/V9.7/lib32 -ldb2
/usr/bin/ld: skipping incompatible /opt/ibm/db2/V9.7/lib32/libdb2.so when
searching for -ldb2
/usr/bin/ld: cannot find -ldb2
collect2: ld returned 1 exit status

I guess my first question, is why is it trying to compile a 32 bit binary
on a 64-bit system?  I'd like to just create a 64-bit SQL Relay binary, but
I don't see any architecture parameters on the configure script.

Second question, is if the 32-bit test is required, why does my gcc think
the 32bit shared lib is incompatible?  I do have it set in ld.so.conf too:

$ ldconfig -p |grep libdb2.so
        libdb2.so.1 (libc6,x86-64) => /opt/ibm/db2/V9.7/lib64/libdb2.so.1
        libdb2.so.1 (libc6) => /opt/ibm/db2/V9.7/lib32/libdb2.so.1
        libdb2.so (libc6,x86-64) => /opt/ibm/db2/V9.7/lib64/libdb2.so
        libdb2.so (libc6) => /opt/ibm/db2/V9.7/lib32/libdb2.so

Thanks for any help!

--Cal

Re: [Sqlrelay-discussion] Client proto changes

[David M. <dav...@fi...>]

Hi Georgy,

Good question.  The client-server protocol has changed over the years. 
Most versions are cross-compatible, but in version 0.49, there was a 
fairly significant change that improved performance by removing a 
client-server round-trip, so unfortunately 0.51 and 0.46 are not compatible.

In the not-yet-released 0.54, I'm adding another change too, a protocol 
version flag.  Whenever 1.0 comes out, all 1.X versions should be 
protocol-compatible.  2.x should support the 2.X and 1.X protocols, and 
so on.  That's the plan, at least.

Sorry for the bad news.

Dave
dav...@fi...

On 12/9/2013 8:39 AM, Georgy Kirichenko wrote:
> Hi!
>
> I have a little question: was the client-server proto (between client (shared
> library) and server (listener/connection) parts) changed?
> Can i use new 0.51 clients so with older 0.43 version of service processes?
>
> Thanks
>
>
>
> ------------------------------------------------------------------------------
> Sponsored by Intel(R) XDK
> Develop, test and display web and hybrid apps with a single code base.
> Download it for free now!
> http://pubads.g.doubleclick.net/gampad/clk?id=111408631&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>

[Sqlrelay-discussion] Client proto changes

[Georgy K. <ge...@st...>]

Hi!

I have a little question: was the client-server proto (between client (shared 
library) and server (listener/connection) parts) changed?
Can i use new 0.51 clients so with older 0.43 version of service processes?

Thanks

[Sqlrelay-discussion] sqlrelay 0.51 with tcl on MAC OS X 10.6.8

[Miguel M. <mam...@iu...>]

Hello everybody

I am trying to run the TCL examples from: 
http://sqlrelay.sourceforge.net/sqlrelay/programming/tcl.html on mac, but it 
seems that the instructions are linux oriented. They refer to 
/usr/lib/sqlrelay/sqlrelay.so but in OSX the libraries are called *.dylib

I checked the installed files and found the /usr/lib/sqlrelay/sqlrelay.so file, 
but when I try it on tclsh I get:

% load /usr/lib/sqlrelay/sqlrelay.so sqlrelay
dlsym(0x100600090, Sqlrelay_SafeInit): symbol not founddlsym(0x100600090, 
Sqlrelay_Unload): symbol not founddlsym(0x100600090, Sqlrelay_SafeUnload): 
symbol not found

I think something is missing in the configure script since it doesn't seem to 
detect the OSX correctly to create the dylibs.

By the way, the sqlrelay 0.51 and rudiments 0.42  compile and install without 
errors on my mac (10.6.8).

Any help to fix this?

Regards

Miguel Montaño
			--- Comprar productos hechos en Mexico es asegurar el futuro economico a las familias mexicanas ---

_______________________________________________________________________________________________________
Este mensaje es confidencial. Si usted no es el destinatario de este mensaje, le suplicamos se lo notifique al remitente mediante un correo electronico asi como eliminar el presente mensaje y sus anexos de su computadora sin retener una copia de los mismos. No debe copiar este mensaje o usarlo para cualquier proposito ni divulgar su contenido. Grupo IUSA, S.A. de C.V. y/o sus subsidiarias y/o sus afiliadas se reservan el derecho de monitorear todas las comunicaciones de correo electronico (relacionadas o no con Grupo IUSA, S.A. de C.V., y/o subsidiarias y/o sus afiliadas) que se transmitan a traves de su sistema.

This email is confidential and may also be privileged.  If you are not the intended recipient please immediately advise the sender by reply e-mail and delete this message and its attachments from your computer without retaining a copy. You should not copy it or use it for any purpose nor disclose its contents to any other person. Grupo IUSA, S.A. de C.V. and/or its subsidiaries or/and its affiliates reserves the right to monitor all email communications (whether related to the business of Grupo IUSA, S.A. de C.V. and/or its subsidiaries or/and its affiliates or not) through its networks.

Re: [Sqlrelay-discussion] Lazy connection in php?

[David M. <dav...@fi...>]

Hi Andy,

You are correct, the SQL Relay API's do lazy connections.  Unfortunately 
there is no way to work around that at this point.

If it's a feature that's important to you then I could implement it on a 
contract basis.  See Consulting Services at 
http://sqlrelay.sourceforge.net/support.html for info about that. 
Otherwise I'll put it on the TODO list.

I hope SQL Relay is otherwise working out well for you.

Take care,

David Muse
dav...@fi...

On 9/18/2013 5:09 AM, Andy Malahovsky wrote:
> HI!
>
> I've noticed that
> if I try to connect via php by sqlrcon_alloc  to fake ip address or to
> anywhere where even sqlrelay is not installed or to sqlrelay with wrong
> connection string to db  - i.e. to anywhere it will get error to connect.
> The sqlrcon_alloc return resource.
>
>
> $connect =
> sqlrcon_alloc(''257.1.0.1",$port,"",$username,$passwd,$retryTime,$tries);
> // above resource
> $error=sqlrcon_errorMessage($this->connect);
> // error is empty
>
> and the error appear after first sql-request.
>
> So it seems like there is lazy connection.
>
> Is it possible to avoid that?
>
>
> ------------------------------------------------------------------------------
> LIMITED TIME SALE - Full Year of Microsoft Training For Just $49.99!
> 1,500+ hours of tutorials including VisualStudio 2012, Windows 8, SharePoint
> 2013, SQL 2012, MVC 4, more. BEST VALUE: New Multi-Library Power Pack includes
> Mobile, Cloud, Java, and UX Design. Lowest price ever! Ends 9/20/13.
> http://pubads.g.doubleclick.net/gampad/clk?id=58041151&iu=/4140/ostg.clktrk
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>

[Sqlrelay-discussion] Lazy connection in php?

[Andy M. <and...@gm...>]

HI!

I've noticed that
if I try to connect via php by sqlrcon_alloc  to fake ip address or to
anywhere where even sqlrelay is not installed or to sqlrelay with wrong
connection string to db  - i.e. to anywhere it will get error to connect.
The sqlrcon_alloc return resource.


$connect =
sqlrcon_alloc(''257.1.0.1",$port,"",$username,$passwd,$retryTime,$tries);
// above resource
$error=sqlrcon_errorMessage($this->connect);
// error is empty

and the error appear after first sql-request.

So it seems like there is lazy connection.

Is it possible to avoid that?

[Sqlrelay-discussion] SQL Relay - 0.51 is out

[David M. <dav...@fi...>]

Hello all,

I just released SQL Relay 0.51.

Not a whole lot of new stuff in this release, mainly updates to support 
newer versions of operating systems, databases and compilers.  Applied 
some patches.  Some of the database install docs were updated.  An 
obscure date conversion bug was fixed and documented.

Changelog:

* fixed minor perl DBI driver documentation issue
* added support for oracle 12c
* added support for db2 10.1 and 10.5
* updated docs for oracle 12c, db2 10.1 and 10.5 and sybase 15.7
* modernized database installation docs a little
* fixed db2/sybase detection to take the system architecture into 
account when searching for libs
* applied various build-related patches from Ville Silventoinen
* fixed rpm->rpmbuild in installation docs
* added ora-00020 to list of errors that will cause the oracle 
connection to attempt to re-login, per Ville Silventoinen
* fixed some MS SQL Server/dateddmm parameter issues and updated docs to 
explain the remaining issues

Give it a run, report bugs.

Dave
dav...@fi...

Re: [Sqlrelay-discussion] Problem compiling SQL Relay 0.50

[David M. <dav...@fi...>]

FYI, fixes for these issues will be in 0.51.

Dave
dav...@fi...

On 3/4/2013 6:55 AM, Ville Silventoinen wrote:
> Hi,
>
> I've now managed to compile sqlrelay-0.50 on RedHat 6.1. Here's what I
> had to do:
>
> 1. To solve the "cc1plus: error: unrecognized command line option
> "-Wno-long-double"" issue, I modified the configure to set it to empty
> string (I know this isn't a real fix, sorry):
>
> # diff configure configure.original
> 15168,15169c15168
> <   #WNOLONGDOUBLE="-Wno-long-double"
> <   WNOLONGDOUBLE=""
> ---
>  >   WNOLONGDOUBLE="-Wno-long-double"
>
> 2. sql_pwdenc should be spelled sqlr_pwdend in the spec file (missing
> "r" character). It was also missing from the %files section:
>
> # diff sqlrelay.spec sqlrelay.spec.original
> 422d421
> < %{_bindir}/sqlr-pwdenc
> 432c431
> < %{_libexecdir}/sqlrelay/sqlrpwdenc_*
> ---
>  > %{_libexecdir}/sqlrelay/sqlpwdenc_*
>
> 3. sqlrsh doesn't compile, because filename variable is not initialized
> (g++ treats warnings as errors). I also initialized filenamelen,
> probably not necessary:
>
> # diff src/cmdline/sqlrsh.cpp src/cmdline/sqlrsh.cpp.original
> 1571,1572c1571,1572
> <         size_t        filenamelen = 0;
> <         char        *filename = NULL;
> ---
>  >         size_t        filenamelen;
>  >         char        *filename;
>
> 4. I had to modify the Makefiles for postgresql and mysql. When I tried
> to build with rpmbuild, the install failed because the $(RM) commands in
> the Makefiles remove the *.a and *.la files. This took me quite a long
> time to figure out. I compared the Makefiles between 0.46 and 0.50
> releases and managed to fix them:
>
> # diff src/api/postgresql/Makefile src/api/postgresql/Makefile.original
> 38c38,39
> <     $(LIBTOOL) --mode=finish $(libdir)
> ---
>  >     $(RM) $(libdir)/libpqsqlrelay.a
>  >     $(RM) $(libdir)/libpqsqlrelay.la <http://libpqsqlrelay.la>
>
> # diff src/api/mysql/Makefile src/api/mysql/Makefile.original
> 64a65,66
>  >     $(RM) $(libdir)/libmysql3sqlrelay.a
>  >     $(RM) $(libdir)/libmysql3sqlrelay.la <http://libmysql3sqlrelay.la>
> 65a68,69
>  >     $(RM) $(libdir)/libmysql40sqlrelay.a
>  >     $(RM) $(libdir)/libmysql40sqlrelay.la <http://libmysql40sqlrelay.la>
> 66a71,72
>  >     $(RM) $(libdir)/libmysql41sqlrelay.a
>  >     $(RM) $(libdir)/libmysql41sqlrelay.la <http://libmysql41sqlrelay.la>
> 67a74,75
>  >     $(RM) $(libdir)/libmysql50sqlrelay.a
>  >     $(RM) $(libdir)/libmysql50sqlrelay.la <http://libmysql50sqlrelay.la>
> 69c77,78
> <     $(LIBTOOL) --mode=finish $(libdir)
> ---
>  >     $(RM) $(libdir)/libmysql51sqlrelay.a
>  >     $(RM) $(libdir)/libmysql51sqlrelay.la <http://libmysql51sqlrelay.la>
>
> After this the rpmbuild command works for me.
>
> # uname -a
> Linux vsi-oy-test1.ebi.ac.uk <http://vsi-oy-test1.ebi.ac.uk>
> 2.6.32-279.9.1.el6.x86_64 #1 SMP Fri Aug 31 09:04:24 EDT 2012 x86_64
> x86_64 x86_64 GNU/Linux
>
> # cat /etc/redhat-release
> Red Hat Enterprise Linux Server release 6.1 (Santiago)
>
> If you need any further details, please don't hesitate to ask.
>
> Cheers,
> Ville
>
> On 1 March 2013 17:37, Ville Silventoinen <vil...@gm...
> <mailto:vil...@gm...>> wrote:
>
>     Hi,
>
>     I just tried to compile sqlrelay-0.50 on RedHat 6.1 (I installed
>     rudiments 0.41-1 first successfully):
>
>     ...
>     libtool: compile:  g++ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
>     -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64
>     -mtune=generic -Wall -pipe -Wno-long-double -Werror
>     -DDEFAULT_CONFIG_FILE=\"/etc/sqlrelay.conf\"
>     -DSYSTEM_SQLRSHRC=\"/etc/sqlrsh\" -I./ -I../../ -I../../src/common
>     -I../../src/util -I../../src/server -I../../src/api/c++/include -c
>     sqlrsh.cpp  -fPIC -DPIC -o .libs/sqlrsh.o
>     cc1plus: warnings being treated as errors
>     sqlrsh.cpp: In member function 'void sqlrsh::execute(int, const
>     char**)':
>     sqlrsh.cpp:1572: error: 'filename' may be used uninitialized in this
>     function
>     At global scope:
>     cc1plus: error: unrecognized command line option "-Wno-long-double"
>     make[2]: *** [sqlrsh.lo] Error 1
>     make[2]: Leaving directory
>     `/root/rpmbuild/BUILD/sqlrelay-0.50/src/cmdline'
>     make[1]: *** [all] Error 2
>     make[1]: Leaving directory `/root/rpmbuild/BUILD/sqlrelay-0.50/src'
>     make: *** [all] Error 2
>     error: Bad exit status from /var/tmp/rpm-tmp.DBkPfW (%build)
>
>     I'm using a shell script like this to build the RPMs:
>
>     #!/bin/sh
>     rpmbuild -ta \
>                 -without db2 \
>                 -without erlang \
>                 -without freetds \
>                 -without firebird \
>                 -without odbc \
>                 -without ruby \
>                 -without sqlite \
>                 -without sybase \
>                 -without mdbtools \
>                 -without tcl \
>                 -without zope \
>                 -without gtk \
>                 -with oracle-instant-client-prefix=/usr \
>                 sqlrelay-0.50.tar.gz
>
>     Any ideas? Apologies if this has already been reported, I haven't
>     been able to keep up with the mailing list recently.
>
>     Thanks,
>     Ville
>
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_feb
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>

Re: [Sqlrelay-discussion] Multiple sqlr-listener processes and no connections

[David M. <dav...@fi...>]

Hi Ville,

Again, sorry for the long delay...

I'm not sure that a query could ever return that error, but you never 
know, so I'll add 20 to the list for the 0.51 release.

As for the larger issue of exceeding the max number of processes...

I'm not sure that much can be done other than to set maxconnections low 
enough that it can't exceed Oracle's max process limit.

Currently, when SQL Relay tries to launch a new connection, if it fails 
to start, then it will wait a bit and try again, over and over. 
Presumably, eventually, it would succeed, though in this case, it won't. 
  In any case, client connections will be left hanging until the new 
connection is up and sqlr-listener processes will accumulate too.

There are maxlisteners and listenertimeout configuration parameters 
though that can limit how many sqlr-listener processes that can 
accumulate and limit how long they'll wait if a connection can't be 
started.  You might get good results if you play with those.

The client API's have recently added setConnectTimeout() and 
setAuthenticationTimeout() methods too that allow the client to "give 
up" after a while if the server is unresponsive.  You might try setting 
those too.

It might be possible to ask the database what its maximum connection 
count is and limit maxconnections to that-35, but that would be 
difficult and would only help if SQL Relay is the only program using the 
database.  Other than that, I'm open to suggestions on what to do to 
address the issue.

Dave
dav...@fi...

On 3/5/2013 11:19 AM, Ville Silventoinen wrote:
> Hi Dave,
>
> I noticed the sqlrelay-0.50
> src/connections/oracle8/oracle8connection.cpp has following Oracle error
> codes:
>
>      // check for dead connection or shutdown in progress
>      switch (errcode) {
>          case 22: // invalid session ID; access denied
>          case 28: // your session has been killed
>          case 604: // error occurred at recursive SQL level ...
>          case 1012: // not logged on
>          case 1033: // oracle init/shutdown in progress
>          case 1041: // internal error. hostdef extension doesn't exist
>          case 1089: // immediate shutdown in progress -
>                  // no operations are permitted
>          case 2067: // transaction or savepoint rollback required
>          case 3114: // not connected to ORACLE
>          case 3113: // end-of-file on communication channel
>          case 3135: // connection lost contact
>          case 14452: // attempt to create, alter or drop an index on
>                  // temporary table already in use
>                  // (see note below)
>              *liveconnection=false;
>              break;
>          default:
>              *liveconnection=true;
>              break;
>      }
>
> I'm just wondering if this list should include "case 20" for ORA-00020,
> so the liveconnection is set to false?
>
> Thanks,
> Ville
>
> On 5 March 2013 15:11, Ville Silventoinen <vil...@gm...
> <mailto:vil...@gm...>> wrote:
>
>     Hi Dave,
>
>     Sorry it has taken so long to get back on this. I haven't been able
>     to reproduce the problem reliably until now.
>
>     Since last time, I've switched to Oracle 11g database. I noticed if
>     Oracle has quite a low maximum number of processes (150) and if I
>     configure too many max connections in SQL Relay, this same problem
>     happens.
>
>     If you want to reproduce it, you could deliberately set the
>     processes in Oracle quite low (alter system set processes = N) and
>     then configure SQL Relay to use max connections > N. My application
>     is a FUSE-based filesystem that I implemented, which keeps the
>     filesystem metadata in Oracle by using SQL Relay. When I run some
>     stress tests (fs_racer from Linux Test Project package does this
>     nicely when I run it for few minutes), I can easily get SQL Relay to
>     reach the max numbers.
>
>     What I'm seeing (with sqlrelay-0.50) is that after Oracle has reach
>     the max number of processes,, the number of sqlr-listeners grows and
>     doesn't back down (until I kill my application). You can see when
>     Oracle has encountered the error from the alter log:
>
>     # pwd
>     /u01/app/oracle/diag/rdbms/unofsdev/UNOFSDEV/trace
>
>     # tail alert_UNOFSDEV.log
>     ...
>     Tue Mar 05 11:16:24 2013
>     ORA-00020: maximum number of processes (150) exceeded
>       ORA-20 errors will not be written to the alert log for
>       the next minute. Please look at trace files to see all
>       the ORA-20 errors.
>     Tue Mar 05 11:16:39 2013
>     Process m000 submission failed with error = 20
>
>     I'm not sure what exactly happens in sqlr-listener or
>     sqlr-connection when they get ORA-00020 error (or if they even
>     receive the error), I never see it in my application. From my point
>     of view, my application just hangs when it tries to connect.
>
>     The only fix I've found so far is to avoid ORA-00020 like plague by
>     making sure the max SQL Relay connections never reaches the max
>     Oracle processes. You also need to note that Oracle has a set of its
>     own server processes that are included in this total (35 in my
>     case), so if your Oracle has max 150 processes, you actually have
>     115 to use for the SQL Relay connections. I got my test working by
>     configuring max 25 connections from 4 test machines (4 * 25 + 35 = 135).
>
>     I just thought I'd report this in case anyone else has been
>     struggling with similar issues. Here is the SQL Relay configuration
>     I'm using:
>
>          <instance id="unofs" port="9011"
>     socket="/var/sqlrelay/tmp/sockets/unofs.socket" dbase="oracle8"
>     connections="5" maxconnections="25" maxqueuelength="0" growby="1"
>     ttl="60" endofsession="rollback" sessiontimeout="600"
>     runasuser="w3secure" runasgroup="systems" cursors="5"
>     authtier="listener" handoff="pass" debug="none">
>              <users>
>                  <user user="unofs" password="secret"/>
>              </users>
>              <connections>
>                  <connection connectionid="unofsdev"
>     string="user=USER;password=PWD;oracle_sid=UNOFSDEV;oracle_home=/usr/lib64/oracle/10.2.0.3/client
>     <http://10.2.0.3/client>" metric="1"/>
>              </connections>
>          </instance>
>
>     If anyone has any experiences with ORA-00020 and SQL Relay, and how
>     to configure SQL Relay to cope with it, I'd be very grateful to
>     learn more.
>
>     Thanks,
>     Ville
>
>
>     On 26 September 2012 13:38, David Muse <dav...@fi...
>     <mailto:dav...@fi...>> wrote:
>
>         Hi Ville,
>
>         Sorry for not responding earlier.
>
>         SQL Relay should automatically re-log in if it loses connection
>         to the
>         database.  I recently added a parameter to disable that
>         behavior, but it
>         should do it by default.
>
>         It sounds like the sqlr-connection-mysql process is crashing for
>         some
>         reason but the semaphores are in such a state that the sqlr-scaler
>         doesn't realize this and doesn't spawn any new processes.
>
>         I've actually tried to reproduce this and gone through the code,
>         looking
>         for some semi-obvious thing that could cause it, but I can't find
>         anything.  My guess is that there is some particular query that
>         crashes
>         the process.
>
>         The last time I encountered a problem like this, it was with
>         oracle and
>         the problem was that I misinterpreted characters for bytes and
>         if OCI
>         said "allocate 100 characters", I'd allocate a buffer of 100 bytes,
>         which was fine until somebody started using unicode and the query
>         returned multi-byte characters.  Something like that.  I looked
>         again
>         for that kind of thing, but I didn't see anything.
>
>         Nevertheless, I suspect something like that is the problem.  It
>         would be
>         helpful if you could find the query or condition that's causing the
>         connections to crash, though I imagine that would be like
>         looking for a
>         needle in a haystack.
>
>         I'll be out of town for the next few days, but I can look into
>         it again
>         when I get back.
>
>         Dave
>         dav...@fi... <mailto:dav...@fi...>
>
>         On 09/25/2012 06:00 AM, Ville Silventoinen wrote:
>          > Hi,
>          >
>          > Just few more details on this:
>          >
>          > - I run SQL Relay 0.46 with two different instances configured in
>          > sqlrelay.conf; one for Oracle and one for MySQL.
>          >
>          > - When the problem happens, it's always with the MySQL
>         connections. All
>          > the sqlr-connection-mysql processes have stopped/crashed and
>         there are
>          > several sqlr-listener processes for the MySQL instance. They
>         seem to be
>          > unable to start any sqlr-connection-mysql processes.
>          >
>          > - Just guesswork, but could the problem be caused if for
>         whatever reason
>          > SQL Relay fails to login to MySQL? Would that stop the
>          > sqlr-connection-mysql process? I've asked the MySQL admin,
>         but we don't
>          > log the failed login attempts. I could turn on the connection
>         debug mode
>          > in SQL Relay, but last time we did that, it filled the /var
>         filesystem
>          > very fast, so I'd need to find some disk space for that.
>          >
>          > - I'm waiting when the problem happens next time so I can run the
>          > "sqlr-status -id INSTANCE" command to see what the raw
>         semaphore numbers
>          > are. I saw an old thread that seems quite similar to this,
>         but I'm not
>          > sure if it's related.
>          >
>          > Cheers,
>          > Ville
>          >
>          > On 4 September 2012 12:15, Ville Silventoinen
>          > <vil...@gm...
>         <mailto:vil...@gm...>
>         <mailto:vil...@gm...
>         <mailto:vil...@gm...>>> wrote:
>          >
>          >     Hi,
>          >
>          >     I'm using SQL Relay 0.45 on RedHat 6. Sometimes SQL Relay
>         goes into
>          >     a state where it starts several sqlr-listener processes,
>         but there
>          >     are no sqlr-connection processes. The application that
>         uses SQL
>          >     Relay hangs. There may be a problem with RedHat and the
>         network card
>          >     we are using, which can cause temporary loss of network
>          >     connectivity. The only cure that I've found is to restart
>         SQL Relay.
>          >
>          >     Today I decided to try SQL Relay 0.46. As I was running
>         tests, our
>          >     DBAs shutdown the test Oracle database (I wasn't aware of
>         this).
>          >     Again, the same situation happened: Several sqlr-listener
>         processes
>          >     and no connections.
>          >
>          >     I'm looking for some insight into how I could configure
>         SQL Relay so
>          >     it would automatically recover from the temporary loss of
>         network
>          >     connectivity or database availability. Is this possible?
>         Would it
>          >     help if I set maxlisteners to 0, listenertimeout to 60,
>         or tweak
>          >     some other configuration variable?
>          >
>          >     Thanks for any advice,
>          >     Ville
>          >
>          >
>          >
>          >
>          >
>          >
>         ------------------------------------------------------------------------------
>          > Live Security Virtual Conference
>          > Exclusive live event will cover all the ways today's security and
>          > threat landscape has changed and how IT managers can respond.
>         Discussions
>          > will include endpoint security, mobile security and the
>         latest in malware
>          > threats.
>         http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>          >
>          > _______________________________________________________
>          > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>          > http://www.doteasy.com
>          >
>          >
>          >
>          > _______________________________________________
>          > Sqlrelay-discussion mailing list
>          > Sql...@li...
>         <mailto:Sql...@li...>
>          > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>          >
>          >
>          > _______________________________________________________
>          > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>          > http://www.doteasy.com
>          >
>
>         ------------------------------------------------------------------------------
>         Live Security Virtual Conference
>         Exclusive live event will cover all the ways today's security and
>         threat landscape has changed and how IT managers can respond.
>         Discussions
>         will include endpoint security, mobile security and the latest
>         in malware
>         threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>         _______________________________________________
>         Sqlrelay-discussion mailing list
>         Sql...@li...
>         <mailto:Sql...@li...>
>         https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
>
>
>
> ------------------------------------------------------------------------------
> Everyone hates slow websites. So do we.
> Make your web apps faster with AppDynamics
> Download AppDynamics Lite for free today:
> http://p.sf.net/sfu/appdyn_d2d_feb
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>

Re: [Sqlrelay-discussion] Connection problem

[David M. <dav...@fi...>]

When using sqlplus, you appear to be using an ORACLE_SID of 
1.2.3.4:1234/SID but then in the sqlrelay configuration you appear to be 
using an oracle_sid of TEST.

Try using the same ORACLE_SID that you used with sqlplus and see if that 
fixes the problem or not.

David Muse
dav...@fi...

On 7/31/2013 1:28 AM, Jiří Wetter wrote:
> Hello everybody,
>
> I have trouble with connection. I have configured only one connection
> for testing purpose. With sqlplus64 haven't problem to log-in.
>
> sqlplus64 username/password@1.2.3.4:1234/SID => success
>
> but with sqlrelay always getting
>
> 07/30/2013 14:30:03 BST connection [11404] : attaching to shared memory
> and semaphores
> 07/30/2013 14:30:03 BST connection [11404] : id filename:
> /var/cache/sqlrelay/tmp/ipc/TEST
> 07/30/2013 14:30:03 BST connection [11404] : attaching to shared memory...
> 07/30/2013 14:30:03 BST connection [11404] : attaching to semaphores...
> 07/30/2013 14:30:03 BST connection [11404] : done attaching to shared
> memory and semaphores
> 07/30/2013 14:30:03 BST connection [11404] : getting unix socket...
> 07/30/2013 14:30:03 BST connection [11404] : opening
> /var/cache/sqlrelay/tmp/sockseq
> 07/30/2013 14:30:03 BST connection [11404] : locking...
> 07/30/2013 14:30:03 BST connection [11404] : got sequence number: 1178
> 07/30/2013 14:30:03 BST connection [11404] : writing new sequence
> number: 1179
> 07/30/2013 14:30:03 BST connection [11404] : unlocking...
> 07/30/2013 14:30:03 BST connection [11404] : done getting unix socket
> 07/30/2013 14:30:03 BST connection [11404] : logging in "TEST"...
> 07/30/2013 14:30:03 BST connection [11404] : log in failed
>
> 	<users>
>           <user user="sqlr_test"  password="sqlr_test"  />
>           </users>
>           <connections>
>               <connection connectionid="TEST"
>                   string="user=SameUsernameLikeSQLPLUS;password=SamePasswordLikeSQLPLUS
> ;oracle_sid=TEST;autocommit=no;maxselectlistsize=32"
>                   metric="1"
>               />
>           </connections>
>
>
> Thank you for help.
>
>
> ------------------------------------------------------------------------------
> Get your SQL database under version control now!
> Version control is standard for application code, but databases havent
> caught up. So what steps can you take to put your SQL databases under
> version control? Why should you start doing it? Read more to find out.
> http://pubads.g.doubleclick.net/gampad/clk?id=49501711&iu=/4140/ostg.clktrk
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>

[Sqlrelay-discussion] Connection problem

[Jiří W. <jir...@gm...>]

Hello everybody,

I have trouble with connection. I have configured only one connection 
for testing purpose. With sqlplus64 haven't problem to log-in.

sqlplus64 username/password@1.2.3.4:1234/SID => success

but with sqlrelay always getting

07/30/2013 14:30:03 BST connection [11404] : attaching to shared memory 
and semaphores
07/30/2013 14:30:03 BST connection [11404] : id filename: 
/var/cache/sqlrelay/tmp/ipc/TEST
07/30/2013 14:30:03 BST connection [11404] : attaching to shared memory...
07/30/2013 14:30:03 BST connection [11404] : attaching to semaphores...
07/30/2013 14:30:03 BST connection [11404] : done attaching to shared 
memory and semaphores
07/30/2013 14:30:03 BST connection [11404] : getting unix socket...
07/30/2013 14:30:03 BST connection [11404] : opening 
/var/cache/sqlrelay/tmp/sockseq
07/30/2013 14:30:03 BST connection [11404] : locking...
07/30/2013 14:30:03 BST connection [11404] : got sequence number: 1178
07/30/2013 14:30:03 BST connection [11404] : writing new sequence 
number: 1179
07/30/2013 14:30:03 BST connection [11404] : unlocking...
07/30/2013 14:30:03 BST connection [11404] : done getting unix socket
07/30/2013 14:30:03 BST connection [11404] : logging in "TEST"...
07/30/2013 14:30:03 BST connection [11404] : log in failed

	<users>
         <user user="sqlr_test"  password="sqlr_test"  />
         </users>
         <connections>
             <connection connectionid="TEST"
                 string="user=SameUsernameLikeSQLPLUS;password=SamePasswordLikeSQLPLUS
;oracle_sid=TEST;autocommit=no;maxselectlistsize=32"
                 metric="1"
             />
         </connections>


Thank you for help.

[Sqlrelay-discussion] Secure access from webapp to database

[Lois B. <loi...@ch...>]

Hello,

My question is rather general.  We are moving a web application to our DMZ
and I need to find a server/service that will allow the oracle database
queries to be transmitted encrypted through the firewall then sent to the
Oracle server.  It seems like sql relay could be the solution.  Does anyone
have any experience using sql relay in this way?  Could anyone point me in
the right direction?

Thanks for your help.

Peace,
Lois

-- 
Lois Bennett, MSEE
Senior System Administrator
Channing Laboratory
Brigham & Women's Hospital
A Teaching Affiliate of
Harvard Medical School and
Harvard School of Public Health

[Sqlrelay-discussion] FW: Andrew L

[Andrew L <akl...@ya...>]

dwrd http://techspot.mu/aung/RNDCHR,3,15%/tqllbyjmydl/fce/gqqiqbjvtlba.htm




gfbz 

[Sqlrelay-discussion] sqlrelay eats memory

[Fabio P. <f....@po...>]

Hello list,

I m having a problem with a sqlrelay installation used to connect to
Oracle on a CentOS 6.3 64bit installation.

Details:

- All the system packages are coming from default yum repo.

- I tried several different versions of sqlrelay and rudiments. The
problem appears always, no matter the version I use.

- Using sqlrelay 0.39.4 and rudiments 0.30 I have the problem on CentOS
6.3 64 bits *but not* on RedHat4.

- I also tried sqlrelay 0.49.1 + rudiments 0.40 on CentOS 6.2 but the
problem persists

- Sqlrelay slowly eats memory, when there is no more available RAM, the
oom killer starts doing its (in)famous job.

- I compile sqlrelay in this way:
./configure --disable-mysql
make uninstall
make



Any hint is strongly appreciated.

Regards,

Fabio

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.

[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>

Re: [Sqlrelay-discussion] Multiple sqlr-listener processes and no connections

[Ville S. <vil...@gm...>]

Hi Dave,

I noticed the sqlrelay-0.50 src/connections/oracle8/oracle8connection.cpp
has following Oracle error codes:

    // check for dead connection or shutdown in progress
    switch (errcode) {
        case 22: // invalid session ID; access denied
        case 28: // your session has been killed
        case 604: // error occurred at recursive SQL level ...
        case 1012: // not logged on
        case 1033: // oracle init/shutdown in progress
        case 1041: // internal error. hostdef extension doesn't exist
        case 1089: // immediate shutdown in progress -
                // no operations are permitted
        case 2067: // transaction or savepoint rollback required
        case 3114: // not connected to ORACLE
        case 3113: // end-of-file on communication channel
        case 3135: // connection lost contact
        case 14452: // attempt to create, alter or drop an index on
                // temporary table already in use
                // (see note below)
            *liveconnection=false;
            break;
        default:
            *liveconnection=true;
            break;
    }

I'm just wondering if this list should include "case 20" for ORA-00020, so
the liveconnection is set to false?

Thanks,
Ville

On 5 March 2013 15:11, Ville Silventoinen <vil...@gm...>wrote:

> Hi Dave,
>
> Sorry it has taken so long to get back on this. I haven't been able to
> reproduce the problem reliably until now.
>
> Since last time, I've switched to Oracle 11g database. I noticed if Oracle
> has quite a low maximum number of processes (150) and if I configure too
> many max connections in SQL Relay, this same problem happens.
>
> If you want to reproduce it, you could deliberately set the processes in
> Oracle quite low (alter system set processes = N) and then configure SQL
> Relay to use max connections > N. My application is a FUSE-based filesystem
> that I implemented, which keeps the filesystem metadata in Oracle by using
> SQL Relay. When I run some stress tests (fs_racer from Linux Test Project
> package does this nicely when I run it for few minutes), I can easily get
> SQL Relay to reach the max numbers.
>
> What I'm seeing (with sqlrelay-0.50) is that after Oracle has reach the
> max number of processes,, the number of sqlr-listeners grows and doesn't
> back down (until I kill my application). You can see when Oracle has
> encountered the error from the alter log:
>
> # pwd
> /u01/app/oracle/diag/rdbms/unofsdev/UNOFSDEV/trace
>
> # tail alert_UNOFSDEV.log
> ...
> Tue Mar 05 11:16:24 2013
> ORA-00020: maximum number of processes (150) exceeded
>  ORA-20 errors will not be written to the alert log for
>  the next minute. Please look at trace files to see all
>  the ORA-20 errors.
> Tue Mar 05 11:16:39 2013
> Process m000 submission failed with error = 20
>
> I'm not sure what exactly happens in sqlr-listener or sqlr-connection when
> they get ORA-00020 error (or if they even receive the error), I never see
> it in my application. From my point of view, my application just hangs when
> it tries to connect.
>
> The only fix I've found so far is to avoid ORA-00020 like plague by making
> sure the max SQL Relay connections never reaches the max Oracle processes.
> You also need to note that Oracle has a set of its own server processes
> that are included in this total (35 in my case), so if your Oracle has max
> 150 processes, you actually have 115 to use for the SQL Relay connections.
> I got my test working by configuring max 25 connections from 4 test
> machines (4 * 25 + 35 = 135).
>
> I just thought I'd report this in case anyone else has been struggling
> with similar issues. Here is the SQL Relay configuration I'm using:
>
>     <instance id="unofs" port="9011"
> socket="/var/sqlrelay/tmp/sockets/unofs.socket" dbase="oracle8"
> connections="5" maxconnections="25" maxqueuelength="0" growby="1" ttl="60"
> endofsession="rollback" sessiontimeout="600" runasuser="w3secure"
> runasgroup="systems" cursors="5" authtier="listener" handoff="pass"
> debug="none">
>         <users>
>             <user user="unofs" password="secret"/>
>         </users>
>         <connections>
>             <connection connectionid="unofsdev"
> string="user=USER;password=PWD;oracle_sid=UNOFSDEV;oracle_home=/usr/lib64/oracle/
> 10.2.0.3/client" metric="1"/>
>         </connections>
>     </instance>
>
> If anyone has any experiences with ORA-00020 and SQL Relay, and how to
> configure SQL Relay to cope with it, I'd be very grateful to learn more.
>
> Thanks,
> Ville
>
>
> On 26 September 2012 13:38, David Muse <dav...@fi...> wrote:
>
>> Hi Ville,
>>
>> Sorry for not responding earlier.
>>
>> SQL Relay should automatically re-log in if it loses connection to the
>> database.  I recently added a parameter to disable that behavior, but it
>> should do it by default.
>>
>> It sounds like the sqlr-connection-mysql process is crashing for some
>> reason but the semaphores are in such a state that the sqlr-scaler
>> doesn't realize this and doesn't spawn any new processes.
>>
>> I've actually tried to reproduce this and gone through the code, looking
>> for some semi-obvious thing that could cause it, but I can't find
>> anything.  My guess is that there is some particular query that crashes
>> the process.
>>
>> The last time I encountered a problem like this, it was with oracle and
>> the problem was that I misinterpreted characters for bytes and if OCI
>> said "allocate 100 characters", I'd allocate a buffer of 100 bytes,
>> which was fine until somebody started using unicode and the query
>> returned multi-byte characters.  Something like that.  I looked again
>> for that kind of thing, but I didn't see anything.
>>
>> Nevertheless, I suspect something like that is the problem.  It would be
>> helpful if you could find the query or condition that's causing the
>> connections to crash, though I imagine that would be like looking for a
>> needle in a haystack.
>>
>> I'll be out of town for the next few days, but I can look into it again
>> when I get back.
>>
>> Dave
>> dav...@fi...
>>
>> On 09/25/2012 06:00 AM, Ville Silventoinen wrote:
>> > Hi,
>> >
>> > Just few more details on this:
>> >
>> > - I run SQL Relay 0.46 with two different instances configured in
>> > sqlrelay.conf; one for Oracle and one for MySQL.
>> >
>> > - When the problem happens, it's always with the MySQL connections. All
>> > the sqlr-connection-mysql processes have stopped/crashed and there are
>> > several sqlr-listener processes for the MySQL instance. They seem to be
>> > unable to start any sqlr-connection-mysql processes.
>> >
>> > - Just guesswork, but could the problem be caused if for whatever reason
>> > SQL Relay fails to login to MySQL? Would that stop the
>> > sqlr-connection-mysql process? I've asked the MySQL admin, but we don't
>> > log the failed login attempts. I could turn on the connection debug mode
>> > in SQL Relay, but last time we did that, it filled the /var filesystem
>> > very fast, so I'd need to find some disk space for that.
>> >
>> > - I'm waiting when the problem happens next time so I can run the
>> > "sqlr-status -id INSTANCE" command to see what the raw semaphore numbers
>> > are. I saw an old thread that seems quite similar to this, but I'm not
>> > sure if it's related.
>> >
>> > Cheers,
>> > Ville
>> >
>> > On 4 September 2012 12:15, Ville Silventoinen
>> > <vil...@gm... <mailto:vil...@gm...>>
>> wrote:
>> >
>> >     Hi,
>> >
>> >     I'm using SQL Relay 0.45 on RedHat 6. Sometimes SQL Relay goes into
>> >     a state where it starts several sqlr-listener processes, but there
>> >     are no sqlr-connection processes. The application that uses SQL
>> >     Relay hangs. There may be a problem with RedHat and the network card
>> >     we are using, which can cause temporary loss of network
>> >     connectivity. The only cure that I've found is to restart SQL Relay.
>> >
>> >     Today I decided to try SQL Relay 0.46. As I was running tests, our
>> >     DBAs shutdown the test Oracle database (I wasn't aware of this).
>> >     Again, the same situation happened: Several sqlr-listener processes
>> >     and no connections.
>> >
>> >     I'm looking for some insight into how I could configure SQL Relay so
>> >     it would automatically recover from the temporary loss of network
>> >     connectivity or database availability. Is this possible? Would it
>> >     help if I set maxlisteners to 0, listenertimeout to 60, or tweak
>> >     some other configuration variable?
>> >
>> >     Thanks for any advice,
>> >     Ville
>> >
>> >
>> >
>> >
>> >
>> >
>> ------------------------------------------------------------------------------
>> > Live Security Virtual Conference
>> > Exclusive live event will cover all the ways today's security and
>> > threat landscape has changed and how IT managers can respond.
>> Discussions
>> > will include endpoint security, mobile security and the latest in
>> malware
>> > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>> >
>> > _______________________________________________________
>> > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>> >                http://www.doteasy.com
>> >
>> >
>> >
>> > _______________________________________________
>> > Sqlrelay-discussion mailing list
>> > Sql...@li...
>> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>> >
>> >
>> > _______________________________________________________
>> > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>> >                http://www.doteasy.com
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> Live Security Virtual Conference
>> Exclusive live event will cover all the ways today's security and
>> threat landscape has changed and how IT managers can respond. Discussions
>> will include endpoint security, mobile security and the latest in malware
>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>> _______________________________________________
>> Sqlrelay-discussion mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>
>

Re: [Sqlrelay-discussion] Multiple sqlr-listener processes and no connections

[Ville S. <vil...@gm...>]

Hi Dave,

Sorry it has taken so long to get back on this. I haven't been able to
reproduce the problem reliably until now.

Since last time, I've switched to Oracle 11g database. I noticed if Oracle
has quite a low maximum number of processes (150) and if I configure too
many max connections in SQL Relay, this same problem happens.

If you want to reproduce it, you could deliberately set the processes in
Oracle quite low (alter system set processes = N) and then configure SQL
Relay to use max connections > N. My application is a FUSE-based filesystem
that I implemented, which keeps the filesystem metadata in Oracle by using
SQL Relay. When I run some stress tests (fs_racer from Linux Test Project
package does this nicely when I run it for few minutes), I can easily get
SQL Relay to reach the max numbers.

What I'm seeing (with sqlrelay-0.50) is that after Oracle has reach the max
number of processes,, the number of sqlr-listeners grows and doesn't back
down (until I kill my application). You can see when Oracle has encountered
the error from the alter log:

# pwd
/u01/app/oracle/diag/rdbms/unofsdev/UNOFSDEV/trace

# tail alert_UNOFSDEV.log
...
Tue Mar 05 11:16:24 2013
ORA-00020: maximum number of processes (150) exceeded
 ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Tue Mar 05 11:16:39 2013
Process m000 submission failed with error = 20

I'm not sure what exactly happens in sqlr-listener or sqlr-connection when
they get ORA-00020 error (or if they even receive the error), I never see
it in my application. From my point of view, my application just hangs when
it tries to connect.

The only fix I've found so far is to avoid ORA-00020 like plague by making
sure the max SQL Relay connections never reaches the max Oracle processes.
You also need to note that Oracle has a set of its own server processes
that are included in this total (35 in my case), so if your Oracle has max
150 processes, you actually have 115 to use for the SQL Relay connections.
I got my test working by configuring max 25 connections from 4 test
machines (4 * 25 + 35 = 135).

I just thought I'd report this in case anyone else has been struggling with
similar issues. Here is the SQL Relay configuration I'm using:

    <instance id="unofs" port="9011"
socket="/var/sqlrelay/tmp/sockets/unofs.socket" dbase="oracle8"
connections="5" maxconnections="25" maxqueuelength="0" growby="1" ttl="60"
endofsession="rollback" sessiontimeout="600" runasuser="w3secure"
runasgroup="systems" cursors="5" authtier="listener" handoff="pass"
debug="none">
        <users>
            <user user="unofs" password="secret"/>
        </users>
        <connections>
            <connection connectionid="unofsdev"
string="user=USER;password=PWD;oracle_sid=UNOFSDEV;oracle_home=/usr/lib64/oracle/
10.2.0.3/client" metric="1"/>
        </connections>
    </instance>

If anyone has any experiences with ORA-00020 and SQL Relay, and how to
configure SQL Relay to cope with it, I'd be very grateful to learn more.

Thanks,
Ville

On 26 September 2012 13:38, David Muse <dav...@fi...> wrote:

> Hi Ville,
>
> Sorry for not responding earlier.
>
> SQL Relay should automatically re-log in if it loses connection to the
> database.  I recently added a parameter to disable that behavior, but it
> should do it by default.
>
> It sounds like the sqlr-connection-mysql process is crashing for some
> reason but the semaphores are in such a state that the sqlr-scaler
> doesn't realize this and doesn't spawn any new processes.
>
> I've actually tried to reproduce this and gone through the code, looking
> for some semi-obvious thing that could cause it, but I can't find
> anything.  My guess is that there is some particular query that crashes
> the process.
>
> The last time I encountered a problem like this, it was with oracle and
> the problem was that I misinterpreted characters for bytes and if OCI
> said "allocate 100 characters", I'd allocate a buffer of 100 bytes,
> which was fine until somebody started using unicode and the query
> returned multi-byte characters.  Something like that.  I looked again
> for that kind of thing, but I didn't see anything.
>
> Nevertheless, I suspect something like that is the problem.  It would be
> helpful if you could find the query or condition that's causing the
> connections to crash, though I imagine that would be like looking for a
> needle in a haystack.
>
> I'll be out of town for the next few days, but I can look into it again
> when I get back.
>
> Dave
> dav...@fi...
>
> On 09/25/2012 06:00 AM, Ville Silventoinen wrote:
> > Hi,
> >
> > Just few more details on this:
> >
> > - I run SQL Relay 0.46 with two different instances configured in
> > sqlrelay.conf; one for Oracle and one for MySQL.
> >
> > - When the problem happens, it's always with the MySQL connections. All
> > the sqlr-connection-mysql processes have stopped/crashed and there are
> > several sqlr-listener processes for the MySQL instance. They seem to be
> > unable to start any sqlr-connection-mysql processes.
> >
> > - Just guesswork, but could the problem be caused if for whatever reason
> > SQL Relay fails to login to MySQL? Would that stop the
> > sqlr-connection-mysql process? I've asked the MySQL admin, but we don't
> > log the failed login attempts. I could turn on the connection debug mode
> > in SQL Relay, but last time we did that, it filled the /var filesystem
> > very fast, so I'd need to find some disk space for that.
> >
> > - I'm waiting when the problem happens next time so I can run the
> > "sqlr-status -id INSTANCE" command to see what the raw semaphore numbers
> > are. I saw an old thread that seems quite similar to this, but I'm not
> > sure if it's related.
> >
> > Cheers,
> > Ville
> >
> > On 4 September 2012 12:15, Ville Silventoinen
> > <vil...@gm... <mailto:vil...@gm...>>
> wrote:
> >
> >     Hi,
> >
> >     I'm using SQL Relay 0.45 on RedHat 6. Sometimes SQL Relay goes into
> >     a state where it starts several sqlr-listener processes, but there
> >     are no sqlr-connection processes. The application that uses SQL
> >     Relay hangs. There may be a problem with RedHat and the network card
> >     we are using, which can cause temporary loss of network
> >     connectivity. The only cure that I've found is to restart SQL Relay.
> >
> >     Today I decided to try SQL Relay 0.46. As I was running tests, our
> >     DBAs shutdown the test Oracle database (I wasn't aware of this).
> >     Again, the same situation happened: Several sqlr-listener processes
> >     and no connections.
> >
> >     I'm looking for some insight into how I could configure SQL Relay so
> >     it would automatically recover from the temporary loss of network
> >     connectivity or database availability. Is this possible? Would it
> >     help if I set maxlisteners to 0, listenertimeout to 60, or tweak
> >     some other configuration variable?
> >
> >     Thanks for any advice,
> >     Ville
> >
> >
> >
> >
> >
> >
> ------------------------------------------------------------------------------
> > Live Security Virtual Conference
> > Exclusive live event will cover all the ways today's security and
> > threat landscape has changed and how IT managers can respond. Discussions
> > will include endpoint security, mobile security and the latest in malware
> > threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> >
> > _______________________________________________________
> > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> >                http://www.doteasy.com
> >
> >
> >
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> >
> > _______________________________________________________
> > Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
> >                http://www.doteasy.com
> >
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

Re: [Sqlrelay-discussion] Problem compiling SQL Relay 0.50

[Ville S. <vil...@gm...>]

Hi,

I've now managed to compile sqlrelay-0.50 on RedHat 6.1. Here's what I had
to do:

1. To solve the "cc1plus: error: unrecognized command line option
"-Wno-long-double"" issue, I modified the configure to set it to empty
string (I know this isn't a real fix, sorry):

# diff configure configure.original
15168,15169c15168
<   #WNOLONGDOUBLE="-Wno-long-double"
<   WNOLONGDOUBLE=""
---
>   WNOLONGDOUBLE="-Wno-long-double"

2. sql_pwdenc should be spelled sqlr_pwdend in the spec file (missing "r"
character). It was also missing from the %files section:

# diff sqlrelay.spec sqlrelay.spec.original
422d421
< %{_bindir}/sqlr-pwdenc
432c431
< %{_libexecdir}/sqlrelay/sqlrpwdenc_*
---
> %{_libexecdir}/sqlrelay/sqlpwdenc_*

3. sqlrsh doesn't compile, because filename variable is not initialized
(g++ treats warnings as errors). I also initialized filenamelen, probably
not necessary:

# diff src/cmdline/sqlrsh.cpp src/cmdline/sqlrsh.cpp.original
1571,1572c1571,1572
<         size_t        filenamelen = 0;
<         char        *filename = NULL;
---
>         size_t        filenamelen;
>         char        *filename;

4. I had to modify the Makefiles for postgresql and mysql. When I tried to
build with rpmbuild, the install failed because the $(RM) commands in the
Makefiles remove the *.a and *.la files. This took me quite a long time to
figure out. I compared the Makefiles between 0.46 and 0.50 releases and
managed to fix them:

# diff src/api/postgresql/Makefile src/api/postgresql/Makefile.original
38c38,39
<     $(LIBTOOL) --mode=finish $(libdir)
---
>     $(RM) $(libdir)/libpqsqlrelay.a
>     $(RM) $(libdir)/libpqsqlrelay.la

# diff src/api/mysql/Makefile src/api/mysql/Makefile.original
64a65,66
>     $(RM) $(libdir)/libmysql3sqlrelay.a
>     $(RM) $(libdir)/libmysql3sqlrelay.la
65a68,69
>     $(RM) $(libdir)/libmysql40sqlrelay.a
>     $(RM) $(libdir)/libmysql40sqlrelay.la
66a71,72
>     $(RM) $(libdir)/libmysql41sqlrelay.a
>     $(RM) $(libdir)/libmysql41sqlrelay.la
67a74,75
>     $(RM) $(libdir)/libmysql50sqlrelay.a
>     $(RM) $(libdir)/libmysql50sqlrelay.la
69c77,78
<     $(LIBTOOL) --mode=finish $(libdir)
---
>     $(RM) $(libdir)/libmysql51sqlrelay.a
>     $(RM) $(libdir)/libmysql51sqlrelay.la

After this the rpmbuild command works for me.

# uname -a
Linux vsi-oy-test1.ebi.ac.uk 2.6.32-279.9.1.el6.x86_64 #1 SMP Fri Aug 31
09:04:24 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.1 (Santiago)

If you need any further details, please don't hesitate to ask.

Cheers,
Ville

On 1 March 2013 17:37, Ville Silventoinen <vil...@gm...>wrote:

> Hi,
>
> I just tried to compile sqlrelay-0.50 on RedHat 6.1 (I installed rudiments
> 0.41-1 first successfully):
>
> ...
> libtool: compile:  g++ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
> -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64
> -mtune=generic -Wall -pipe -Wno-long-double -Werror
> -DDEFAULT_CONFIG_FILE=\"/etc/sqlrelay.conf\"
> -DSYSTEM_SQLRSHRC=\"/etc/sqlrsh\" -I./ -I../../ -I../../src/common
> -I../../src/util -I../../src/server -I../../src/api/c++/include -c
> sqlrsh.cpp  -fPIC -DPIC -o .libs/sqlrsh.o
> cc1plus: warnings being treated as errors
> sqlrsh.cpp: In member function 'void sqlrsh::execute(int, const char**)':
> sqlrsh.cpp:1572: error: 'filename' may be used uninitialized in this
> function
> At global scope:
> cc1plus: error: unrecognized command line option "-Wno-long-double"
> make[2]: *** [sqlrsh.lo] Error 1
> make[2]: Leaving directory `/root/rpmbuild/BUILD/sqlrelay-0.50/src/cmdline'
> make[1]: *** [all] Error 2
> make[1]: Leaving directory `/root/rpmbuild/BUILD/sqlrelay-0.50/src'
> make: *** [all] Error 2
> error: Bad exit status from /var/tmp/rpm-tmp.DBkPfW (%build)
>
> I'm using a shell script like this to build the RPMs:
>
> #!/bin/sh
> rpmbuild -ta \
>            -without db2 \
>            -without erlang \
>            -without freetds \
>            -without firebird \
>            -without odbc \
>            -without ruby \
>            -without sqlite \
>            -without sybase \
>            -without mdbtools \
>            -without tcl \
>            -without zope \
>            -without gtk \
>            -with oracle-instant-client-prefix=/usr \
>            sqlrelay-0.50.tar.gz
>
> Any ideas? Apologies if this has already been reported, I haven't been
> able to keep up with the mailing list recently.
>
> Thanks,
> Ville
>
>

[Sqlrelay-discussion] Problem compiling SQL Relay 0.50

[Ville S. <vil...@gm...>]

Hi,

I just tried to compile sqlrelay-0.50 on RedHat 6.1 (I installed rudiments
0.41-1 first successfully):

...
libtool: compile:  g++ -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
-fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64
-mtune=generic -Wall -pipe -Wno-long-double -Werror
-DDEFAULT_CONFIG_FILE=\"/etc/sqlrelay.conf\"
-DSYSTEM_SQLRSHRC=\"/etc/sqlrsh\" -I./ -I../../ -I../../src/common
-I../../src/util -I../../src/server -I../../src/api/c++/include -c
sqlrsh.cpp  -fPIC -DPIC -o .libs/sqlrsh.o
cc1plus: warnings being treated as errors
sqlrsh.cpp: In member function 'void sqlrsh::execute(int, const char**)':
sqlrsh.cpp:1572: error: 'filename' may be used uninitialized in this
function
At global scope:
cc1plus: error: unrecognized command line option "-Wno-long-double"
make[2]: *** [sqlrsh.lo] Error 1
make[2]: Leaving directory `/root/rpmbuild/BUILD/sqlrelay-0.50/src/cmdline'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/root/rpmbuild/BUILD/sqlrelay-0.50/src'
make: *** [all] Error 2
error: Bad exit status from /var/tmp/rpm-tmp.DBkPfW (%build)

I'm using a shell script like this to build the RPMs:

#!/bin/sh
rpmbuild -ta \
           -without db2 \
           -without erlang \
           -without freetds \
           -without firebird \
           -without odbc \
           -without ruby \
           -without sqlite \
           -without sybase \
           -without mdbtools \
           -without tcl \
           -without zope \
           -without gtk \
           -with oracle-instant-client-prefix=/usr \
           sqlrelay-0.50.tar.gz

Any ideas? Apologies if this has already been reported, I haven't been able
to keep up with the mailing list recently.

Thanks,
Ville

[Sqlrelay-discussion] SQL Relay 0.50 is out

[David M. <dav...@fi...>]

Hello all,

SQL Relay version 0.50 is now available.

This release mainly fixes a few issues relevant to users of Microsoft 
SQL Server.

The following client C++ API methods, equivalent methods and functions 
in other API's and equivalent commands in sqlrsh now work properly with 
SQL Server via FreeTDS:

     getDbVersion()
     getDatabaseList()
     getTableList()
     getColumnList()

The FreeTDS code has been updated as well. FreeTDS (and the TDS protocol 
in general) doesn't support fetching multiple result sets simultaneously 
through the same connection. Any attempt to do so results in the error: 
"Attempt to initiate a new Adaptive Server operation with results 
pending." SQL Relay has always attempted to work around this by caching 
the entire result set on the client by default but there was a subtle 
bug that could cause the error to still occur under certain 
circumstances. That bug has been fixed and everything now works as 
expected. I added an entry to the FAQ about it as well.

Parameters have been added to sqlrelay.conf for reformatting dates in 
the result set as well. Many databases provide limited or no control 
over the formatting of dates and times in the result set. This is 
problematic when migrating from one database to another if the databases 
have different date/time formats and the application expects a 
particular format. To help remedy this the datetimeformat, dateformat, 
timeformat and dateddmm parameters have been added to allow dates in the 
result set to be reformatted by SQL Relay before they are sent to the 
client.

Various other bug fixes and updates have also been made:

* fixed the sqlrserver-config script to return data for libsqlrserver 
rather than libsqlrconnection
* added a test for SQLROWSETSIZE for odbc that doesn't define it
* fixed a NULL dereference in custom_nw logger
* fixed an uninitialized variable (sqltr) in sqlrcontroller class
* fixed a memory leak in the freetds version-checking code
* added new search paths and default prefix for Syllable OS
* added support for null-terminated result bind lists to the mysql 
drop-in library so apps can optionally bind fewer columns than come back 
in the result set
* added configure tests for php-config-"version"

One thing that has not been addressed yet is support for Multiarch 
systems.  Debian and Ubuntu have recently started installing libraries 
for some modules under /usr/lib/<arch> where arch is something like 
i386-gnu-linux or x86_64-gnu-linux.  On 64 bit platforms, both 
directories exist with 32-bit libraries installed in 
/usr/lib/i386-gnu-linux and 64-bit libraries installed in 
/usr/lib/x86_64-gnu-linux.  On arm platforms it's even more complicated 
as there are both old-school and EABI directories as well as one for 
thumb and some other option as well.  Mips platforms are similarly 
complicated.  To make it more complex, the Debian/Ubuntu naming 
conventions don't match the output of the config.guess script.  I'm 
working on a solution but it didn't make it into this release. 
Unfortunately the only good solution for now is to symlink the 
appropriate libraries into /lib.

For example, to get FreeTDS to be detected and used, run:
cd /usr/lib
ln -s i386-gnu-linux/libct.so.4.0.0 libct.so.4.0.0
ln -s i386-gnu-linux/libct.so.4.0.0 libct.so.4
ln -s i386-gnu-linux/libct.so.4.0.0 libct.so

Or similar for the libraries of other packages.

Enjoy,

David Muse
dav...@fi...

Re: [Sqlrelay-discussion] Problem compiling sqlrelay 0.49.1 in Ubuntu server 12.04.2 LTS 64bits

[David M. <dav...@fi...>]

Hi Tomas,

Sorry for the late reply.  I have had several problems on Ubuntu 12.04 
and Debian Wheezy as well.  Ubuntu and Debian appear to be using a 
"multiarch" framework: http://wiki.debian.org/Multiarch

On a 64 bit machine, 32 bit libraries are installed under 
/usr/lib/i386-linux-gnu and 64 bit libraries are installed under 
/usr/lib/x86_64-linux-gnu or /usr/lib/amd64-linux-gnu.  For arm and mips 
platforms there are even more directories: 
http://wiki.debian.org/Multiarch/Tuples

All of this confounds the sqlrelay configure script.  You can get around 
it by running the configure script, then manually editing the config.mk 
file that it produces to point to the correct locations for the 
libraries and header files.

I'm not exactly sure how to fix the problem you're having, but I'm sure 
it has something to do with all of that.

I recently received a patch to support multiarch on x86_64 machines but 
I'm currently trying to figure out how to make the configure script 
support generic multiarch.  Hopefully I can figure it out soon.

Dave
dav...@fi...

On 2/10/2013 1:13 AM, Tomás Rafael Moreno Poggio wrote:
> Hi, i had problems compiling the sqlrelay the configure line is next:
>
> ./configure  --with-firebird-prefix=/usr/
> --with-java-prefix=/usr/local/java/jdk1.7.0_07/
>
> The screen output is for the conifgure command is in attachament file
> Configure_OutPut.txt
>
> I get the next error
>
> /bin/sh ../../libtool --mode=link g++  -o sqlr-start sqlr-start.lo
> -L./ -lsqlrserver -L../../src/util -lsqlrutil
> -L/usr/local/firstworks/lib -lrudiments -pthread /lib/
> -L/usr/lib/x86_64-linux-gnu -lpcre -lrt -lrt -lcrypt -ldl
> libtool: link: g++ -o .libs/sqlr-start .libs/sqlr-start.o -pthread
> /lib/  -L/usr/local/src/sqlrelay-0.49.1/src/server
> /usr/local/src/sqlrelay-0.49.1/src/server/.libs/libsqlrserver.so
> -L/usr/local/src/sqlrelay-0.49.1/src/util -L/usr/local/firstworks/lib
> /usr/local/src/sqlrelay-0.49.1/src/util/.libs/libsqlrutil.so
> /usr/local/firstworks/lib/librudiments.so -L/usr/lib/x86_64-linux-gnu
> -lpcre -lrt -lcrypt -ldl -pthread  -Wl,-rpath
> -Wl,/usr/local/firstworks/lib
> /usr/bin/ld: cannot find /lib/: File format not recognized
> collect2: ld returned 1 exit status
> make[2]: *** [sqlr-start] Error 1
> make[2]: se sale del directorio «/usr/local/src/sqlrelay-0.49.1/src/server»
> make[1]: *** [all] Error 2
> make[1]: se sale del directorio «/usr/local/src/sqlrelay-0.49.1/src»
> make: *** [all] Error 2
>
> I noticied from the problem for ubuntu and dash issued in the faq, i
> fix this follow the instruccions in the FAQ and also get the same
> error.
>
> Any idea to fix the compile error?
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>
>
>
> ------------------------------------------------------------------------------
> Free Next-Gen Firewall Hardware Offer
> Buy your Sophos next-gen firewall before the end March 2013
> and get the hardware for free! Learn more.
> http://p.sf.net/sfu/sophos-d2d-feb
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>                http://www.doteasy.com
>

[Sqlrelay-discussion] Problem compiling sqlrelay 0.49.1 in Ubuntu server 12.04.2 LTS 64bits

[Tomás R. M. P. <sir...@gm...>]

Hi, i had problems compiling the sqlrelay the configure line is next:

./configure  --with-firebird-prefix=/usr/
--with-java-prefix=/usr/local/java/jdk1.7.0_07/

The screen output is for the conifgure command is in attachament file
Configure_OutPut.txt

I get the next error

/bin/sh ../../libtool --mode=link g++  -o sqlr-start sqlr-start.lo
-L./ -lsqlrserver -L../../src/util -lsqlrutil
-L/usr/local/firstworks/lib -lrudiments -pthread /lib/
-L/usr/lib/x86_64-linux-gnu -lpcre -lrt -lrt -lcrypt -ldl
libtool: link: g++ -o .libs/sqlr-start .libs/sqlr-start.o -pthread
/lib/  -L/usr/local/src/sqlrelay-0.49.1/src/server
/usr/local/src/sqlrelay-0.49.1/src/server/.libs/libsqlrserver.so
-L/usr/local/src/sqlrelay-0.49.1/src/util -L/usr/local/firstworks/lib
/usr/local/src/sqlrelay-0.49.1/src/util/.libs/libsqlrutil.so
/usr/local/firstworks/lib/librudiments.so -L/usr/lib/x86_64-linux-gnu
-lpcre -lrt -lcrypt -ldl -pthread  -Wl,-rpath
-Wl,/usr/local/firstworks/lib
/usr/bin/ld: cannot find /lib/: File format not recognized
collect2: ld returned 1 exit status
make[2]: *** [sqlr-start] Error 1
make[2]: se sale del directorio «/usr/local/src/sqlrelay-0.49.1/src/server»
make[1]: *** [all] Error 2
make[1]: se sale del directorio «/usr/local/src/sqlrelay-0.49.1/src»
make: *** [all] Error 2

I noticied from the problem for ubuntu and dash issued in the faq, i
fix this follow the instruccions in the FAQ and also get the same
error.

Any idea to fix the compile error?

[Sqlrelay-discussion] 0.49.1 is out

[David M. <dav...@fi...>]

This release fixes two bugs:

* sqlrserver-config returns the proper library now
* there was a NULL-dereference in the custom_nw logger module

If you don't use either of those things then there's no need to upgrade.

Dave
dav...@fi...

Re: [Sqlrelay-discussion] Testing sqlrelay's drop-in replacement libraries on Debian Wheezy

[David M. <dav...@fi...>]

What version of psycopg2 is on Wheezy?

The attached code (also in CVS) should work with psycopg2-2.4.6 (and 
maybe other 2.4's)

However, there are some caveats...

Psycopg runs a begin query to start a transaction, so if the db you're 
using doesn't support begin queries (Oracle doesn't, for example) then 
you need to enable fake transaction blocks by adding 
faketransactionblocks=yes to the db connect string.

See: http://sqlrelay.sourceforge.net/sqlrelay/admin/configuring.html for 
more info on that.

Psycopg also wants the date to come back from the DB in YYYY-MM-DD 
format.  It checks the date format and if it's not "ISO" then it tries 
to set it to ISO using a "SET datetype TO 'ISO'" query, which works on 
postgresql but probably not on most databases.  The drop-in lib has been 
tweaked to return ISO no matter what, but if the DB isn't set to return 
dates in that format, then psycopg can't parse the date.  To work around 
this, you can change your database's default date format, or if you 
can't do that, you can use SQL Relay's "session query" feature to set 
the date format for the duration of the session.  For example, with Oracle:

<instance ...>
     ...
     </users>
         <session>
             <start>
                 <runquery>alter session set 
nls_date_format='YYYY-MM-DD'</runquery>
             </start>
             <end>
                 <runquery>alter session set 
nls_date_format='DD-MON-YYYY'</runquery>
             </end>
         </session>
     <connections>
     ...
</instance>


Another caveat is the client_encoding.  For now, the drop-in lib returns 
UTF8 when asked for the client_encoding, which may or may not be 
correct, depending on your set up.


The attached file won't work with Psycopg 2.2 if SQL Relay is aimed at a 
non-postgresql DB because 2.2 runs the "show client_encoding" query to 
get the client encoding, rather than calling PQparameterStatus, which 
non-postgredsql db's don't like.  I can't seem to get it working with a 
postgresql db either though, so something else must be wrong too.  Maybe 
I'll figure it out soon though.


Anyway, I only tried some simple tests but they worked.  Give it a try 
if you're using psycopg 2.4 and let me know if you run into any issues.

Dave
dav...@fi...


On 01/18/2013 03:12 PM, David Muse wrote:
> Interesting.  The mysql client for 5.5 doesn't appear to be against the
> libmysqlclient library at all:
>
> [dmuse@localhost mysql]$ mysql --version
> mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1
> [dmuse@localhost mysql]$ ldd /usr/bin/mysql
>      linux-vdso.so.1 =>  (0x00007fff22d33000)
>      libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003775200000)
>      libz.so.1 => /lib64/libz.so.1 (0x0000003775e00000)
>      librt.so.1 => /lib64/librt.so.1 (0x0000003775a00000)
>      libssl.so.10 => /lib64/libssl.so.10 (0x0000003785e00000)
>      libcrypto.so.10 => /lib64/libcrypto.so.10 (0x0000003783a00000)
>      libdl.so.2 => /lib64/libdl.so.2 (0x0000003775600000)
>      libncurses.so.5 => /lib64/libncurses.so.5 (0x0000003788600000)
>      libtinfo.so.5 => /lib64/libtinfo.so.5 (0x0000003785a00000)
>      libstdc++.so.6 => /lib64/libstdc++.so.6 (0x000000377e600000)
>      libm.so.6 => /lib64/libm.so.6 (0x0000003776200000)
>      libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000003778600000)
>      libc.so.6 => /lib64/libc.so.6 (0x0000003774e00000)
>      /lib64/ld-linux-x86-64.so.2 (0x0000003774a00000)
>      libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x0000003782e00000)
>      libkrb5.so.3 => /lib64/libkrb5.so.3 (0x0000003783200000)
>      libcom_err.so.2 => /lib64/libcom_err.so.2 (0x000000377fe00000)
>      libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x0000003783e00000)
>      libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x0000003783600000)
>      libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x0000003784600000)
>      libresolv.so.2 => /lib64/libresolv.so.2 (0x0000003776a00000)
>      libselinux.so.1 => /lib64/libselinux.so.1 (0x0000003776600000)
>
> or use any of the mysql client api functions:
>
> [dmuse@localhost mysql]$ ltrace -e "mysql_real_connect" mysql --host=db
> --user=test --password=test
> ERROR 1045 (28000): Access denied for user 'test'@'192.168.1.73' (using
> password: YES)
>
> 5.1 uses libmysqlclient though:
>
> [dmuse@fedora ~]$ mysql --version
> mysql  Ver 14.14 Distrib 5.1.60, for redhat-linux-gnu (i386) using
> readline 5.1
> [dmuse@fedora ~]$ ldd /usr/bin/mysql
>      linux-gate.so.1 =>  (0x0043e000)
>      libncursesw.so.5 => /lib/libncursesw.so.5 (0x00475000)
>      libtinfo.so.5 => /lib/libtinfo.so.5 (0x0033f000)
>      libpthread.so.0 => /lib/libpthread.so.0 (0x0079f000)
>      libmysqlclient.so.16 => /usr/lib/mysql/libmysqlclient.so.16
> (0x00110000)
>      libcrypt.so.1 => /lib/libcrypt.so.1 (0x00511000)
>      libnsl.so.1 => /lib/libnsl.so.1 (0x006ba000)
>      libssl.so.10 => /usr/lib/libssl.so.10 (0x00284000)
>      libcrypto.so.10 => /lib/libcrypto.so.10 (0x007ba000)
>      libz.so.1 => /lib/libz.so.1 (0x00e5c000)
>      libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x00d0e000)
>      libm.so.6 => /lib/libm.so.6 (0x00c26000)
>      libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00b1a000)
>      libc.so.6 => /lib/libc.so.6 (0x00946000)
>      libdl.so.2 => /lib/libdl.so.2 (0x002dc000)
>      /lib/ld-linux.so.2 (0x00c60000)
>      libfreebl3.so => /lib/libfreebl3.so (0x002e1000)
>      libgssapi_krb5.so.2 => /lib/libgssapi_krb5.so.2 (0x0035f000)
>      libkrb5.so.3 => /lib/libkrb5.so.3 (0x00541000)
>      libcom_err.so.2 => /lib/libcom_err.so.2 (0x0032e000)
>      libk5crypto.so.3 => /lib/libk5crypto.so.3 (0x00397000)
>      libresolv.so.2 => /lib/libresolv.so.2 (0x00ebc000)
>      libkrb5support.so.0 => /lib/libkrb5support.so.0 (0x0072e000)
>      libkeyutils.so.1 => /lib/libkeyutils.so.1 (0x0041e000)
>      libselinux.so.1 => /lib/libselinux.so.1 (0x00e97000)
>
> I'm not sure what they did in 5.5.  Maybe they static-linked and inlined
> the functions or something.  Strange.  That explains why it's not
> working though.  I would expect it to work with any program that uses
> libmysqlclient.  Try it with a third-party mysql app and see what happens.
>
> Dave
>
>
> On 01/18/2013 12:46 PM, David Muse wrote:
>> Hmmm.  The mysql 5.1 library should work with 5.5.  I have a client that
>> I think it using it with 5.5, but I could be wrong about that.  It's
>> possible that one of the MySQL internal structures changed between 5.1
>> and 5.5 and the code needs to be updated to handle that.  Take a look at
>> the file src/api/mysql/mysql.cpp.  Near the top there are several
>> ifdef's for different versions of MySQL.  Compare the structures defined
>> for 5.1 with the ones defined in the mysql headers for 5.5.  If the
>> sizes look different, then that could be the problem.  Otherwise, you'd
>> have to enable debug and trace through it.
>>
>> I just looked at the code for the postgres drop-in library and I don't
>> see a function that returns the protocol version, so that could be
>> tricky to solve.  It might be returned in a struct or there could be
>> some set of commands that get run or something.  I'll look into it.  It
>> should be possible to get it working in an upcoming release.
>>
>> Dave
>>
>> On 01/18/2013 12:23 PM, Petros Moisiadis wrote:
>>> I' ve tested sqlrelay's drop-in replacement libraries for MySQL and
>>> PostgreSQL on Debian Wheezy (x86_64).
>>>
>>> The drop-in for MySQL does not seem to work. It just hangs when trying
>>> to connect with the mysql client program. It asks for password and after
>>> entering it, it freezes without ever giving back a mysql prompt. Maybe
>>> this is related to the fact that Debian Wheezy comes with mysql 5.5, but
>>> sqlrelay provides libraries up to 5.1? If this is not relevant, I would
>>> be glad to do any tests needed to narrow down the possible causes of the
>>> problem.
>>>
>>> The drop-in replacement library for PostgreSQL works when using the psql
>>> client program.
>>> However, it is not usable if trying to connect with psycopg2, the most
>>> popular library to interface with postgres from python. In particular,
>>> psycopg2 checks for the protocol version of the connection and if it is
>>> not 3, it exits. Maybe sqlrelay's implementation of postgres api does
>>> not support protocol version requests. Dave, if that is the case, would
>>> it be easy to include support for this type of requests in a following
>>> release of sqlrelay?
>>>
>>> ------------------------------------------------------------------------------
>>>
>>>
>>> Master HTML5, CSS3, ASP.NET, MVC, AJAX, Knockout.js, Web API and
>>> much more. Get web development skills now with LearnDevNow -
>>> 350+ hours of step-by-step video tutorials by Microsoft MVPs and
>>> experts.
>>> SALE $99.99 this month only -- learn more at:
>>> http://p.sf.net/sfu/learnmore_122812
>>> _______________________________________________
>>> Sqlrelay-discussion mailing list
>>> Sql...@li...
>>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>>
>>>
>>> _______________________________________________________
>>> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>>>                http://www.doteasy.com
>>>