sqlrelay-discussion — Discussion of topics related to SQL Relay

Re: [Sqlrelay-discussion] (Resend) Re: Listener hangs on query

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

Not running is probably the best result in this case :)  If it were
anything else then it would be tougher to track down.

It's most likely that there is just some permissions-related problem and
the sqlr-connection-mysql daemons can't log into the db.  Check the
mysql db log.

ltrace is actually a lot more useful for this stuff than strace.  You
could try starting sqlr-listener manually and then start an instance of
sqlr-connection-mysql-debug using ltrace:

sqlr-listener -id jal
ltrace sqlr-connection-mysql-debug -id jal -connectionid something

(where "something" is replaced with the connectionid defined in the
sqlrelay.conf file)

You should be able to tell what function is failing that way.

Dave
dav...@fi...


On Tue, 2008-04-08 at 12:08 -0400, Jamie A Lawrence wrote:
> Just sending this out again, hoping that someone has an idea.
> 
> I've tried stracing sqlr-start, but that appears to not provide much  
> in the way of anything useful.
> 
> -j
> 
> 
> On Mar 26, 2008, at 10:16 AM, David Muse wrote:
> > It looks like either the sqlr-connection-mysql processes aren't  
> > running,
> > or they're hung.
> >
> > Do ps -efa | grep sqlr-connection-mysql and see if they are  
> > running.  If
> > not, then they may not have been able to log into the DB.  If they are
> > running, then something more sinister is going on.  In that case, try
> > handoff="reconnect" rather than handoff="pass" and see if that works.
> >
> > Let me know what you find.
> 
> 
> Sorry to be slow. Looks like "not running". is the answer.
> 
> 
> jal@charybdis $ ps -efa | grep sqlr
> sqlrelay 29858     1  0 18:05 pts/7    00:00:00 sqlr-listener-debug - 
> id jal -config /etc/sqlrelay.conf
> sqlrelay 29900     1  0 18:06 ?        00:00:00 sqlr-scaler -id jal - 
> debug -config /etc/sqlrelay.conf
> root     29906     1  0 18:06 ?        00:00:00 sqlr-cachemanager
> root     29911 19275  0 18:06 pts/7    00:00:00 sqlrsh -config /etc/ 
> sqlrelay.conf -id jal
> sqlrelay 29912 29858  0 18:06 pts/7    00:00:00 sqlr-listener-debug - 
> id jal -config /etc/sqlrelay.conf
> jal      29932 29913  0 18:07 pts/6    00:00:00 grep sqlr
> 
> 
> As for not logging in to the DB, would that failure be logged  
> anywhere? I doublechecked the credentials in /etc/sqlrelay.conf, and  
> they're correct. Are there other reasons sqlr-connection might not  
> start?
> 
> Thanks,
> 
> -j
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
> Register now and save $200. Hurry, offer ends at 11:59 p.m., 
> Monday, April 7! Use priority code J8TLD2. 
> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
> _______________________________________________
> 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] phppeardb returning only 100 rows

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

Hmm, that should fetch all rows.  I'll have to look into it.  I'm pretty
sure this has to do with the result set buffer size though.  A
workaround would be to edit src/api/phppeardb/sqlrelay.php and comment
out these lines:

        sqlrcur_setResultSetBufferSize($cursor,100);

which should be around lines 171 and 230.  And then reinstall.

The side effect is that the entire result set will be cached on the
client side, rather than just the first 100 rows, which could consume a
lot of memory, depending on how much data is in each row.

I'll fix this for the next release.

Dave
dav...@fi...


