sqlrelay-discussion — Discussion of topics related to SQL Relay

Re: [Sqlrelay-discussion] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)

[a. k. <kl...@fd...>]

Renaud,

On Mon, 2008-02-11 at 18:05 -0800, Renaud Amar wrote:
> I wrote a small patch which fixes an issue I’ve noticed with the SQL
> Relay connection daemons for MySQL:
> 
> the connection daemon doesn’t automatically reconnect to the MySQL
> database once it’s been disconnected (either when restarting mysqld or
> when the connection times out). I’ve also noticed that after being
> disconnected, the connection daemon usually gets stuck in a weird loop
> where it keeps trying to unsuccessfully send a query over and over.

We have the same issue here, and I wanted to give it a shot.
> 
> After looking through the mysql connection daemon code, I noticed that
> the code that is supposed  to enable the  MYSQL_OPT_RECONNECT option
> (in mysqlconnection.h and mysqlconnection.C) was ifdef’ed using a
> different #define symbol as the one used in the config.h file.

> Namely, it’s using “#ifdef MYSQL_OPT_RECONNECT” while config.h defines
> “HAVE_MYSQL_OPT_RECONNECT”.

Thanks for the finding,

> This patch simply modifies mysqlconnection.h and mysqlconnection.C to
> use HAVE_MYSQL_OPT_RECONNECT.

but the patched source does perfectly compile on i386, but not on x86_64 arch.
It fails with following error:

make[3]: Leaving directory `src/connections/status'
make -C mysql all
make[3]: Entering directory `src/sqlrelay_patch/4/src/connections/mysql'
/bin/sh ../../../libtool --mode=compile g++ -Wall -pipe  -pthread  -I./
-I../../../ -I../../../src/common -I../../../src/util
-I../../../src/connection -I/usr/local/include -pthread
-I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2
-I../../../src/api/c++/include -c mysqlconnection.C -o
mysqlconnection.lo
mkdir .libs
 g++ -Wall -pipe -pthread -I./ -I../../../ -I../../../src/common