On Tue, 2008-04-08 at 22:37 -0300, Felipe Ceglia - PY1NB wrote:
> Hello,
> 
> Can anyone help me on this issue?
> 
> I am using sqlrelay-0.39.4 and postgresql 8.3.1.
> 
> When I run this code from phppeardb, it only returns me 100 rows:
> 
> $query = "SELECT id, name FROM ct_contest ORDER BY name ASC;";
>         $db->setFetchMode(DB_FETCHMODE_ASSOC);
>         $result = $db->query($query);
>                 while ($line = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
>                  print ("\t<option value=\"$line[id]\" 
> $selected>$line[name]</option>\n");
>                 print "$line[name]\n";
>         }
>         $result->free();
> 
> ###########################################################
> 
> However, if I run it on sqlrsh:
>         Rows Returned   : 293
>         Fields Returned : 586
>         System time     : 0
> 
> ###########################################################
> 
> 
> print_r($result); says:
> 
> DB_result Object
> (
>     [autofree] => 
>     [dbh] => DB_sqlrelay Object
>         (
>             [connection] => Resource id #14
>             [identity] => 
>             [bindformat] => 
>             [phptype] => sqlrelay
>             [dbsyntax] => sqlrelay
>             [autocommit] => 
>             [fetchmode] => 2
>             [affectedrows] => 0
>             [prepare_types] => Array
>                 (
>                 )
> 
>             [is_select] => Array
>                 (
>                 )
> 
>             [features] => Array
>                 (
>                     [prepare] => 1
>                     [pconnect] => 1
>                     [transactions] => 1
>                     [limit] => emulate
>                 )
> 
>             [fetchmode_object_class] => stdClass
>             [was_connected] => 
>             [last_query] => 
>             [options] => Array
>                 (
>                     [result_buffering] => 500
>                     [persistent] => 
>                     [ssl] => 
>                     [debug] => 0
>                     [seqname_format] => %s_seq
>                     [autofree] => 
>                     [portability] => 0
>                     [optimize] => performance
>                 )
> 
>             [last_parameters] => Array
>                 (
>                 )
> 
>             [prepare_tokens] => Array
>                 (
>                 )
> 
>             [prepared_queries] => Array
>                 (
>                 )
> 
>             [_last_query_manip] => 
>             [_next_query_manip] => 
>             [_debug] => 
>             [_default_error_mode] => 
>             [_default_error_options] => 
>             [_default_error_handler] => 
>             [_error_class] => DB_Error
>             [_expected_errors] => Array
>                 (
>                 )
> 
>             [dsn] => Array
>                 (
>                     [phptype] => sqlrelay
>                     [dbsyntax] => sqlrelay
>                     [username] => postgres
>                     [password] => password
>                     [protocol] => unix
>                     [hostspec] => 
>                     [port] => 9000
>                     [socket] => /tmp/clx_db.socket
>                     [database] => clx_db
>                 )
> 
>         )
> 
>     [fetchmode] => 2
>     [fetchmode_object_class] => stdClass
>     [limit_count] => 
>     [limit_from] => 
>     [parameters] => Array
>         (
>         )
> 
>     [query] => 
>     [result] => DB_sqlrelay_cursor Object
>         (
>             [cursor] => Resource id #18
>             [connection] => Resource id #14
>             [rownum] => 100
>             [rownun] => 0
>         )
> 
>     [row_counter] => 
>     [statement] => 
> 
> 
> 
> Thank you,
> 
> Felipe
> 
> 
> -------------------------------------------------------------------------
> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference 
> Don't miss this year's exciting event. There's still time to save $100. 
> Use priority code J8TL2D2. 
> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
> _______________________________________________
> 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] phppeardb returning only 100 rows

[Felipe C. - P. <fel...@te...>]

Hello,

Can anyone help me on this issue?

I am using sqlrelay-0.39.4 and postgresql 8.3.1.

When I run this code from phppeardb, it only returns me 100 rows:

$query = "SELECT id, name FROM ct_contest ORDER BY name ASC;";
        $db->setFetchMode(DB_FETCHMODE_ASSOC);
        $result = $db->query($query);
                while ($line = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
                 print ("\t<option value=\"$line[id]\" 
$selected>$line[name]</option>\n");
                print "$line[name]\n";
        }
        $result->free();

###########################################################

However, if I run it on sqlrsh:
        Rows Returned   : 293
        Fields Returned : 586
        System time     : 0

###########################################################


print_r($result); says:

DB_result Object
(
    [autofree] => 
    [dbh] => DB_sqlrelay Object
        (
            [connection] => Resource id #14
            [identity] => 
            [bindformat] => 
            [phptype] => sqlrelay
            [dbsyntax] => sqlrelay
            [autocommit] => 
            [fetchmode] => 2
            [affectedrows] => 0
            [prepare_types] => Array
                (
                )

            [is_select] => Array
                (
                )

            [features] => Array
                (
                    [prepare] => 1
                    [pconnect] => 1
                    [transactions] => 1
                    [limit] => emulate
                )

            [fetchmode_object_class] => stdClass
            [was_connected] => 
            [last_query] => 
            [options] => Array
                (
                    [result_buffering] => 500
                    [persistent] => 
                    [ssl] => 
                    [debug] => 0
                    [seqname_format] => %s_seq
                    [autofree] => 
                    [portability] => 0
                    [optimize] => performance
                )

            [last_parameters] => Array
                (
                )

            [prepare_tokens] => Array
                (
                )

            [prepared_queries] => Array
                (
                )

            [_last_query_manip] => 
            [_next_query_manip] => 
            [_debug] => 
            [_default_error_mode] => 
            [_default_error_options] => 
            [_default_error_handler] => 
            [_error_class] => DB_Error
            [_expected_errors] => Array
                (
                )

            [dsn] => Array
                (
                    [phptype] => sqlrelay
                    [dbsyntax] => sqlrelay
                    [username] => postgres
                    [password] => password
                    [protocol] => unix
                    [hostspec] => 
                    [port] => 9000
                    [socket] => /tmp/clx_db.socket
                    [database] => clx_db
                )

        )

    [fetchmode] => 2
    [fetchmode_object_class] => stdClass
    [limit_count] => 
    [limit_from] => 
    [parameters] => Array
        (
        )

    [query] => 
    [result] => DB_sqlrelay_cursor Object
        (
            [cursor] => Resource id #18
            [connection] => Resource id #14
            [rownum] => 100
            [rownun] => 0
        )

    [row_counter] => 
    [statement] => 



Thank you,

Felipe

[Sqlrelay-discussion] (Resend) Re: Listener hangs on query

[Jamie A L. <ja...@ja...>]

Just sending this out again, hoping that someone has an idea.

I've tried stracing sqlr-start, but that appears to not provide much  
in the way of anything useful.

-j


On Mar 26, 2008, at 10:16 AM, David Muse wrote:
> It looks like either the sqlr-connection-mysql processes aren't  
> running,
> or they're hung.
>
> Do ps -efa | grep sqlr-connection-mysql and see if they are  
> running.  If
> not, then they may not have been able to log into the DB.  If they are
> running, then something more sinister is going on.  In that case, try
> handoff="reconnect" rather than handoff="pass" and see if that works.
>
> Let me know what you find.


Sorry to be slow. Looks like "not running". is the answer.


jal@charybdis $ ps -efa | grep sqlr
sqlrelay 29858     1  0 18:05 pts/7    00:00:00 sqlr-listener-debug - 
id jal -config /etc/sqlrelay.conf
sqlrelay 29900     1  0 18:06 ?        00:00:00 sqlr-scaler -id jal - 
debug -config /etc/sqlrelay.conf
root     29906     1  0 18:06 ?        00:00:00 sqlr-cachemanager
root     29911 19275  0 18:06 pts/7    00:00:00 sqlrsh -config /etc/ 
sqlrelay.conf -id jal
sqlrelay 29912 29858  0 18:06 pts/7    00:00:00 sqlr-listener-debug - 
id jal -config /etc/sqlrelay.conf
jal      29932 29913  0 18:07 pts/6    00:00:00 grep sqlr


As for not logging in to the DB, would that failure be logged  
anywhere? I doublechecked the credentials in /etc/sqlrelay.conf, and  
they're correct. Are there other reasons sqlr-connection might not  
start?

Thanks,

-j

[Sqlrelay-discussion] "Error was: No such file or directory" on freeBSD

[Culley H. <har...@gm...>]

Hello, 

I am trying to configure sqlrelay on freebsd-- pc-bsd actually, my
development machine and eventually on my production freebsd servers.
Sqlrelay was installed from the port-- version sqlrelay-0.38_2

When I try to start it I get the below error.  Any ideas?  It seems like it
is looking for /var/sqlrelay.  In this directory there was no
/var/sqlrelay/var so I created it but it didn't fix the problem.  

ch

===================


[root@pcbsd /usr/local/etc]# sqlr-start -id flashex

Starting listener:
  sqlr-listener -id flashex -config /usr/local/etc/sqlrelay.conf

sqlr-listener error:
        Unable to create a semaphore set.  This is usally because an
        sqlr-listener is already running for the flashex instance.

        If it is not running, something may have crashed and left an old semaphore set
        lying around.  Use the ipcs command to inspect existing semaphore sets
        and the ipcrm command to remove the semaphore set with
        id -1.

        Error was: No such file or directory

unsuccessful completion

sqlr-listener failed to start.

Re: [Sqlrelay-discussion] Listener hangs on query

[Jamie A L. <ja...@ja...>]

On Mar 26, 2008, at 10:16 AM, David Muse wrote:
> It looks like either the sqlr-connection-mysql processes aren't  
> running,
> or they're hung.
>
> Do ps -efa | grep sqlr-connection-mysql and see if they are  
> running.  If
> not, then they may not have been able to log into the DB.  If they are
> running, then something more sinister is going on.  In that case, try
> handoff="reconnect" rather than handoff="pass" and see if that works.
>
> Let me know what you find.


Sorry to be slow. Looks like "not running". is the answer.


jal@charybdis $ ps -efa | grep sqlr
sqlrelay 29858     1  0 18:05 pts/7    00:00:00 sqlr-listener-debug - 
id jal -config /etc/sqlrelay.conf
sqlrelay 29900     1  0 18:06 ?        00:00:00 sqlr-scaler -id jal - 
debug -config /etc/sqlrelay.conf
root     29906     1  0 18:06 ?        00:00:00 sqlr-cachemanager
root     29911 19275  0 18:06 pts/7    00:00:00 sqlrsh -config /etc/ 
sqlrelay.conf -id jal
sqlrelay 29912 29858  0 18:06 pts/7    00:00:00 sqlr-listener-debug - 
id jal -config /etc/sqlrelay.conf
jal      29932 29913  0 18:07 pts/6    00:00:00 grep sqlr


As for not logging in to the DB, would that failure be logged  
anywhere? I doublechecked the credentials in /etc/sqlrelay.conf, and  
they're correct. Are there other reasons sqlr-connection might not  
start?

Thanks,

-j

Re: [Sqlrelay-discussion] SQLRelay 0.35 + Perl API + DB2

[Lee S. <st...@st...>]

Thanks David,

I worked that one out myself a couple of days after my first email.

The next snag I've run into is that I can't execute the same query more than once.  It might be something that's fixed in a newer release (0.35 is the only one which will build on my system).

The error is:
Error: [IBM][CLI Driver] CLI0115E  Invalid cursor state. SQLSTATE=24000

Any suggestions? :/




-----Original Message-----
From: sql...@li... [mailto:sql...@li...] On Behalf Of David Muse
Sent: Wednesday, 26 March 2008 23:24
To: Discussion of topics related to SQL Relay
Subject: Re: [Sqlrelay-discussion] SQLRelay 0.35 + Perl API + DB2

Ugg, it looks like the DBI driver for SQL Relay converts ?'s to :1's.  I
suspect this is because back when the DBI driver was first written, SQL
Relay didn't support native binds and used the :1 format.  It should
check the DB type and either leave the ?'s alone or convert to the
appropriate format like the PHP Pear DB driver does.

I think a quick fix would be to comment out line 135 of SQLRelay.pm:

Change:
        $statement =~ s/\?/":" . ++$count/eg;
to:
        # $statement =~ s/\?/":" . ++$count/eg;

That should have the effect of leaving ?'s alone.  The rest should "just
work".  Let me know how that goes though.  It's possible that some other
code may need to be modified.

I'll put in a fix for this in the next release.

David Muse
dav...@fi...


On Thu, 2008-03-20 at 01:29 +0900, Lee Standen wrote:
> Hey Guys,
>
>
>
> I've been trying to get SQLRelay 0.35 working with DB2, and now that I
> finally got it to connect, I'm having trouble with the Perl API
> munging the SQL into an invalid format.   Here's an example:
>
>
>
> Original SQL: SELECT oid FROM service WHERE oid=?
>
> Perl API Query: SELECT oid FROM service WHERE oid=:1
>
> Error: [IBM][CLI Driver][DB2/LINUX] SQL0312N  The host variable "1" is
> used in a dynamic SQL statement, a view definition, or a trigger
> definition.
>
>
>
> This is just a simple query I've tried to replicate the issue, but it
> obviously happens with any.
>
>
>
> SQLRelay 0.35
>
> Client Version: 8.1.0.48 (Fixpak 5)
>
>
>
> Note: This works perfectly fine if I bypass the SQLRelay server and go
> direct to the database.
>
>
>
> So I have two quick questions:
>
> 1.      Is this a known issue which has been fixed in a later version?
> (I'd try this, but newer versions won't build on my OS without serious
> hacky library updates)
>
> 2.      If this is not fixed, is there a patch floating around I can
> apply to 0.35 to make this work?
>
>
>
> Thanks J
>
>
>
>
>
>
> __________________________________________________
> 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

-------------------------------------------------------------------------
Check out the new SourceForge.net Marketplace.
It's the best place to buy or sell services for
just about anything Open Source.
http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
_______________________________________________
Sqlrelay-discussion mailing list
Sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

Re: [Sqlrelay-discussion] SQLRelay 0.35 + Perl API + DB2

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

Ugg, it looks like the DBI driver for SQL Relay converts ?'s to :1's.  I
suspect this is because back when the DBI driver was first written, SQL
Relay didn't support native binds and used the :1 format.  It should
check the DB type and either leave the ?'s alone or convert to the
appropriate format like the PHP Pear DB driver does.

I think a quick fix would be to comment out line 135 of SQLRelay.pm:

Change:
	$statement =~ s/\?/":" . ++$count/eg;
to:
	# $statement =~ s/\?/":" . ++$count/eg;

That should have the effect of leaving ?'s alone.  The rest should "just
work".  Let me know how that goes though.  It's possible that some other
code may need to be modified.

I'll put in a fix for this in the next release.

David Muse
dav...@fi...


On Thu, 2008-03-20 at 01:29 +0900, Lee Standen wrote:
> Hey Guys,
> 
>  
> 
> I’ve been trying to get SQLRelay 0.35 working with DB2, and now that I
> finally got it to connect, I’m having trouble with the Perl API
> munging the SQL into an invalid format.   Here’s an example:
> 
>  
> 
> Original SQL: SELECT oid FROM service WHERE oid=?
> 
> Perl API Query: SELECT oid FROM service WHERE oid=:1
> 
> Error: [IBM][CLI Driver][DB2/LINUX] SQL0312N  The host variable "1" is
> used in a dynamic SQL statement, a view definition, or a trigger
> definition.
> 
>  
> 
> This is just a simple query I’ve tried to replicate the issue, but it
> obviously happens with any.
> 
>  
> 
> SQLRelay 0.35
> 
> Client Version: 8.1.0.48 (Fixpak 5)
> 
>  
> 
> Note: This works perfectly fine if I bypass the SQLRelay server and go
> direct to the database.
> 
>  
> 
> So I have two quick questions:
> 
> 1.      Is this a known issue which has been fixed in a later version?
> (I’d try this, but newer versions won’t build on my OS without serious
> hacky library updates)
> 
> 2.      If this is not fixed, is there a patch floating around I can
> apply to 0.35 to make this work?
> 
>  
> 
> Thanks J
> 
>  
> 
>  
> 
> 
> __________________________________________________
> 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

Re: [Sqlrelay-discussion] Listener hangs on query

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

It looks like either the sqlr-connection-mysql processes aren't running,
or they're hung.

Do ps -efa | grep sqlr-connection-mysql and see if they are running.  If
not, then they may not have been able to log into the DB.  If they are
running, then something more sinister is going on.  In that case, try
handoff="reconnect" rather than handoff="pass" and see if that works.

Let me know what you find.

David Muse
dav...@fi...

On Mon, 2008-03-24 at 13:59 -0400, Jamie A Lawrence wrote:
> Hello,
> 
> I'm trying to get a pretty simple sqlrelay setup working. Config:
> 
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> 
> <instances>
> 
>          <instance id="gab" port="9000" socket="/tmp/sqlrelay.socket"  
> dbase="mysql" connections="20" maxconnections="20" maxqueuelength="5"  
> growby="1" ttl="60" endofsession="commit" sessiontimeout="600"  
> runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5"  
> authtier="listener" handoff="pass" deniedips="" allowedips="(127\.0\. 
> 0\.1|209\.212\.93\.52)" debug="listener_and_connection">
>                  <users>
>                          <user user="jal" password="xxx"/> -->
>                  </users>
>                  <connections>
>                          <connection connectionid="gab"  
> string 
> ="user=jal;password=xxx;db=gab;host=127.0.0.1;port=3306;fakebinds=no;"  
> metric="1"/>
>                  </connections>
>          </instance>
> 
> </instances>
> 
> 
> Symptom:
> 
> I start sqlrsh, try to ping, and it hangs forever:
> 
> [jal@zipp ~]$ sqlrsh localhost 9000 /tmp/sqlrelay.socket jal xxx
> SQLRShell - Version 0.22
> 	Connected to: localhost:9000 as jal
> 
> 	type help; for a help.
> 
> 0> ping;
> 
> 
> The logs seem to indicate that it is hanging on getting a connection:
> 
> ::::::::::::::
> sqlr-listener.8041
> ::::::::::::::
> 03/24/2008 13:58:37 EDT listener [8041] : creating shared memory and  
> semaphores
> 
> 03/24/2008 13:58:37 EDT listener [8041] : id filename:
> 
> 03/24/2008 13:58:37 EDT listener [8041] : /var/sqlrelay/tmp/gab
> 
> 03/24/2008 13:58:37 EDT listener [8041] : 	creating shared memory...
> 
> 03/24/2008 13:58:37 EDT listener [8041] : 	creating semaphores...
> 
> 03/24/2008 13:58:37 EDT listener [8041] : waiting for client  
> connection...
> 
> 03/24/2008 13:59:08 EDT listener [8041] : done waiting for client  
> connection
> 
> 03/24/2008 13:59:08 EDT listener [8041] : forked a child: 8092
> 
> 03/24/2008 13:59:08 EDT listener [8041] : waiting for client  
> connection...
> 
> ::::::::::::::
> sqlr-listener.8092
> ::::::::::::::
> 03/24/2008 13:59:08 EDT listener [8092] : getting authentication...
> 
> 03/24/2008 13:59:08 EDT listener [8092] : 	listener-based  
> authentication succeeded
> 
> 03/24/2008 13:59:08 EDT listener [8092] : incrementing session count...
> 
> 03/24/2008 13:59:08 EDT listener [8092] : 	1
> 
> 03/24/2008 13:59:08 EDT listener [8092] : done incrementing session  
> count
> 
> 03/24/2008 13:59:08 EDT listener [8092] : getting a connection...
> 
> This is on FC5, with Mysql 5.0.22 and Sqlrelay from sqlrelay-0.33.1- 
> fr0.2.i386.rpm, and rudiments-0.25.1-fr1.i386.rpm
> 
> Anyone have any ideas?
> 
> Thanks,
> 
> -j
> 
> -------------------------------------------------------------------------
> 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] FreeTDS bind incorrectly escaping strings

[Michael A. <mi...@mi...>]

David Muse wrote:
> Hey Michael,
>
> Wow, I had no idea that MS SQL Server didn't use backslashes!
>
> A quick fix, assuming that you're not using SQL Relay with any db other
> then SQL Server, would be to edit src/connection/sqlrcursor/fakebinds.C
> around line 119 and change:
>
> 			if (ch=='\'' || ch=='\\') {
> 				buffer->append('\\');
>
> to:
> 			if (ch=='\'') {
> 				buffer->append('\'');
>
> Then rebuild/reinstall.
>   
Thanks David, made the change and tested, can confirm that this fixes 
the string escaping problem.

Cheers,
--Michael

Re: [Sqlrelay-discussion] FreeTDS bind incorrectly escaping strings

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

Hey Michael,

Wow, I had no idea that MS SQL Server didn't use backslashes!

A quick fix, assuming that you're not using SQL Relay with any db other
then SQL Server, would be to edit src/connection/sqlrcursor/fakebinds.C
around line 119 and change:

			if (ch=='\'' || ch=='\\') {
				buffer->append('\\');

to:
			if (ch=='\'') {
				buffer->append('\'');

Then rebuild/reinstall.

I'll fix it for real in the next release.

David Muse
dav...@fi...


On Thu, 2008-03-27 at 00:25 +1300, Michael Adams wrote:
> Hi there,
> 
> Ran into an interesting problem today... it appears that bound input 
> variables are escaped incorrectly when running against SQL Server. 
> Basically it is escaping quotes with a backslash (unix style), not 
> double quotes (MS style).
> 
> E.g. the following PHP code...:
>         $sql = "SELECT * FROM tblPatron WHERE FirstName = @first_name";
>         $args['first_name'] = "O'Rourke";
>        
>         $q = sqlrcon_alloc("localhost", $port, $socket, $username, 
> $password, 1, 1);
>         $c = sqlrcur_alloc($q);
>        
>         sqlrcur_prepareQuery($c, $sql);
>         foreach($args as $a => $b)
>             sqlrcur_inputbind($c, $a, $b);
>         $o = sqlrcur_executequery($c);
>         if ($o != 1) throw new Exception("Error with query: " . 
> sqlrcur_errorMessage($c));
>         for ($i=0; $i<sqlrcur_rowcount($c); $i++)
>             $result[] = (object) sqlrcur_getrowassoc($c, $i);
>        
>         sqlrcur_free($c);
>         sqlrcon_free($q);
> 
> 
> This results in the following error:
>     Error with query: Server message:
>     severity(0)
>     number(102)
>     state(1)
>     line(1)
>     Server Name:
>     HAMLET\SQLEXPRESS
>     Procedure Name:
>    
>     Error: Incorrect syntax near 'Rourke'.
> So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE 
> FirstName = 'O\'Rourke'; SQL server doesn't treat the backslash 
> specially so strings ends before the R.
> It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''Rourke';
> 
> If I set $args['first_name'] = "O''Rourke" (two single quotes) then I 
> get: Error: Incorrect syntax near '\'.
> So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE 
> FirstName = 'O\'\'Rourke'; so this time the statement ends at the second \
> It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''''Rourke';
> 
> Am I doing something wrong? The only relevant info I could find is that 
> under FreeTDS connections the binds are faked, but nothing how strings 
> are escaped.
> 
> Thanks in advance, SQLR has made a massive (~ 5x) improvement in 
> database access times here.
> --Michael Adams
> 
> -------------------------------------------------------------------------
> Check out the new SourceForge.net Marketplace.
> It's the best place to buy or sell services for
> just about anything Open Source.
> http://ad.doubleclick.net/clk;164216239;13503038;w?http://sf.net/marketplace
> _______________________________________________
> 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] FreeTDS bind incorrectly escaping strings

[Michael A. <mi...@mi...>]

Hi there,

Ran into an interesting problem today... it appears that bound input 
variables are escaped incorrectly when running against SQL Server. 
Basically it is escaping quotes with a backslash (unix style), not 
double quotes (MS style).

E.g. the following PHP code...:
        $sql = "SELECT * FROM tblPatron WHERE FirstName = @first_name";
        $args['first_name'] = "O'Rourke";
       
        $q = sqlrcon_alloc("localhost", $port, $socket, $username, 
$password, 1, 1);
        $c = sqlrcur_alloc($q);
       
        sqlrcur_prepareQuery($c, $sql);
        foreach($args as $a => $b)
            sqlrcur_inputbind($c, $a, $b);
        $o = sqlrcur_executequery($c);
        if ($o != 1) throw new Exception("Error with query: " . 
sqlrcur_errorMessage($c));
        for ($i=0; $i<sqlrcur_rowcount($c); $i++)
            $result[] = (object) sqlrcur_getrowassoc($c, $i);
       
        sqlrcur_free($c);
        sqlrcon_free($q);


This results in the following error:
    Error with query: Server message:
    severity(0)
    number(102)
    state(1)
    line(1)
    Server Name:
    HAMLET\SQLEXPRESS
    Procedure Name:
   
    Error: Incorrect syntax near 'Rourke'.
So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE 
FirstName = 'O\'Rourke'; SQL server doesn't treat the backslash 
specially so strings ends before the R.
It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''Rourke';

If I set $args['first_name'] = "O''Rourke" (two single quotes) then I 
get: Error: Incorrect syntax near '\'.
So the SQL passed to the DB would be: SELECT * FROM tblPatron WHERE 
FirstName = 'O\'\'Rourke'; so this time the statement ends at the second \
It should be: SELECT * FROM tblPatron WHERE FirstName = 'O''''Rourke';

Am I doing something wrong? The only relevant info I could find is that 
under FreeTDS connections the binds are faked, but nothing how strings 
are escaped.

Thanks in advance, SQLR has made a massive (~ 5x) improvement in 
database access times here.
--Michael Adams

[Sqlrelay-discussion] Listener hangs on query

[Jamie A L. <ja...@ja...>]

Hello,

I'm trying to get a pretty simple sqlrelay setup working. Config:

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

<instances>

         <instance id="gab" port="9000" socket="/tmp/sqlrelay.socket"  
dbase="mysql" connections="20" maxconnections="20" maxqueuelength="5"  
growby="1" ttl="60" endofsession="commit" sessiontimeout="600"  
runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5"  
authtier="listener" handoff="pass" deniedips="" allowedips="(127\.0\. 
0\.1|209\.212\.93\.52)" debug="listener_and_connection">
                 <users>
                         <user user="jal" password="xxx"/> -->
                 </users>
                 <connections>
                         <connection connectionid="gab"  
string 
="user=jal;password=xxx;db=gab;host=127.0.0.1;port=3306;fakebinds=no;"  
metric="1"/>
                 </connections>
         </instance>

</instances>


Symptom:

I start sqlrsh, try to ping, and it hangs forever:

[jal@zipp ~]$ sqlrsh localhost 9000 /tmp/sqlrelay.socket jal xxx
SQLRShell - Version 0.22
	Connected to: localhost:9000 as jal

	type help; for a help.

0> ping;


The logs seem to indicate that it is hanging on getting a connection:

::::::::::::::
sqlr-listener.8041
::::::::::::::
03/24/2008 13:58:37 EDT listener [8041] : creating shared memory and  
semaphores

03/24/2008 13:58:37 EDT listener [8041] : id filename:

03/24/2008 13:58:37 EDT listener [8041] : /var/sqlrelay/tmp/gab

03/24/2008 13:58:37 EDT listener [8041] : 	creating shared memory...

03/24/2008 13:58:37 EDT listener [8041] : 	creating semaphores...

03/24/2008 13:58:37 EDT listener [8041] : waiting for client  
connection...

03/24/2008 13:59:08 EDT listener [8041] : done waiting for client  
connection

03/24/2008 13:59:08 EDT listener [8041] : forked a child: 8092

03/24/2008 13:59:08 EDT listener [8041] : waiting for client  
connection...

::::::::::::::
sqlr-listener.8092
::::::::::::::
03/24/2008 13:59:08 EDT listener [8092] : getting authentication...

03/24/2008 13:59:08 EDT listener [8092] : 	listener-based  
authentication succeeded

03/24/2008 13:59:08 EDT listener [8092] : incrementing session count...

03/24/2008 13:59:08 EDT listener [8092] : 	1

03/24/2008 13:59:08 EDT listener [8092] : done incrementing session  
count

03/24/2008 13:59:08 EDT listener [8092] : getting a connection...

This is on FC5, with Mysql 5.0.22 and Sqlrelay from sqlrelay-0.33.1- 
fr0.2.i386.rpm, and rudiments-0.25.1-fr1.i386.rpm

Anyone have any ideas?

Thanks,

-j

[Sqlrelay-discussion] SQLRelay 0.35 + Perl API + DB2

[Lee S. <st...@st...>]

Hey Guys,

I've been trying to get SQLRelay 0.35 working with DB2, and now that I finally got it to connect, I'm having trouble with the Perl API munging the SQL into an invalid format.   Here's an example:

Original SQL: SELECT oid FROM service WHERE oid=?
Perl API Query: SELECT oid FROM service WHERE oid=:1
Error: [IBM][CLI Driver][DB2/LINUX] SQL0312N  The host variable "1" is used in a dynamic SQL statement, a view definition, or a trigger definition.

This is just a simple query I've tried to replicate the issue, but it obviously happens with any.

SQLRelay 0.35
Client Version: 8.1.0.48 (Fixpak 5)

Note: This works perfectly fine if I bypass the SQLRelay server and go direct to the database.

So I have two quick questions:

1.       Is this a known issue which has been fixed in a later version? (I'd try this, but newer versions won't build on my OS without serious hacky library updates)

2.       If this is not fixed, is there a patch floating around I can apply to 0.35 to make this work?

Thanks J

Re: [Sqlrelay-discussion] Is it possible to do mdb->sqlrelay->mdb scheme?

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

Hello,

Sorry for the delayed reply...

I'm not certain that you can do what you want with SQL Relay.

If your applications are accessing an MDB file, they are probably doing
it through an Access DLL or maybe an ODBC driver for Access databases.
If they are using an ODBC driver, then in theory, you could just change
the DSN to point to a different database, like postgres or mysql, or any
other db.  The only problem then is the format of the queries.  All
databases use slightly different query formats for higher level
functions.  Your app's simple selects, inserts and deletes might work,
but outer joins, subselects and so forth might not.

If the apps aren't using ODBC to access the database, then you'd need
something similar to SQL Relay's drop-in replacement library for MySQL,
but for Access, on windows.  You'd need a DLL that implements all of the
Access API functions that are called by your apps, but overrides them to
talk to a different database.  SQL Relay doesn't provide a library that
maps the Access API.

Since apps access mdb files directly by opening the file rather than
over a socket through an RDBMS, it's also not possible to put a proxy
between the app and the db other than to use the approach described
above.

Hopefully this is helpful, wish there was an easier solution.

Dave
dav...@fi...


On Thu, 2008-02-28 at 20:32 +0300, if wrote:
> Hello list!
> 
> I face a little challenge, that after some heavy googling seem to be
> not so little. Here's the problem:
> 
> We have custom (*cough* goverment) application for windows, that works
> with mdb files _only_. It runs on windows, it opens only predefined
> mdb file, and it is dumb and enerprisey, and the worst part -- we are
> locked to use it. There's no security in it, no logging, and no
> control over the user. It works with financial data, so i have to
> tighten it a bit.
> 
> I moved mdb file to linux samba server, and restricted access on
> network level. The next part is tricky. I need control at sql query
> level and make an alert system, because main threat is emloyees
> working with it. My plan is to inject sqlrelay between "fake" mdb file
> and some good database (think postgres). Or at least inject it between
> fake mdb and real mdb, and log sql, that will be sufficient for the
> first time. After that i'll write everything i need.
> 
> So the main question is -- how can i make this fake mdb file? Sqlrelay
> can make socket->mdb proxying, but can it make a reverse proxy? Than
> i'll just chain few instances together and get what i want.
> 
> I also looked at Perl::DBI Proxy guides, mdbtools guides -- all with no luck.
> Any, just any advice will be highly appreciated!
> 
> 
> Thanks for reading,
> regards,
> if
> 
> -------------------------------------------------------------------------
> 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] is SQL Relay "relocatable"?

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

Using the disable-rpath options should prevent SQL Relay from looking in
the paths that the database libraries were found in at compile time.  If
it's not, then it sounds like there's a bug.

There are some other possible issues though.  The api's for perl, ruby,
python, etc. are compiled using compile strings generated by those
commands.  Those compile strings may introduce rpath's, depending on the
platform.  I'm not sure what to do about that other than modify the
configurations of perl, ruby, python, etc. so that they don't include
rpath.  I know on debian linux, they are already configured to exclude
rpath, but I'm not sure about other platforms.  I guess I could also add
code to filter the rpath's out, but that might turn out to be tricky.

Another issue is config file names and locations for temporary files.
The default paths for config files, debug files, temp files and so forth
are compiled into SQL Relay.  There are command line switches to make it
use other locations, but the default locations are compiled in.  I could
add an option requiring them to be specified on the command line though.

Dave
dav...@fi...

On Tue, 2008-03-04 at 09:57 +0000, Chris Underhill wrote: 
> ohn Klassa wrote:
> 
>  > Given this "definition" (if you will), is SQL Relay relocatable?  Do I
>  > need to do anything special, to make it so?
> 
> [Snip]
> 
>  > The closest thing I see is the rpath options, for which the "disable"
>  > versions are limited to:
>  >
>  >   --disable-oracle-rpath        Don't use -rpath when building Oracle 
> connection
>  >   --disable-freetds-rpath       Don't use -rpath when building FreeTDS
>  > connection
>  >   --disable-sybase-rpath      Don't use -rpath when building Sybase 
> connection
>  >   --disable-db2-rpath            Use -rpath when building DB2 connection
>  >
>  > This doesn't seem to cover all bases...
> 
> I've had similar problem in the past with sqlrelay linking to libraries 
> in my build environment. This I spotted as it caused grief with SELinux.
> 
> The solution (other than fixing the build scripts) is to install chrpath 
> and then run it against the libraries. If you're running Red Hat, it's 
> in the RPMForge repository. From the README file:
> 
> chrpath
> =======
> 
> chrpath allows you to modify the dynamic library load path (rpath) of
> compiled programs.  Currently, only removing and modifying the rpath
> is supported.  Eventually, I hope to be able to add an rpath if it is
> missing.
> 
> Cheers,
> 
> 
> Chris,.
> 
> -------------------------------------------------------------------------
> 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] is SQL Relay "relocatable"?

[Chris U. <ch...@od...>]

ohn Klassa wrote:

 > Given this "definition" (if you will), is SQL Relay relocatable?  Do I
 > need to do anything special, to make it so?

[Snip]

 > The closest thing I see is the rpath options, for which the "disable"
 > versions are limited to:
 >
 >   --disable-oracle-rpath        Don't use -rpath when building Oracle 
connection
 >   --disable-freetds-rpath       Don't use -rpath when building FreeTDS
 > connection
 >   --disable-sybase-rpath      Don't use -rpath when building Sybase 
connection
 >   --disable-db2-rpath            Use -rpath when building DB2 connection
 >
 > This doesn't seem to cover all bases...

I've had similar problem in the past with sqlrelay linking to libraries 
in my build environment. This I spotted as it caused grief with SELinux.

The solution (other than fixing the build scripts) is to install chrpath 
and then run it against the libraries. If you're running Red Hat, it's 
in the RPMForge repository. From the README file:

chrpath
=======

chrpath allows you to modify the dynamic library load path (rpath) of
compiled programs.  Currently, only removing and modifying the rpath
is supported.  Eventually, I hope to be able to add an rpath if it is
missing.

Cheers,


Chris,.

[Sqlrelay-discussion] is SQL Relay "relocatable"?

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

I'm attempting to install SQL Relay into a company-wide, distributed
filesystem...  One of the requirements is:

> Make sure your binary package is relocatable.
> This is more important than it sounds; many binary
> packages have hardcoded paths and expect to be
> installed in a particular place.

Given this "definition" (if you will), is SQL Relay relocatable?  Do I
need to do anything special, to make it so?

I ask, because did some playing around, as follows:

1. I built SQL Relay with a --prefix, and then installed it (call the
location ORIG).

2. I tarred up the installation, and copied it elsewhere (call the
location MOVE).

3. I ran a perl test script against MOVE, and it seemed to work.
However, a truss revealed that it was still looking for the libraries
in ORIG -- even though, after not finding them, it was able to
successfully move on, and find them in MOVE.

In my case, though, the fact that it looked in ORIG at all, is bad.  I
can't afford to have clients hit ORIG, from anywhere and everywhere on
our global intranet.  They need to go to MOVE, period.

Pointers/advice/thoughts?  Is this something I can set up, or manage,
via "configure" options?

The closest thing I see is the rpath options, for which the "disable"
versions are limited to:

  --disable-oracle-rpath        Don't use -rpath when building Oracle connection
  --disable-freetds-rpath       Don't use -rpath when building FreeTDS
connection
  --disable-sybase-rpath      Don't use -rpath when building Sybase connection
  --disable-db2-rpath            Use -rpath when building DB2 connection

This doesn't seem to cover all bases...

After reading "rpath considered harmful"
(http://people.debian.org/~che/personal/rpath-considered-harmful),
rpath seemed the likely culprit.  I'm no longer sure, though.

Thanks.

P.S. We do have a build environment, for building things that are
destined for this company-wide, distributed filesystem.  I can't get
SQL Relay to build there, though, and so was falling back to the
next-best thing (which is to create a binary elsewhere, and then
install *that* instead).

[Sqlrelay-discussion] Is it possible to do mdb->sqlrelay->mdb scheme?

[if <ze...@gm...>]

Hello list!

I face a little challenge, that after some heavy googling seem to be
not so little. Here's the problem:

We have custom (*cough* goverment) application for windows, that works
with mdb files _only_. It runs on windows, it opens only predefined
mdb file, and it is dumb and enerprisey, and the worst part -- we are
locked to use it. There's no security in it, no logging, and no
control over the user. It works with financial data, so i have to
tighten it a bit.

I moved mdb file to linux samba server, and restricted access on
network level. The next part is tricky. I need control at sql query
level and make an alert system, because main threat is emloyees
working with it. My plan is to inject sqlrelay between "fake" mdb file
and some good database (think postgres). Or at least inject it between
fake mdb and real mdb, and log sql, that will be sufficient for the
first time. After that i'll write everything i need.

So the main question is -- how can i make this fake mdb file? Sqlrelay
can make socket->mdb proxying, but can it make a reverse proxy? Than
i'll just chain few instances together and get what i want.

I also looked at Perl::DBI Proxy guides, mdbtools guides -- all with no luck.
Any, just any advice will be highly appreciated!


Thanks for reading,
regards,
if

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

[Renaud A. <re...@me...>]

Hey David,

I just tried again by enabling/disabling the MYSQL_OPT_RECONNECT line in mysqlconnection.C and I still get the same results.

I'm running Centos 5.0 and MySQL 5.0.22.

Here are the steps:
1. start sqlrelay instance
2. launch sqlrsh, run a query, verify that it's working ok, exit sqlrsh
3. restart mysqld
4. run sqlrsh again and run a query

In step 4, when I'm running the sqlr-connection-mysql daemon compiled with MYSQL_OPT_RECONNECT enabled, the connection daemon reconnects to the database and the query is successful. When I'm running the sqlr-connection-mysql with MYSQL_OPT_RECONNECT disabled, the connection daemon cannot reconnect and the query fails.

In order to reproduce this problem, I'm thinking that you might want to make sure you're running the same kind of sqlrelay.conf parameters I'm using.
Here are the params for my SQL Relay instance:

<instance id="sqlrelay-test" port="960" socket="/var/sqlrelay/tmp/sockets/sqlrelay-test.socket" dbase="mysql" connections="3" maxconnections="5" maxqueuelength="5" growby="1" ttl="5" endofsession="commit" sessiontimeout="1" runasuser="root" runasgroup="root" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="1" maxlisteners="100" listenertimeout="1" reloginatstart="false">
		<users>
			<user user="test" password="test"/>
		</users>
		<connections>
			<connection connectionid="user_map" string="user=test;password=test;db=sqlrelaytest;host=localhost" metric="1" behindloadbalancer="no"/>
		</connections>
	</instance>


The MySQL config file I'm using is very basic:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



Hope that helps!



Renaud.


-----Original Message-----
From: sql...@li... [mailto:sql...@li...] On Behalf Of David Muse
Sent: Tuesday, February 19, 2008 7:16 AM
To: Discussion of topics related to SQL Relay
Subject: Re: [Sqlrelay-discussion] Patch to fix MySQLauto-reconnect (MYSQL_OPT_RECONNECT option)

This patch appears to be more correct that the code I had before, so
I'll apply it.  Though even without this patch, I can't replicate the
problem that several folks have reported.

If I run an instance of mysql, run sqlrelay against it, run some queries
through sqlrsh, stop the mysql server, run a query through sqlrsh, then
restart the mysql server, sqlrelay successfully reconnects and the query
goes through.  I've tried several different versions of mysql and tried
restart vs. stop/start, and other things, but I can't seem to reproduce
the error.

Could someone who's had trouble with this send me some more info.  What
version of mysql are you using, what platform, and exactly what steps
are you using to reproduce the error?

Thanks,

David Muse
dav...@fi...

On Fri, 2008-02-15 at 00:17 -0500, David Muse wrote:
> 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

-------------------------------------------------------------------------
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] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)

[Chris C. <cc...@gm...>]

Now that you mention the loop.. Something akin to that appears to be
happening without OPT_RECONNECT according to tcpdump and query logs.  Seems
to open a new connection, and immediately quit..
Added some debug output to mysqlconnection.C to make sure it was catching
mysql_error() properly, and it is.  Getting "Lost connection to MySQL server
during query" on or after the re-login.

What's interesting, is the reconnect does work when when I let the
connection timeout and use ping().. However the next query attempted will
fail.

 -- my.cnf -- (nothing special)
[mysqld]
user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
old_passwords   = 1
bind-address            = 0.0.0.0
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
max_connections=200
thread_cache_size=10
table_cache=512
query_cache_limit       = 1048576
query_cache_size        = 16777216
query_cache_type        = 1
log-bin                 = /var/log/mysql/mysql-bin.log
max_binlog_size         = 104857600
wait_timeout                    = 10
server-id       = 1

On Feb 19, 2008 1:54 PM, David Muse <dav...@fi...> wrote:

> When I disable the MYSQL_OPT_RECONNECT line and test it, I get an error
> message: "mysql server has gone away".  SQL Relay detects that string,
> re-logs in and everything works.
>
> I did discover a bug though, because it should have caught the
> CR_SERVER_GONE_ERROR error instead of having to resort to testing the
> error string, but I was checking the result of
> mysql_stmt_execute/mysql_real_query rather than the result of
> mysql_stmt_errno/mysql_errno.  I have a fuzzy memory though of some
> version around 4.0 returning CR_SERVER_GONE_ERROR as the result of the
> query rather than in errno, so maybe that wasn't a bug.
>
> At any rate, the mysql connection daemon code gets the errno now and
> detects CR_SERVER_GONE_ERROR instead of having to test the actual error
> string.  But that still doesn't explain why it works for me but not for
> anyone else.
>
> I do remember an issue way back where running create,drop,start,begin
> and a few other queries through the mysql stmt api caused a
> CR_SREVER_GONE_ERROR and somehow triggered a loop-up when sqlrelay tried
> to re-log in.  I don't remember the exact details.  The fix was to run
> those queries using the older mysql api.  I'm pretty sure you guys are
> seeing something similar but I'm at a loss for how to reproduce it.
>
> Granted I'm not running on Centos, just Fedora, maybe that's the key.
>
> Could you post your /etc/my.cnf file?  Maybe there's some other odd
> mysql parameter that's triggering the issue.
>
> Thanks,
>
> Dave
> dav...@fi...
>
> On Tue, 2008-02-19 at 11:46 -0500, Chris Coyle wrote:
> > Centos 4.4 (Generally)
> > Mysql 5.0.22 or 5.0.41
> >
> >
> > Test Case
> >  - Set the wait_timeout on the mysql server to 10 seconds
> >  - open local sqlrsh instance to mysql server
> >  - run a few queries
> >  - let it sit for > 10s
> >  - run another query
> >
> >
> > Without auto_reconnect enabled, sqlrelay will hang indefinitely (or
> > not, I've never been that patient).  With reconnect enabled you can
> > sit there and every 15 seconds select session_id() and watch it
> > increment.
> >
> >
> > It may be that the API treats a timeout or other non-server failure
> > based disconnect differently from the server bouncing (? vaguely
> > recall something like this from when I was digging through docs, but
> > not sure).
> >
> >
> > Hope this helps,
> >  - Chris
> >
> >
> > On Feb 19, 2008 10:16 AM, David Muse <dav...@fi...>
> > wrote:
> >         This patch appears to be more correct that the code I had
> >         before, so
> >         I'll apply it.  Though even without this patch, I can't
> >         replicate the
> >         problem that several folks have reported.
> >
> >         If I run an instance of mysql, run sqlrelay against it, run
> >         some queries
> >         through sqlrsh, stop the mysql server, run a query through
> >         sqlrsh, then
> >         restart the mysql server, sqlrelay successfully reconnects and
> >         the query
> >         goes through.  I've tried several different versions of mysql
> >         and tried
> >         restart vs. stop/start, and other things, but I can't seem to
> >         reproduce
> >         the error.
> >
> >         Could someone who's had trouble with this send me some more
> >         info.  What
> >         version of mysql are you using, what platform, and exactly
> >         what steps
> >         are you using to reproduce the error?
> >
> >         Thanks,
> >
> >         David Muse
> >         dav...@fi...
> >
> >
> >         On Fri, 2008-02-15 at 00:17 -0500, David Muse wrote:
> >         > 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
> >
> >
> -------------------------------------------------------------------------
> >         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] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)

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

When I disable the MYSQL_OPT_RECONNECT line and test it, I get an error
message: "mysql server has gone away".  SQL Relay detects that string,
re-logs in and everything works.

I did discover a bug though, because it should have caught the
CR_SERVER_GONE_ERROR error instead of having to resort to testing the
error string, but I was checking the result of
mysql_stmt_execute/mysql_real_query rather than the result of
mysql_stmt_errno/mysql_errno.  I have a fuzzy memory though of some
version around 4.0 returning CR_SERVER_GONE_ERROR as the result of the
query rather than in errno, so maybe that wasn't a bug.

At any rate, the mysql connection daemon code gets the errno now and
detects CR_SERVER_GONE_ERROR instead of having to test the actual error
string.  But that still doesn't explain why it works for me but not for
anyone else.

I do remember an issue way back where running create,drop,start,begin
and a few other queries through the mysql stmt api caused a
CR_SREVER_GONE_ERROR and somehow triggered a loop-up when sqlrelay tried
to re-log in.  I don't remember the exact details.  The fix was to run
those queries using the older mysql api.  I'm pretty sure you guys are
seeing something similar but I'm at a loss for how to reproduce it.

Granted I'm not running on Centos, just Fedora, maybe that's the key.

Could you post your /etc/my.cnf file?  Maybe there's some other odd
mysql parameter that's triggering the issue.