-I../../../src/util -I../../../src/connection -I/usr/local/include
-pthread -I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2
-I../../../src/api/c++/include -c mysqlconnection.C  -fPIC -DPIC
-o .libs/mysqlconnection.o
mysqlconnection.C:19: error: 'TRUE' was not declared in this scope
make[3]: *** [mysqlconnection.lo] Fehler 1
make[3]: Leaving directory `src/sqlrelay_patch/4/src/connections/mysql'
make[2]: *** [all] Fehler 2
make[2]: Leaving directory `src/sqlrelay_patch/4/src/connections'
make[1]: *** [all] Fehler 2
make[1]: Leaving directory `src/sqlrelay_patch/4/src'
make: *** [all] Fehler 2

Any suggestions on this?

regards,
k

Re: [Sqlrelay-discussion] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)

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

Cool.  I'll give it a try and should be able to include it in the next
release.

Thanks!

Dave Muse
dav...@fi...


On Mon, 2008-02-11 at 18:05 -0800, Renaud Amar wrote:
> I wrote a small patch which fixes an issue I’ve noticed with the SQL
> Relay connection daemons for MySQL:
> 
> the connection daemon doesn’t automatically reconnect to the MySQL
> database once it’s been disconnected (either when restarting mysqld or
> when the connection times out). I’ve also noticed that after being
> disconnected, the connection daemon usually gets stuck in a weird loop
> where it keeps trying to unsuccessfully send a query over and over.
> 
>  
> 
> After looking through the mysql connection daemon code, I noticed that
> the code that is supposed  to enable the  MYSQL_OPT_RECONNECT option
> (in mysqlconnection.h and mysqlconnection.C) was ifdef’ed using a
> different #define symbol as the one used in the config.h file.
> 
> Namely, it’s using “#ifdef MYSQL_OPT_RECONNECT” while config.h defines
> “HAVE_MYSQL_OPT_RECONNECT”.
> 
> This patch simply modifies mysqlconnection.h and mysqlconnection.C to
> use HAVE_MYSQL_OPT_RECONNECT.
> 
>  
> 
> This is probably the same issue Abraham Müller mentioned in a previous
> post (here:
> http://sourceforge.net/mailarchive/forum.php?thread_name=47739600.8090800%40gmx.de&forum_name=sqlrelay-discussion).
> 
> Hopefully, this patch will fix this issue as well.
> 
>  
> 
>  
> 
>  
> 
> Renaud.
> 
>  
> 
>  
> 
> PS: when applying the patch in your spec file, in the %prep section,
> make sure you call it with the –p1 option, in order to strip one level
> of directories from the paths in the patch file.
> 
>  
> 
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
> http://www.doteasy.com 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com

[Sqlrelay-discussion] Too many listeners

[John K. <kl...@gm...>]

Decided to punt on Solaris (my Solaris box appears to have issues)...
Built SQL Relay on linux, and it seems to perform pretty nicely.  One
thing we see, though, when we throw a lot of simultaneous connections
at it, is "Too many listeners".  That's from perl code that does this:

    die "ERROR: Couldn't prime the connection: " . $cursor->errorMessage()
        unless $cursor->sendQuery("select sysdate from dual");

Once this happens, SQL Relay is mostly toast.  With light load, you
can get data through it.  Once you up the load even slightly, it's
back to "Too many listeners".

My settings include:

            connections="5"
            maxconnections="50"
            maxqueuelength="2"
            growby="5"
            ttl="60"
            endofsession="rollback"
            sessiontimeout="600"
            runasuser="qddts"
            runasgroup="qddts"
            cursors="5"
            authtier="listener"
            handoff="pass"
            deniedips=""
            allowedips=""
            debug="none"
            maxquerysize="65536"
            maxstringbindvaluelength="4000"
            maxlobbindvaluelength="71680"
            idleclienttimeout="5"
            maxlisteners="5"
            listenertimeout="5"
            reloginatstart="false"

(This is actually the same error I saw on Solaris, but under a much lower load.)

Anyway, I searched this list, and this doesn't appear to have come up
in the past...

The box claims to be:

Linux linuxdb4 2.6.9-22.0.2.ELsmp #1 SMP Thu Jan 5 17:11:56 EST 2006
x86_64 x86_64 x86_64 GNU/Linux

Thanks.

Re: [Sqlrelay-discussion] getting stuck (Oracle)

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

The "acquiring announce mutex" thing is actually normal behavior.  One
of the sqlr-connection daemons will acquire the mutex and wait for the
listener to give it some work to do.  The others will wait for their
turn.  As soon as the sqlr-listener has given the sqlr-connection daemon
a query to run, another sqlr-connection daemon will acquire the mutex
and wait.  So, that part is normal.

Does it work as expected if connections="10" and maxconnections="10" and
just start failing when they're bumped up to 15?  If so, the only thing
I can think of offhand is that you've exhausted some resource like
memory or some semaphore-related resource or something.  What else do
you have running on the machine?  Also, a common error is to start up
too many cursors per connection, which can easily exhaust memory as each
allocates a pretty large fetch buffer.  See
http://sqlrelay.sourceforge.net/sqlrelay/tuning.html#memoryusage
for more info on that.

Also, send me your sqlrelay.conf file entries with the passwords XXX'ed
out, there might be something helpful in there.

Dave

On Mon, 2008-02-11 at 15:55 -0500, John Klassa wrote:
> I decided to try running just one instance of SQL Relay (rather than
> two, with one simply routing SELECTs to the other).  It seems to work
> now, so I suspect the issue was that the two instances were stepping
> on each other somehow.  I'm still digging...
> 
> On Feb 11, 2008 3:29 PM, John Klassa <kl...@gm...> wrote:
> > A previous thread on this list suggested that the listener(s) might
> > not be running...  They are.  For them, the last debug/log line reads:
> >
> > 02/11/2008 11:09:25 PST listener [7683] : waiting for client connection...
> >
> > Thanks.
> >
> >
> > On Feb 11, 2008 12:55 PM, John Klassa <kl...@gm...> wrote:
> > > Slight correction...  Clients can occasionally connect, and do a bit
> > > more work.  Perhaps something eventually times out and gives up?
> > > Regardless, most of the time, clients are just stuck, waiting, and the
> > > debug output looks largely the same (i.e. most of them ending in,
> > > "acquiring announce mutex").
> > >
> > >
> > > On Feb 11, 2008 12:51 PM, John Klassa <kl...@gm...> wrote:
> > > > I have a setup with two SQL Relay instances -- one just routes
> > > > "select" statements to the other, and drops anything else.  The
> > > > "other" is actually connected to the database, and does work.
> > > >
> > > > I've found that if I change the worker (the one that's actually
> > > > connected to the DB) from having:
> > > >
> > > >             connections="10"
> > > >             maxconnections="10"
> > > >
> > > > to having :
> > > >
> > > >             connections="15"
> > > >             maxconnections="15"
> > > >
> > > > then after I run just a few commands through SQL Relay, it hangs.
> > > > Clients can't connect, and no work gets done -- even though the SQL
> > > > Relay server and the database are sitting largely idle, doing nothing.
> > > >  No error messages anywhere (other than timeouts on the clients).
> > > > Nothing ever frees up, and SQL Relay never does useful work again,
> > > > until I restart it.
> > > >
> > > > The database user I'm connecting as has no simultaneous session limit,
> > > > according to my DBA.  So, I'm at a bit of a loss.  It's as though
> > > > something is getting locked, and blocking everything from then on.
> > > >
> > > > When I turn on debug, the connection debug files end like this (this
> > > > is the output of "tail -2" on each):
> > > >
> > > > 02/11/2008 09:44:15 PST connection [7638] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:43:35 PST connection [7640] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:11 PST connection [7642] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:24 PST connection [7644] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:14 PST connection [7646] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:13 PST connection [7648] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:18 PST connection [7650] :     waiting for listener
> > > >
> > > > 02/11/2008 09:44:15 PST connection [7652] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:43:43 PST connection [7661] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:11 PST connection [7663] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:10 PST connection [7665] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:18 PST connection [7667] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:13 PST connection [7669] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:17 PST connection [7671] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:18 PST connection [7673] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:18 PST connection [7685] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:12 PST connection [7687] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:14 PST connection [7689] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:19 PST connection [7691] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:13 PST connection [7693] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:13 PST connection [7699] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:14 PST connection [7701] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:18 PST connection [7703] :     waiting for listener
> > > >
> > > > 02/11/2008 09:44:16 PST connection [7708] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:17 PST connection [7710] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:18 PST connection [7712] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:14 PST connection [7732] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:10 PST connection [7737] :     acquiring announce mutex
> > > >
> > > > 02/11/2008 09:44:18 PST connection [7744] :
> > > > preparing/executing...
> > > >
> > > > 02/11/2008 09:44:12 PST connection [7753] :     acquiring announce mutex
> > > >
> > > > So it looks like they're all waiting to acquire the announce mutex.
> > > >
> > > > I tried building against Oracle 9 and Oracle 10, and get this same
> > > > result either way.
> > > >
> > > > (On that note, I need to post separately about Oracle 10.  I had to
> > > > finagle it to build, there.)
> > > >
> > > > Anyway, any thoughts on how to fix this "announce mutex" issue?
> > > >
> > > > Thanks!
> > > >
> > >
> >
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com

Re: [Sqlrelay-discussion] getting stuck (Oracle)

[John K. <kl...@gm...>]

I decided to try running just one instance of SQL Relay (rather than
two, with one simply routing SELECTs to the other).  It seems to work
now, so I suspect the issue was that the two instances were stepping
on each other somehow.  I'm still digging...

On Feb 11, 2008 3:29 PM, John Klassa <kl...@gm...> wrote:
> A previous thread on this list suggested that the listener(s) might
> not be running...  They are.  For them, the last debug/log line reads:
>
> 02/11/2008 11:09:25 PST listener [7683] : waiting for client connection...
>
> Thanks.
>
>
> On Feb 11, 2008 12:55 PM, John Klassa <kl...@gm...> wrote:
> > Slight correction...  Clients can occasionally connect, and do a bit
> > more work.  Perhaps something eventually times out and gives up?
> > Regardless, most of the time, clients are just stuck, waiting, and the
> > debug output looks largely the same (i.e. most of them ending in,
> > "acquiring announce mutex").
> >
> >
> > On Feb 11, 2008 12:51 PM, John Klassa <kl...@gm...> wrote:
> > > I have a setup with two SQL Relay instances -- one just routes
> > > "select" statements to the other, and drops anything else.  The
> > > "other" is actually connected to the database, and does work.
> > >
> > > I've found that if I change the worker (the one that's actually
> > > connected to the DB) from having:
> > >
> > >             connections="10"
> > >             maxconnections="10"
> > >
> > > to having :
> > >
> > >             connections="15"
> > >             maxconnections="15"
> > >
> > > then after I run just a few commands through SQL Relay, it hangs.
> > > Clients can't connect, and no work gets done -- even though the SQL
> > > Relay server and the database are sitting largely idle, doing nothing.
> > >  No error messages anywhere (other than timeouts on the clients).
> > > Nothing ever frees up, and SQL Relay never does useful work again,
> > > until I restart it.
> > >
> > > The database user I'm connecting as has no simultaneous session limit,
> > > according to my DBA.  So, I'm at a bit of a loss.  It's as though
> > > something is getting locked, and blocking everything from then on.
> > >
> > > When I turn on debug, the connection debug files end like this (this
> > > is the output of "tail -2" on each):
> > >
> > > 02/11/2008 09:44:15 PST connection [7638] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:43:35 PST connection [7640] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:11 PST connection [7642] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:24 PST connection [7644] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:14 PST connection [7646] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:13 PST connection [7648] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:18 PST connection [7650] :     waiting for listener
> > >
> > > 02/11/2008 09:44:15 PST connection [7652] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:43:43 PST connection [7661] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:11 PST connection [7663] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:10 PST connection [7665] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:18 PST connection [7667] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:13 PST connection [7669] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:17 PST connection [7671] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:18 PST connection [7673] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:18 PST connection [7685] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:12 PST connection [7687] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:14 PST connection [7689] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:19 PST connection [7691] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:13 PST connection [7693] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:13 PST connection [7699] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:14 PST connection [7701] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:18 PST connection [7703] :     waiting for listener
> > >
> > > 02/11/2008 09:44:16 PST connection [7708] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:17 PST connection [7710] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:18 PST connection [7712] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:14 PST connection [7732] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:10 PST connection [7737] :     acquiring announce mutex
> > >
> > > 02/11/2008 09:44:18 PST connection [7744] :
> > > preparing/executing...
> > >
> > > 02/11/2008 09:44:12 PST connection [7753] :     acquiring announce mutex
> > >
> > > So it looks like they're all waiting to acquire the announce mutex.
> > >
> > > I tried building against Oracle 9 and Oracle 10, and get this same
> > > result either way.
> > >
> > > (On that note, I need to post separately about Oracle 10.  I had to
> > > finagle it to build, there.)
> > >
> > > Anyway, any thoughts on how to fix this "announce mutex" issue?
> > >
> > > Thanks!
> > >
> >
>

Re: [Sqlrelay-discussion] getting stuck (Oracle)

[John K. <kl...@gm...>]

A previous thread on this list suggested that the listener(s) might
not be running...  They are.  For them, the last debug/log line reads:

02/11/2008 11:09:25 PST listener [7683] : waiting for client connection...

Thanks.

On Feb 11, 2008 12:55 PM, John Klassa <kl...@gm...> wrote:
> Slight correction...  Clients can occasionally connect, and do a bit
> more work.  Perhaps something eventually times out and gives up?
> Regardless, most of the time, clients are just stuck, waiting, and the
> debug output looks largely the same (i.e. most of them ending in,
> "acquiring announce mutex").
>
>
> On Feb 11, 2008 12:51 PM, John Klassa <kl...@gm...> wrote:
> > I have a setup with two SQL Relay instances -- one just routes
> > "select" statements to the other, and drops anything else.  The
> > "other" is actually connected to the database, and does work.
> >
> > I've found that if I change the worker (the one that's actually
> > connected to the DB) from having:
> >
> >             connections="10"
> >             maxconnections="10"
> >
> > to having :
> >
> >             connections="15"
> >             maxconnections="15"
> >
> > then after I run just a few commands through SQL Relay, it hangs.
> > Clients can't connect, and no work gets done -- even though the SQL
> > Relay server and the database are sitting largely idle, doing nothing.
> >  No error messages anywhere (other than timeouts on the clients).
> > Nothing ever frees up, and SQL Relay never does useful work again,
> > until I restart it.
> >
> > The database user I'm connecting as has no simultaneous session limit,
> > according to my DBA.  So, I'm at a bit of a loss.  It's as though
> > something is getting locked, and blocking everything from then on.
> >
> > When I turn on debug, the connection debug files end like this (this
> > is the output of "tail -2" on each):
> >
> > 02/11/2008 09:44:15 PST connection [7638] :     acquiring announce mutex
> >
> > 02/11/2008 09:43:35 PST connection [7640] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:11 PST connection [7642] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:24 PST connection [7644] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:14 PST connection [7646] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:13 PST connection [7648] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:18 PST connection [7650] :     waiting for listener
> >
> > 02/11/2008 09:44:15 PST connection [7652] :     acquiring announce mutex
> >
> > 02/11/2008 09:43:43 PST connection [7661] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:11 PST connection [7663] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:10 PST connection [7665] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:18 PST connection [7667] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:13 PST connection [7669] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:17 PST connection [7671] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:18 PST connection [7673] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:18 PST connection [7685] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:12 PST connection [7687] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:14 PST connection [7689] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:19 PST connection [7691] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:13 PST connection [7693] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:13 PST connection [7699] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:14 PST connection [7701] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:18 PST connection [7703] :     waiting for listener
> >
> > 02/11/2008 09:44:16 PST connection [7708] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:17 PST connection [7710] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:18 PST connection [7712] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:14 PST connection [7732] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:10 PST connection [7737] :     acquiring announce mutex
> >
> > 02/11/2008 09:44:18 PST connection [7744] :
> > preparing/executing...
> >
> > 02/11/2008 09:44:12 PST connection [7753] :     acquiring announce mutex
> >
> > So it looks like they're all waiting to acquire the announce mutex.
> >
> > I tried building against Oracle 9 and Oracle 10, and get this same
> > result either way.
> >
> > (On that note, I need to post separately about Oracle 10.  I had to
> > finagle it to build, there.)
> >
> > Anyway, any thoughts on how to fix this "announce mutex" issue?
> >
> > Thanks!
> >
>

Re: [Sqlrelay-discussion] getting stuck (Oracle)

[John K. <kl...@gm...>]

Slight correction...  Clients can occasionally connect, and do a bit
more work.  Perhaps something eventually times out and gives up?
Regardless, most of the time, clients are just stuck, waiting, and the
debug output looks largely the same (i.e. most of them ending in,
"acquiring announce mutex").

On Feb 11, 2008 12:51 PM, John Klassa <kl...@gm...> wrote:
> I have a setup with two SQL Relay instances -- one just routes
> "select" statements to the other, and drops anything else.  The
> "other" is actually connected to the database, and does work.
>
> I've found that if I change the worker (the one that's actually
> connected to the DB) from having:
>
>             connections="10"
>             maxconnections="10"
>
> to having :
>
>             connections="15"
>             maxconnections="15"
>
> then after I run just a few commands through SQL Relay, it hangs.
> Clients can't connect, and no work gets done -- even though the SQL
> Relay server and the database are sitting largely idle, doing nothing.
>  No error messages anywhere (other than timeouts on the clients).
> Nothing ever frees up, and SQL Relay never does useful work again,
> until I restart it.
>
> The database user I'm connecting as has no simultaneous session limit,
> according to my DBA.  So, I'm at a bit of a loss.  It's as though
> something is getting locked, and blocking everything from then on.
>
> When I turn on debug, the connection debug files end like this (this
> is the output of "tail -2" on each):
>
> 02/11/2008 09:44:15 PST connection [7638] :     acquiring announce mutex
>
> 02/11/2008 09:43:35 PST connection [7640] :     acquiring announce mutex
>
> 02/11/2008 09:44:11 PST connection [7642] :     acquiring announce mutex
>
> 02/11/2008 09:44:24 PST connection [7644] :     acquiring announce mutex
>
> 02/11/2008 09:44:14 PST connection [7646] :     acquiring announce mutex
>
> 02/11/2008 09:44:13 PST connection [7648] :     acquiring announce mutex
>
> 02/11/2008 09:44:18 PST connection [7650] :     waiting for listener
>
> 02/11/2008 09:44:15 PST connection [7652] :     acquiring announce mutex
>
> 02/11/2008 09:43:43 PST connection [7661] :     acquiring announce mutex
>
> 02/11/2008 09:44:11 PST connection [7663] :     acquiring announce mutex
>
> 02/11/2008 09:44:10 PST connection [7665] :     acquiring announce mutex
>
> 02/11/2008 09:44:18 PST connection [7667] :     acquiring announce mutex
>
> 02/11/2008 09:44:13 PST connection [7669] :     acquiring announce mutex
>
> 02/11/2008 09:44:17 PST connection [7671] :     acquiring announce mutex
>
> 02/11/2008 09:44:18 PST connection [7673] :     acquiring announce mutex
>
> 02/11/2008 09:44:18 PST connection [7685] :     acquiring announce mutex
>
> 02/11/2008 09:44:12 PST connection [7687] :     acquiring announce mutex
>
> 02/11/2008 09:44:14 PST connection [7689] :     acquiring announce mutex
>
> 02/11/2008 09:44:19 PST connection [7691] :     acquiring announce mutex
>
> 02/11/2008 09:44:13 PST connection [7693] :     acquiring announce mutex
>
> 02/11/2008 09:44:13 PST connection [7699] :     acquiring announce mutex
>
> 02/11/2008 09:44:14 PST connection [7701] :     acquiring announce mutex
>
> 02/11/2008 09:44:18 PST connection [7703] :     waiting for listener
>
> 02/11/2008 09:44:16 PST connection [7708] :     acquiring announce mutex
>
> 02/11/2008 09:44:17 PST connection [7710] :     acquiring announce mutex
>
> 02/11/2008 09:44:18 PST connection [7712] :     acquiring announce mutex
>
> 02/11/2008 09:44:14 PST connection [7732] :     acquiring announce mutex
>
> 02/11/2008 09:44:10 PST connection [7737] :     acquiring announce mutex
>
> 02/11/2008 09:44:18 PST connection [7744] :
> preparing/executing...
>
> 02/11/2008 09:44:12 PST connection [7753] :     acquiring announce mutex
>
> So it looks like they're all waiting to acquire the announce mutex.
>
> I tried building against Oracle 9 and Oracle 10, and get this same
> result either way.
>
> (On that note, I need to post separately about Oracle 10.  I had to
> finagle it to build, there.)
>
> Anyway, any thoughts on how to fix this "announce mutex" issue?
>
> Thanks!
>

[Sqlrelay-discussion] getting stuck (Oracle)

[John K. <kl...@gm...>]

I have a setup with two SQL Relay instances -- one just routes
"select" statements to the other, and drops anything else.  The
"other" is actually connected to the database, and does work.

I've found that if I change the worker (the one that's actually
connected to the DB) from having:

            connections="10"
            maxconnections="10"

to having :

            connections="15"
            maxconnections="15"

then after I run just a few commands through SQL Relay, it hangs.
Clients can't connect, and no work gets done -- even though the SQL
Relay server and the database are sitting largely idle, doing nothing.
 No error messages anywhere (other than timeouts on the clients).
Nothing ever frees up, and SQL Relay never does useful work again,
until I restart it.

The database user I'm connecting as has no simultaneous session limit,
according to my DBA.  So, I'm at a bit of a loss.  It's as though
something is getting locked, and blocking everything from then on.

When I turn on debug, the connection debug files end like this (this
is the output of "tail -2" on each):

02/11/2008 09:44:15 PST connection [7638] :     acquiring announce mutex

02/11/2008 09:43:35 PST connection [7640] :     acquiring announce mutex

02/11/2008 09:44:11 PST connection [7642] :     acquiring announce mutex

02/11/2008 09:44:24 PST connection [7644] :     acquiring announce mutex

02/11/2008 09:44:14 PST connection [7646] :     acquiring announce mutex

02/11/2008 09:44:13 PST connection [7648] :     acquiring announce mutex

02/11/2008 09:44:18 PST connection [7650] :     waiting for listener

02/11/2008 09:44:15 PST connection [7652] :     acquiring announce mutex

02/11/2008 09:43:43 PST connection [7661] :     acquiring announce mutex

02/11/2008 09:44:11 PST connection [7663] :     acquiring announce mutex

02/11/2008 09:44:10 PST connection [7665] :     acquiring announce mutex

02/11/2008 09:44:18 PST connection [7667] :     acquiring announce mutex

02/11/2008 09:44:13 PST connection [7669] :     acquiring announce mutex

02/11/2008 09:44:17 PST connection [7671] :     acquiring announce mutex

02/11/2008 09:44:18 PST connection [7673] :     acquiring announce mutex

02/11/2008 09:44:18 PST connection [7685] :     acquiring announce mutex

02/11/2008 09:44:12 PST connection [7687] :     acquiring announce mutex

02/11/2008 09:44:14 PST connection [7689] :     acquiring announce mutex

02/11/2008 09:44:19 PST connection [7691] :     acquiring announce mutex

02/11/2008 09:44:13 PST connection [7693] :     acquiring announce mutex

02/11/2008 09:44:13 PST connection [7699] :     acquiring announce mutex

02/11/2008 09:44:14 PST connection [7701] :     acquiring announce mutex

02/11/2008 09:44:18 PST connection [7703] :     waiting for listener

02/11/2008 09:44:16 PST connection [7708] :     acquiring announce mutex

02/11/2008 09:44:17 PST connection [7710] :     acquiring announce mutex

02/11/2008 09:44:18 PST connection [7712] :     acquiring announce mutex

02/11/2008 09:44:14 PST connection [7732] :     acquiring announce mutex

02/11/2008 09:44:10 PST connection [7737] :     acquiring announce mutex

02/11/2008 09:44:18 PST connection [7744] :
preparing/executing...

02/11/2008 09:44:12 PST connection [7753] :     acquiring announce mutex

So it looks like they're all waiting to acquire the announce mutex.

I tried building against Oracle 9 and Oracle 10, and get this same
result either way.

(On that note, I need to post separately about Oracle 10.  I had to
finagle it to build, there.)

Anyway, any thoughts on how to fix this "announce mutex" issue?

Thanks!

Re: [Sqlrelay-discussion] Hibernate & sqlrelay

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

Actually, I'm working on that right now.  SQL Relay doesn't yet have a
JDBC driver.  I started working on it once, but writing a JDBC driver is
very complicated.  There is an ODBC driver for JDBC though, and ODBC is
much easier to code for.  So, I'm currently working on an ODBC driver
for SQL Relay which will enable it to work with both ODBC and JDBC
applications, including Hibernate.  The code is in CVS, but it's not
complete yet.  It "mostly works", and supports features that most people
are likely to use but does not fully support the ODBC standard yet.

There is another alternative though.  If you can find a version of the
JDBC driver for mysql that isn't pure Java, ie. that uses libmysqlclient
natively, then you can use the drop-in replacement library for mysql as
described in:
http://sqlrelay.sourceforge.net/sqlrelay/dropin/mysql.html
And configure SQL Relay to talk to your mysql database.

But the last time I looked for a driver like that I couldn't find it.
I'm sure that at one time such a thing existed, but it's since
disappeared off of the web.

Another, much more highly contrived alternative is to find a version of
the JDBC driver for postgresql that uses libpq natively and use the
drop-in replacement library for postgresql as described in:
http://sqlrelay.sourceforge.net/sqlrelay/dropin/postgresql.html
And configure SQL Relay to talk to your mysql database.

I believe that the JDBC driver that uses the native postgresql client
library is available in the older or archived code on the postgresql
site.

The postgresql route is the most likely to work.  This is the chain now:

your app -> hibernate -> jdbc -> mysql db

and when using the postgres lib, this would be the chain:

your app -> hibernate -> jdbc -> drop-in-replacement-lib-for-postgres ->
	libsqlrclient -> sqlrelay server -> libmysqlclient -> mysql db

It looks weird, but it should work.

Let me know what you think :)

Dave
dav...@fi...

On Mon, 2008-02-11 at 06:45 +0200, Craig Main wrote:
> Hi all,
> 
> I have a jboss application that uses hibernate for connection pooling
> which in turn uses jdbc to connect to a mysql database on a db server. I
> would like to start using sqlrelay to better utilize our
> backup/replicated database for select queries etc while the primary db
> is used for inserts and updates.
> 
> Is there a way to incorporate sqlrelay into this scenario. I don't want
> to rewrite the java code to connect directly with sqlrelay.
> 
> Any help will be appreciated.
> 
> Regards
> Craig Main
> 
> 
> To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser: 
> https://www.fnb.co.za/disclaimer.html 
> 
> If you are unable to access the Disclaimer, send a blank e-mail to
> fir...@fn... and we will send you a copy of the Disclaimer.
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com

[Sqlrelay-discussion] Hibernate & sqlrelay

[Craig M. <cm...@fn...>]

Hi all,

I have a jboss application that uses hibernate for connection pooling
which in turn uses jdbc to connect to a mysql database on a db server. I
would like to start using sqlrelay to better utilize our
backup/replicated database for select queries etc while the primary db
is used for inserts and updates.

Is there a way to incorporate sqlrelay into this scenario. I don't want
to rewrite the java code to connect directly with sqlrelay.

Any help will be appreciated.

Regards
Craig Main


To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser: 
https://www.fnb.co.za/disclaimer.html 

If you are unable to access the Disclaimer, send a blank e-mail to
fir...@fn... and we will send you a copy of the Disclaimer.

Re: [Sqlrelay-discussion] Bug report, open client connections not freed, with code to reproduce problem

[Martin F. <mar...@po...>]

Hello David,

Just an update for this reported bug: we have commented the line 1658 
(with "OCIHandleFree(def[i],OCI_HTYPE_DEFINE);") and haven't had any 
problems since, we are using the code in production enviroment for 2 
months. So I'd say: you can safely update the sourcecode of sqlrelay, to 
prevent the connection-crash-bug that was bugging us.

Regards,

Martin Fleurke

Ingmar Brouns schreef:
> Hi David,
>
> I have been trying to debug what's going on. The connection process
> crashes on this statement
>
>                 OCIHandleFree(def[i],OCI_HTYPE_DEFINE);
>
> line 1744 of oracle8connection.C with a segmentation fault. I've not
> been able to figer out why this happens. I am new to C++ and GDB and I
> cannot view the contents of the OCIDefine object. However, when I
> looked into the Oracle Call Interface Programmer's Guide
>
> http://www.ewp.rpi.edu/hartford/webgen//appdocs/oracle/doc/appdev.920/a96584/oci02bas.htm
>
> it said the following:
>
> OCI_HTYPE_DEFINE = OCI define handle
>
> and
>
> Bind and define handles are freed when the statement handle is freed
> or when a new statement is prepared on the statement handle.
>
> So my question is, why is it being freed explicitly anyway? I've
> compiled sqlrelay with the statement commented out, and now everything
> seems to be going fine. For what situations is the
> OCIHandleFree(def[i],OCI_HTYPE_DEFINE) statement neccesary?
>
> Anxiously awaiting your reply,
>
> Ingmar
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>   

[Sqlrelay-discussion] Hibernate & sqlrelay

[Main, C. <CM...@fn...>]

Hi all,

I have a jboss application that uses hibernate for connection pooling which in turn uses jdbc to connect to a mysql database on a db server. I would like to start using sqlrelay to better utilize our backup/replicated database for select queries etc while the primary db is used for inserts and updates.

Is there a way to incorporate sqlrelay into this scenario. I don't want to rewrite the java code to connect directly with sqlrelay.

Any help will be appreciated.

Regards
Craig Main

To read FirstRand Bank's Disclaimer for this email click on the following address or copy into your Internet browser: 
https://www.fnb.co.za/disclaimer.html 

If you are unable to access the Disclaimer, send a blank e-mail to
fir...@fn... and we will send you a copy of the Disclaimer.

Re: [Sqlrelay-discussion] adabas_d & odbc

[Marie F. <ma...@vt...>]

Well, it doesn't seem to be quite odbc 2.0 also - it returned 
SQL_INVALID_HANDLE for SQLColAttributes (odbc 2.0) but seems to be fine with 
SQLColAttribute (odbc 3.0).
Even stranger, the readme.txt says "ODBC Standard 3.5 Core Level 1".
Is there someone else out there using sqlrelay with adabas?

About SQL_COLUMN_AUTO_UNIQUE_VALUE:
I'm using unixODBC 2.2.12, which #defines SQL_DESC_AUTO_UNIQUE_VALUE
in sqlext.h as SQL_COLUMN_AUTO_INCREMENT, but has nothing in the source 
about SQL_COLUMN_AUTO_UNIQUE_VALUE.

-- 
marie

----- Original Message ----- 
From: "David Muse" <dav...@fi...>
To: "Discussion of topics related to SQL Relay" 
<sql...@li...>
Sent: Friday, February 08, 2008 4:59 PM
Subject: Re: [Sqlrelay-discussion] adabas_d & odbc


> That is unusual.  I imagine that it means that adabas's odbc driver
> doesn't implement the newer ODBC API.  It would be good if SQL Relay
> could detect this or at least give you an option in the config file to
> select whether to use the odbc 3.0 or 2.0 api.
>
> I'm not sure what's going on with SQL_COLUMN_AUTO_UNIQUE_VALUE though,
> what version of unixODBC are you using?
>
> David Muse
> dav...@fi...
>
>
> On Fri, 2008-02-08 at 12:04 +0200, Marie Fischer wrote:
>> Hello all,
>>
>> I have been trying to get sqlrelay to work with an Adabas_D database
>> (version 11 or 12)through ODBC connection (unixODBC). I could access the
>> database with isql and perl DBD::Adabas module, but would get segfaults 
>> with
>> sqlrelay or even the odbctest from sqlrelay/bench. Sqlrelay worked fine 
>> with
>> MySQL over ODBC connection.
>>
>> Finally, by trial and error, I seem to have gotten a working setup. I
>> commented out the code between "#if (ODBCVER >= 0x0300)" and "#else" 
>> (didn't
>> want to mess with #defines) in odbcconnection.C - thus using the code for
>> older ODBCVER. The only exception was in odbccursor::executeQuery, where 
>> I
>> had to use SQLColAttribute instead of SQLColAttributes.
>>
>> I guess this is just some quirk of Adabas's odbclib, but maybe my 
>> experience
>> is helpful to someone else struggling with the same problem.
>>
>> BTW, there seems to be a small bug in odbccursor::executeQuery, at least 
>> my
>> compiler complained about not knowing SQL_COLUMN_AUTO_UNIQUE_VALUE.
>> @@ -846,7 +846,7 @@
>>                         // autoincrement
>>  #ifdef SQL_DESC_AUTO_UNIQUE_VALUE
>>                         erg=SQLColAttributes(stmt,i+1,
>> -                                       SQL_COLUMN_AUTO_UNIQUE_VALUE,
>> +                                       SQL_DESC_AUTO_UNIQUE_VALUE,
>>                                         NULL,0,NULL,
>>                                         (SQLINTEGER
>> *)&(col[i].autoincrement));
>>                         if (erg!=SQL_SUCCESS && 
>> erg!=SQL_SUCCESS_WITH_INFO)
>> {
>>
>> rgds,
>>
>
>
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>             http://www.doteasy.com
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] adabas_d & odbc

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

That is unusual.  I imagine that it means that adabas's odbc driver
doesn't implement the newer ODBC API.  It would be good if SQL Relay
could detect this or at least give you an option in the config file to
select whether to use the odbc 3.0 or 2.0 api.

I'm not sure what's going on with SQL_COLUMN_AUTO_UNIQUE_VALUE though,
what version of unixODBC are you using?

David Muse
dav...@fi...


On Fri, 2008-02-08 at 12:04 +0200, Marie Fischer wrote:
> Hello all,
> 
> I have been trying to get sqlrelay to work with an Adabas_D database 
> (version 11 or 12)through ODBC connection (unixODBC). I could access the 
> database with isql and perl DBD::Adabas module, but would get segfaults with 
> sqlrelay or even the odbctest from sqlrelay/bench. Sqlrelay worked fine with 
> MySQL over ODBC connection.
> 
> Finally, by trial and error, I seem to have gotten a working setup. I 
> commented out the code between "#if (ODBCVER >= 0x0300)" and "#else" (didn't 
> want to mess with #defines) in odbcconnection.C - thus using the code for 
> older ODBCVER. The only exception was in odbccursor::executeQuery, where I 
> had to use SQLColAttribute instead of SQLColAttributes.
> 
> I guess this is just some quirk of Adabas's odbclib, but maybe my experience 
> is helpful to someone else struggling with the same problem.
> 
> BTW, there seems to be a small bug in odbccursor::executeQuery, at least my 
> compiler complained about not knowing SQL_COLUMN_AUTO_UNIQUE_VALUE.
> @@ -846,7 +846,7 @@
>                         // autoincrement
>  #ifdef SQL_DESC_AUTO_UNIQUE_VALUE
>                         erg=SQLColAttributes(stmt,i+1,
> -                                       SQL_COLUMN_AUTO_UNIQUE_VALUE,
> +                                       SQL_DESC_AUTO_UNIQUE_VALUE,
>                                         NULL,0,NULL,
>                                         (SQLINTEGER 
> *)&(col[i].autoincrement));
>                         if (erg!=SQL_SUCCESS && erg!=SQL_SUCCESS_WITH_INFO) 
> {
> 
> rgds,
> 



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com

[Sqlrelay-discussion] adabas_d & odbc

[Marie F. <ma...@vt...>]

Hello all,

I have been trying to get sqlrelay to work with an Adabas_D database 
(version 11 or 12)through ODBC connection (unixODBC). I could access the 
database with isql and perl DBD::Adabas module, but would get segfaults with 
sqlrelay or even the odbctest from sqlrelay/bench. Sqlrelay worked fine with 
MySQL over ODBC connection.

Finally, by trial and error, I seem to have gotten a working setup. I 
commented out the code between "#if (ODBCVER >= 0x0300)" and "#else" (didn't 
want to mess with #defines) in odbcconnection.C - thus using the code for 
older ODBCVER. The only exception was in odbccursor::executeQuery, where I 
had to use SQLColAttribute instead of SQLColAttributes.

I guess this is just some quirk of Adabas's odbclib, but maybe my experience 
is helpful to someone else struggling with the same problem.

BTW, there seems to be a small bug in odbccursor::executeQuery, at least my 
compiler complained about not knowing SQL_COLUMN_AUTO_UNIQUE_VALUE.
@@ -846,7 +846,7 @@
                        // autoincrement
 #ifdef SQL_DESC_AUTO_UNIQUE_VALUE
                        erg=SQLColAttributes(stmt,i+1,
-                                       SQL_COLUMN_AUTO_UNIQUE_VALUE,
+                                       SQL_DESC_AUTO_UNIQUE_VALUE,
                                        NULL,0,NULL,
                                        (SQLINTEGER 
*)&(col[i].autoincrement));
                        if (erg!=SQL_SUCCESS && erg!=SQL_SUCCESS_WITH_INFO) 
{

rgds,

-- 
marie 

Re: [Sqlrelay-discussion] Couldn't attach to shared memory segment: Permission denied

[John K. <kl...@gm...>]

Hi Dave,

Are you referring to the files in var/sqlrelay/tmp/ipc?  I actually
manually chmod'd those to 666, and still have the issue.

Or, are you saying that the permissions *at the time of creation* are
the issue (such that chmod'ing after the fact has no real effect)?

I'll give your code change a try, regardless.

Thanks,
JK

On Feb 5, 2008 5:32 PM, David Muse <dav...@fi...> wrote:
> Hmm, I never ran into the group log-in issue, I'll have to look into
> that.  The sqlr-status issue is simpler though.  The shared memory
> segment is created with rw----- (600) permissions so that the owner can
> read/write it but no one else has any access to it.  I guess it would
> make sense to make it at least rw-r---- (640).
>
> A quick fix would be to edit src/listener/sqlrlistener.C and modify the
> lines:
>
> if (!idmemory->create(key,sizeof(shmdata),
>                                 permissions::ownerReadWrite())) {
>
> to:
>
> if (!idmemory->create(key,sizeof(shmdata),
>                         permissions::evalPermString("rw-r----"))) {
>
> Then run make and make install in that directory.
>
> That should take care of it.  I'll put that fix in the next release.
>
> Dave
>
>
>
> On Tue, 2008-02-05 at 13:29 -0500, John Klassa wrote:
> > Trying to run SQL Relay in an environment where a particular group (in
> > the Unix group sense) have the ability to administer and check on SQL
> > Relay.  I'm running into a problem, though.
> >
> > I've got runasgroup set to the group of interest (call it "xyz"), in
> > the config file.  I'm also doing a "newgrp xyz" prior to launching SQL
> > Relay...  I discovered that setting "runasgroup" doesn't work unless
> > you're logged into that group...
> >
> > Anyway, when other folks in the group try to run sqlr-status, they get:
> >
> > Couldn't attach to shared memory segment: Permission denied
> >
> > I've tried chmoding the directories and files down to var/sqlrelay/tmp
> > so that they're group readable and writable.  I even tried world
> > readable/writable at one point, just to see.
> >
> > What am I missing?
> >
> > Thanks,
> > JK
> >
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2008.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> >
> > __________________________________________________
> > D O T E A S Y - "Join the web hosting revolution!"
> >              http://www.doteasy.com
>
>
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

Re: [Sqlrelay-discussion] Couldn't attach to shared memory segment: Permission denied

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

Hmm, I never ran into the group log-in issue, I'll have to look into
that.  The sqlr-status issue is simpler though.  The shared memory
segment is created with rw----- (600) permissions so that the owner can
read/write it but no one else has any access to it.  I guess it would
make sense to make it at least rw-r---- (640).

A quick fix would be to edit src/listener/sqlrlistener.C and modify the
lines:

if (!idmemory->create(key,sizeof(shmdata),
				permissions::ownerReadWrite())) {

to:

if (!idmemory->create(key,sizeof(shmdata),
			permissions::evalPermString("rw-r----"))) {

Then run make and make install in that directory.

That should take care of it.  I'll put that fix in the next release.

Dave


On Tue, 2008-02-05 at 13:29 -0500, John Klassa wrote:
> Trying to run SQL Relay in an environment where a particular group (in
> the Unix group sense) have the ability to administer and check on SQL
> Relay.  I'm running into a problem, though.
> 
> I've got runasgroup set to the group of interest (call it "xyz"), in
> the config file.  I'm also doing a "newgrp xyz" prior to launching SQL
> Relay...  I discovered that setting "runasgroup" doesn't work unless
> you're logged into that group...
> 
> Anyway, when other folks in the group try to run sqlr-status, they get:
> 
> Couldn't attach to shared memory segment: Permission denied
> 
> I've tried chmoding the directories and files down to var/sqlrelay/tmp
> so that they're group readable and writable.  I even tried world
> readable/writable at one point, just to see.
> 
> What am I missing?
> 
> Thanks,
> JK
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com

[Sqlrelay-discussion] Couldn't attach to shared memory segment: Permission denied

[John K. <kl...@gm...>]

Trying to run SQL Relay in an environment where a particular group (in
the Unix group sense) have the ability to administer and check on SQL
Relay.  I'm running into a problem, though.

I've got runasgroup set to the group of interest (call it "xyz"), in
the config file.  I'm also doing a "newgrp xyz" prior to launching SQL
Relay...  I discovered that setting "runasgroup" doesn't work unless
you're logged into that group...

Anyway, when other folks in the group try to run sqlr-status, they get:

Couldn't attach to shared memory segment: Permission denied

I've tried chmoding the directories and files down to var/sqlrelay/tmp
so that they're group readable and writable.  I even tried world
readable/writable at one point, just to see.

What am I missing?

Thanks,
JK

Re: [Sqlrelay-discussion] case-insensitive router/filter settings

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

The only gotcha that I can think of is that (?i) works with pcre, but
with posix regular expressions there's no way to do it within the
expression so it won't be portable.

A more portable, though imperfect solution would be something like:

^\s*(select|SELECT|Select)\s+.*

imperfect though because it doesn't cover all possible permutations,
just ones that whoever's writing the queries are likely to use.

I'd personally use the (?i)

Dave
dav...@fi...


On Mon, 2008-02-04 at 11:15 -0500, John Klassa wrote:
> I noticed that the pattern for a route/filter is case-sensitive...
> That is, if you specify:
> 
>         <query pattern="^\s*select\s+.*"/>
> 
> then only something like "select x from y" will get through; doing
> "SELECT x from y" will *not*.
> 
> Since the system uses PCRE, I tried the perl approach and did:
> 
>         <query pattern="(?i)^\s*select\s+.*"/>
> 
> This seems to fix it, so that "select" and "SELECT" both work nicely.
> Just curious as to whether this is the intended way to do this?  Any
> unforeseen gotchas?
> 
> Thanks,
> JK
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com

[Sqlrelay-discussion] case-insensitive router/filter settings

[John K. <kl...@gm...>]

I noticed that the pattern for a route/filter is case-sensitive...
That is, if you specify:

        <query pattern="^\s*select\s+.*"/>

then only something like "select x from y" will get through; doing
"SELECT x from y" will *not*.

Since the system uses PCRE, I tried the perl approach and did:

        <query pattern="(?i)^\s*select\s+.*"/>

This seems to fix it, so that "select" and "SELECT" both work nicely.
Just curious as to whether this is the intended way to do this?  Any
unforeseen gotchas?

Thanks,
JK

Re: [Sqlrelay-discussion] large-scale deployment?

[John K. <kl...@gm...>]

You betcha. :-)

Thanks!

On Feb 1, 2008 3:35 PM, Denis Chapligin <aka...@gm...> wrote:
> Hi
>
> 2008/1/30, John Klassa <kl...@gm...>:
> > Hi,
> > Anyway, before I get too excited about SQL Relay, can anyone share a
> > success story, where SQL Relay was used in an environment this large?
>
>
>
> Our billing system maintains about 400 connections with 750
> connections at peak load. Every connection is processing about 30
> requests per second
>
>
> 30x400=12000 requests per second. I have several sqlrelay instances,
> runinning on separate hardware and a partitioned db2 database.  Is it
> large enough to prove that SQLRelay is scalable? :)
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

Re: [Sqlrelay-discussion] large-scale deployment?

[Denis C. <aka...@gm...>]

Hi

2008/1/30, John Klassa <kl...@gm...>:
> Hi,
> Anyway, before I get too excited about SQL Relay, can anyone share a
> success story, where SQL Relay was used in an environment this large?



Our billing system maintains about 400 connections with 750
connections at peak load. Every connection is processing about 30
requests per second


30x400=12000 requests per second. I have several sqlrelay instances,
runinning on separate hardware and a partitioned db2 database.  Is it
large enough to prove that SQLRelay is scalable? :)

Re: [Sqlrelay-discussion] Patch: Fix pkg-config .pc file for rudiments and sqlrelay

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

Thanks Mark,

I'll check out these patches and if all goes well, include them in the
next release.

David Muse
dav...@fi...


On Thu, 2008-01-31 at 13:41 -0800, Mark Doliner wrote:
> I noticed that the rudiments.pc, sqlrelay-c.pc and sqlrelay-c++.pc files that are installed to /usr/lib64/pkgconfig/ by the RPMs are invalid (on my CentOS 5 Linux machine anyway--I guess it's possible they would work correctly elsewhere).
> 
> The first line of each .pc file is "prefix=${DESTDIR}/usr/local/firstworks", which is incorrect and causes pkg-config to give the following error:
> Variable 'DESTDIR' not defined in './sqlrelay-c++.pc'
> 
> This happens because of a chunk of configure.in which prepends DESTDIR to a bunch of autoconf variables, which in turn get substituted into sqlrelay-c++.pc.in.
> 
> I feel that the best fix is to remove this chunk from configure.in and instead change the install and uninstall targets of all Makefiles to prepend DESTDIR where needed.  This appears to be roughly how automake handles DESTDIR.  It also seems incorrect to me to change things like 'bindir' and 'libdir' in configure.in.
> 
> The attached patches make those changes.  I don't believe they will cause any negative side effects, but I'm not incredibly familiar with your build process.  The patches do not include the modified configure script, but you can just run autoconf to regenerate it.
> 
> There is a little information about the purpose of DESTDIR here:
> http://www.gnu.org/prep/standards/html_node/DESTDIR.html
> http://www.gnu.org/software/automake/manual/html_node/DESTDIR.html
> 
> Thanks,
> Mark
> 
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________ Sqlrelay-discussion mailing list Sql...@li... https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion



__________________________________________________
D O T E A S Y - "Join the web hosting revolution!"
             http://www.doteasy.com

[Sqlrelay-discussion] Patch: Fix pkg-config .pc file for rudiments and sqlrelay

[Mark D. <ma...@me...>]

SSBub3RpY2VkIHRoYXQgdGhlIHJ1ZGltZW50cy5wYywgc3FscmVsYXktYy5wYyBhbmQgc3FscmVs
YXktYysrLnBjIGZpbGVzIHRoYXQgYXJlIGluc3RhbGxlZCB0byAvdXNyL2xpYjY0L3BrZ2NvbmZp
Zy8gYnkgdGhlIFJQTXMgYXJlIGludmFsaWQgKG9uIG15IENlbnRPUyA1IExpbnV4IG1hY2hpbmUg
YW55d2F5LS1JIGd1ZXNzIGl0J3MgcG9zc2libGUgdGhleSB3b3VsZCB3b3JrIGNvcnJlY3RseSBl
bHNld2hlcmUpLg0KDQpUaGUgZmlyc3QgbGluZSBvZiBlYWNoIC5wYyBmaWxlIGlzICJwcmVmaXg9
JHtERVNURElSfS91c3IvbG9jYWwvZmlyc3R3b3JrcyIsIHdoaWNoIGlzIGluY29ycmVjdCBhbmQg
Y2F1c2VzIHBrZy1jb25maWcgdG8gZ2l2ZSB0aGUgZm9sbG93aW5nIGVycm9yOg0KVmFyaWFibGUg
J0RFU1RESVInIG5vdCBkZWZpbmVkIGluICcuL3NxbHJlbGF5LWMrKy5wYycNCg0KVGhpcyBoYXBw
ZW5zIGJlY2F1c2Ugb2YgYSBjaHVuayBvZiBjb25maWd1cmUuaW4gd2hpY2ggcHJlcGVuZHMgREVT
VERJUiB0byBhIGJ1bmNoIG9mIGF1dG9jb25mIHZhcmlhYmxlcywgd2hpY2ggaW4gdHVybiBnZXQg
c3Vic3RpdHV0ZWQgaW50byBzcWxyZWxheS1jKysucGMuaW4uDQoNCkkgZmVlbCB0aGF0IHRoZSBi
ZXN0IGZpeCBpcyB0byByZW1vdmUgdGhpcyBjaHVuayBmcm9tIGNvbmZpZ3VyZS5pbiBhbmQgaW5z
dGVhZCBjaGFuZ2UgdGhlIGluc3RhbGwgYW5kIHVuaW5zdGFsbCB0YXJnZXRzIG9mIGFsbCBNYWtl
ZmlsZXMgdG8gcHJlcGVuZCBERVNURElSIHdoZXJlIG5lZWRlZC4gIFRoaXMgYXBwZWFycyB0byBi
ZSByb3VnaGx5IGhvdyBhdXRvbWFrZSBoYW5kbGVzIERFU1RESVIuICBJdCBhbHNvIHNlZW1zIGlu
Y29ycmVjdCB0byBtZSB0byBjaGFuZ2UgdGhpbmdzIGxpa2UgJ2JpbmRpcicgYW5kICdsaWJkaXIn
IGluIGNvbmZpZ3VyZS5pbi4NCg0KVGhlIGF0dGFjaGVkIHBhdGNoZXMgbWFrZSB0aG9zZSBjaGFu
Z2VzLiAgSSBkb24ndCBiZWxpZXZlIHRoZXkgd2lsbCBjYXVzZSBhbnkgbmVnYXRpdmUgc2lkZSBl
ZmZlY3RzLCBidXQgSSdtIG5vdCBpbmNyZWRpYmx5IGZhbWlsaWFyIHdpdGggeW91ciBidWlsZCBw
cm9jZXNzLiAgVGhlIHBhdGNoZXMgZG8gbm90IGluY2x1ZGUgdGhlIG1vZGlmaWVkIGNvbmZpZ3Vy
ZSBzY3JpcHQsIGJ1dCB5b3UgY2FuIGp1c3QgcnVuIGF1dG9jb25mIHRvIHJlZ2VuZXJhdGUgaXQu
DQoNClRoZXJlIGlzIGEgbGl0dGxlIGluZm9ybWF0aW9uIGFib3V0IHRoZSBwdXJwb3NlIG9mIERF
U1RESVIgaGVyZToNCmh0dHA6Ly93d3cuZ251Lm9yZy9wcmVwL3N0YW5kYXJkcy9odG1sX25vZGUv
REVTVERJUi5odG1sDQpodHRwOi8vd3d3LmdudS5vcmcvc29mdHdhcmUvYXV0b21ha2UvbWFudWFs
L2h0bWxfbm9kZS9ERVNURElSLmh0bWwNCg0KVGhhbmtzLA0KTWFyaw0K

Re: [Sqlrelay-discussion] build problems on Solaris 2.8

[John K. <kl...@gm...>]

Hi Dave,

I'm building on Solaris:

% uname -a
SunOS mavis 5.8 Generic_117350-34 sun4u sparc SUNW,Ultra-80

using gcc 2.95.3-p11.  I appear to have access to autoconf 2.59 and
libtool 1.5.20.

Here's what's in the directory you asked about:

% ls -al /auto/tte/its/SQLRELAY/lib
total 964
drwxrwxr-x   3 klassa   eng          4096 Jan 30 15:45 ./
drwxrwxr-x   6 klassa   eng          4096 Jan 30 11:41 ../
-rw-r--r--   1 klassa   eng        963264 Jan 30 15:45 librudiments.a
-rwxr-xr-x   1 klassa   eng           857 Jan 30 15:45 librudiments.la*
drwxrwxr-x   2 klassa   eng          4096 Jan 30 15:49 pkgconfig/

My plan was to set "prefix" to /auto/tte/its/SQLRELAY for both
rudiments and SQL Relay, so that it's all just "in there".

Walking me through re-libtool'ing and re-autoconf'ing would be great.
:-)  Thanks!

JK


On Jan 30, 2008 4:15 PM, David Muse <dav...@fi...> wrote:
> Those are some strange errors like I've never seen before :)  It looks
> like libtool is having trouble figuring out the rules for your system
> and adding .libs before the library when it shouldn't.  What platform
> are you building on?
>
> Also, look in /auto/tte/its/SQLRELAY/lib, are there any librudiments
> files installed there?
>
> You might need to re-libtoolize for your system.  Do you have the
> libtool and autoconf packages installed?  If so, let me know and I'll
> walk you through it.
>
> Dave
> dav...@fi...
>
>
>
> On Wed, 2008-01-30 at 16:07 -0500, John Klassa wrote:
> > I finally got rudiments to build, but to do so, I had to get rid of
> > the "-rpath $(libdir)" from the Makefile, and then also had to remove
> > a bogus, trailing "i" from the value of "instname" (in libtool).
> >
> > When I try to build SQL Relay proper, though, I get this far:
> >
> > % make
> > make -C src all
> > make[1]: Entering directory `/tmp/sqlrelay-0.39.4/src'
> > make -C util all
> > make[2]: Entering directory `/tmp/sqlrelay-0.39.4/src/util'
> > /bin/sh ../../libtool --mode=link g++ -o libsqlrutil.la cmdline.lo
> > debugfile.lo tempdir.lo sqlrconfigfile.lo authenticator.lo
> > -L/auto/tte/its/SQLRELAY/lib -lrudiments -lsocket -lrt -lresolv
> > -rpath /auto/tte/its/SQLRELAY/lib -release 0.39.4 -version-info 1:0:0
> > -no-undefined
> > rm -fr  .libs/libsqlrutil.lax
> > rm -fr .libs/libsqlrutil.lax
> > mkdir .libs/libsqlrutil.lax
> > rm -fr .libs/libsqlrutil.lax/librudiments.a
> > mkdir .libs/libsqlrutil.lax/librudiments.a
> > (cd .libs/libsqlrutil.lax/librudiments.a && ar x
> > /auto/tte/its/SQLRELAY/lib/.libs/librudiments.a)
> > ar: /auto/tte/its/SQLRELAY/lib/.libs/librudiments.a: No such file or directory
> > make[2]: *** [libsqlrutil.la] Error 9
> > make[2]: Leaving directory `/tmp/sqlrelay-0.39.4/src/util'
> > make[1]: *** [all] Error 2
> > make[1]: Leaving directory `/tmp/sqlrelay-0.39.4/src'
> > make: *** [all] Error 2
> > Exit 2
> >
> > Note that /auto/tte/its/SQLRELAY is what I passed in via
> > --with-rudiments-prefix.  Why is it looking in
> > /auto/tte/its/SQLRELAY/lib/.libs rather than just
> > /auto/tte/its/SQLRELAY/lib, though (i.e. why the extra ".libs")?
> >
> > Confused,
> > JK
> >
> > -------------------------------------------------------------------------
> > This SF.net email is sponsored by: Microsoft
> > Defy all challenges. Microsoft(R) Visual Studio 2008.
> > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
>
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2008.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>