Thanks,

Dave
dav...@fi...

On Tue, 2008-02-19 at 11:46 -0500, Chris Coyle wrote:
> Centos 4.4 (Generally)
> Mysql 5.0.22 or 5.0.41
> 
> 
> Test Case
>  - Set the wait_timeout on the mysql server to 10 seconds
>  - open local sqlrsh instance to mysql server
>  - run a few queries
>  - let it sit for > 10s
>  - run another query
> 
> 
> Without auto_reconnect enabled, sqlrelay will hang indefinitely (or
> not, I've never been that patient).  With reconnect enabled you can
> sit there and every 15 seconds select session_id() and watch it
> increment.  
> 
> 
> It may be that the API treats a timeout or other non-server failure
> based disconnect differently from the server bouncing (? vaguely
> recall something like this from when I was digging through docs, but
> not sure).
> 
> 
> Hope this helps, 
>  - Chris
> 
> 
> On Feb 19, 2008 10:16 AM, David Muse <dav...@fi...>
> wrote:
>         This patch appears to be more correct that the code I had
>         before, so
>         I'll apply it.  Though even without this patch, I can't
>         replicate the
>         problem that several folks have reported.
>         
>         If I run an instance of mysql, run sqlrelay against it, run
>         some queries
>         through sqlrsh, stop the mysql server, run a query through
>         sqlrsh, then
>         restart the mysql server, sqlrelay successfully reconnects and
>         the query
>         goes through.  I've tried several different versions of mysql
>         and tried
>         restart vs. stop/start, and other things, but I can't seem to
>         reproduce
>         the error.
>         
>         Could someone who's had trouble with this send me some more
>         info.  What
>         version of mysql are you using, what platform, and exactly
>         what steps
>         are you using to reproduce the error?
>         
>         Thanks,
>         
>         David Muse
>         dav...@fi...
>         
>         
>         On Fri, 2008-02-15 at 00:17 -0500, David Muse wrote:
>         > 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
>         
>         -------------------------------------------------------------------------
>         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] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)

[Fabio W. T. <tr...@gm...>]

Hi David,

Without the patch, the failover of a dead mysql works perfectly, and the
application doesn't report errors to the client, but when the mysql server
comes back again, it doesn't receive new connections until we restart
sqlrelay.

After applying the patch, we had the opposite behavior: while we have a dead
mysql in the backend, the application keeps reporting database errors to the
client, so we don't have a smooth failover anymore, but then the server is
up again, sqlrelay was able to put it back to the pool.

IMHO I think we need to find a better solution to handle mysql reconnections
from sqlrelay to the database servers...
Fabio.

On Feb 19, 2008 12:16 PM, David Muse <dav...@fi...> wrote:

> This patch appears to be more correct that the code I had before, so
> I'll apply it.  Though even without this patch, I can't replicate the
> problem that several folks have reported.
>
> If I run an instance of mysql, run sqlrelay against it, run some queries
> through sqlrsh, stop the mysql server, run a query through sqlrsh, then
> restart the mysql server, sqlrelay successfully reconnects and the query
> goes through.  I've tried several different versions of mysql and tried
> restart vs. stop/start, and other things, but I can't seem to reproduce
> the error.
>
> Could someone who's had trouble with this send me some more info.  What
> version of mysql are you using, what platform, and exactly what steps
> are you using to reproduce the error?
>
> Thanks,
>
> David Muse
> dav...@fi...
>
> On Fri, 2008-02-15 at 00:17 -0500, David Muse wrote:
> > 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
>
> -------------------------------------------------------------------------
> 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] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)

[Chris C. <cc...@gm...>]

Centos 4.4 (Generally)Mysql 5.0.22 or 5.0.41

Test Case
 - Set the wait_timeout on the mysql server to 10 seconds
 - open local sqlrsh instance to mysql server
 - run a few queries
 - let it sit for > 10s
 - run another query

Without auto_reconnect enabled, sqlrelay will hang indefinitely (or not,
I've never been that patient).  With reconnect enabled you can sit there and
every 15 seconds select session_id() and watch it increment.

It may be that the API treats a timeout or other non-server failure based
disconnect differently from the server bouncing (? vaguely recall something
like this from when I was digging through docs, but not sure).

Hope this helps,
 - Chris

On Feb 19, 2008 10:16 AM, David Muse <dav...@fi...> wrote:

> This patch appears to be more correct that the code I had before, so
> I'll apply it.  Though even without this patch, I can't replicate the
> problem that several folks have reported.
>
> If I run an instance of mysql, run sqlrelay against it, run some queries
> through sqlrsh, stop the mysql server, run a query through sqlrsh, then
> restart the mysql server, sqlrelay successfully reconnects and the query
> goes through.  I've tried several different versions of mysql and tried
> restart vs. stop/start, and other things, but I can't seem to reproduce
> the error.
>
> Could someone who's had trouble with this send me some more info.  What
> version of mysql are you using, what platform, and exactly what steps
> are you using to reproduce the error?
>
> Thanks,
>
> David Muse
> dav...@fi...
>
> On Fri, 2008-02-15 at 00:17 -0500, David Muse wrote:
> > 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
>
> -------------------------------------------------------------------------
> 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] Patch to fix MySQL auto-reconnect (MYSQL_OPT_RECONNECT option)

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

This patch appears to be more correct that the code I had before, so
I'll apply it.  Though even without this patch, I can't replicate the
problem that several folks have reported.

If I run an instance of mysql, run sqlrelay against it, run some queries
through sqlrsh, stop the mysql server, run a query through sqlrsh, then
restart the mysql server, sqlrelay successfully reconnects and the query
goes through.  I've tried several different versions of mysql and tried
restart vs. stop/start, and other things, but I can't seem to reproduce
the error.

Could someone who's had trouble with this send me some more info.  What
version of mysql are you using, what platform, and exactly what steps
are you using to reproduce the error?

Thanks,

David Muse
dav...@fi...

On Fri, 2008-02-15 at 00:17 -0500, David Muse wrote:
> 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